Database Isolation Level Detailed

A transaction (transaction) is the execution unit of a database management system, either a database operation (such as a select operation) or a set of sequence of actions. Transaction acid properties, i.e. atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability).

Atomicity: Guarantees that all operations in a transaction are performed or all are not performed. For example, to carry out a transfer transaction, either transfer successfully or fail. Succeeds, the amount is transferred from the outbound account to the destination account, and the amount of the two accounts changes accordingly; the amount of the two accounts is unchanged. There will be no transfer out of the account to deduct the money, while the destination account does not receive the money situation.

Consistency: Ensure that the database always maintains data consistency-the transaction is consistent before the transaction operation, regardless of the success or failure of the transaction. As in the above example, the database maintains consistency of data both before and after the transfer.

Isolation: When multiple transactions are executed concurrently, the result should be the same as the serial execution effect of multiple transactions. Obviously, the simplest isolation is to execute all transactions serially: First, execute first, and a transaction is done before it is allowed to execute the next. However, this database is inefficient, such as: Two different transactions just read the same batch of data, this can be done concurrently. There are different isolation levels to control the effect of concurrent execution. This is described in more detail below.

Persistence: persistence means that when things are done, the impact on the database is persistent, and the database should be able to recover even if the database is compromised by a failure. The usual way to implement this is to use logs.

Transaction isolation level (transaction isolation Levels): Isolation level is the level of concurrency control on transactions. Ansi/iso SQL divides it into serialized (SERIALIZABLE), repeatable read (repeatable read), read-committed (reading commited), read-uncommitted (reading uncommited) four levels. In order to implement isolation levels, the database typically uses locks (lock). Generally in the programming when only need to set the isolation level, as to the specific use of what locks are set by the database. First, we introduce four levels, and then illustrate the concurrency problems that occur in the next three levels (repeatable read, Read committed, read UNCOMMITTED).

Serialization (SERIALIZABLE): All transactions are serially executed one after another, thus avoiding Phantom reads (Phantom reads). For databases that implement concurrency control based on locks, serialization requires a range lock (range lock) to be obtained when executing a range query, such as selecting a user aged between 10 and 30. If a database that is not concurrency-controlled based on the lock is not implemented, the transaction needs to be rolled back when a transaction that violates the serial operation is checked.

REPEATABLE READ (REPEATABLE Read): All data obtained by select cannot be modified, thus avoiding the inconsistency of reading data before and after a transaction. But there is no way to control the Phantom reading because the other transaction cannot change the selected data at this time, but you can increase the data because the previous transaction has no scope lock.

Read commited: Read data can be modified by other transactions. This can result in non repeatable reads. That is, when a transaction reads data, it acquires a read lock, but is released immediately after reading (no need to wait until the end of the transaction) and the write lock is released after the transaction is committed. After you release the read lock, you may be able to modify the data by other things. This level is also the default isolation level for SQL Server.

Read uncommited: This is the lowest isolation level, allowing other transactions to see uncommitted data. This level can result in dirty reads (Dirty read).

Example: The concurrency problem for the following three isolation levels is examined below. Suppose there are two transactions. Transaction 1 executes query 1, then transaction 2 executes query 2, then commits, and then the query 1 in transaction 1 executes again. The query is based on the following table:

Users

Id

Name

Age

1

Joe

20

2

Jill

25

REPEATABLE READ (phantom Reading, Phantom reads)

The same query is executed successively in one transaction, but the result set returned is not the same. This occurs because the range lock (range lock) is not acquired when the select operation is performed, causing other transactions to still be able to insert new data.

Transaction 1

Transaction 2

SELECT * from users

WHERE age BETWEEN 30;

INSERT into Users VALUES (3, ' Bob ', 27);

COMMIT;

SELECT * from users

WHERE age BETWEEN 30;

Note that transaction 1 was executed two times for the same query (query 1). If you use a higher level of isolation (that is, serialization), then the two queries should return the same result set. However, the result sets are not the same in the REPEATABLE read isolation level two times. But why is it called a repeatable reading level? That's because the level solves the following problem of non repeatable reading.

Read submitted (not repeatable read, non-repeatable reads)

In a database system that uses locks to implement concurrent control, it is not repeatable to read because the lock is not read when the select operation is performed.

Transaction 1

Transaction 2

SELECT * FROM users WHERE id = 1;

UPDATE users SET age = WHERE id = 1;

COMMIT;

SELECT * FROM users WHERE id = 1;

In this example, Transaction 2 commits successfully, so Transaction 1 will get a different age value for the second time. In the serializable and repeatable read isolation levels, the database should return the same value. In the Read committed and read uncommitted levels, the database returns the updated value. This shows that there is no repeatable reading.

READ UNCOMMITTED (dirty read, dirty reads)

If one transaction 2 reads another transaction 1 modified value, but the last transaction 1 rolls back, then transaction 2 is enrolled in a dirty data, which is known as dirty reading. This occurs when a transaction is allowed to read UNCOMMITTED updates.

Transaction 1

Transaction 2

SELECT * FROM users WHERE id = 1;

UPDATE users SET age = WHERE id = 1;

SELECT * FROM users WHERE id = 1;

RollBack

The above, you can wait until the following table:

Isolation level

Dirty Read

Do not read repeatedly

Phantom reading

Read not submitted

YES

YES

YES

Read submitted

NO

YES

YES

REPEATABLE READ

NO

NO

YES

Serialization

NO

NO

NO