Database transactions and Isolation levels

Transactions ( Transaction ) is the execution unit of a database management system, which can be a database operation (such as Select operation) or a set of sequence of operations. Transaction ACID properties, i.e. atomicity (atomicity), consistency (consistency), Isolation (Isolation ), persistence (durability).

Atomic nature: guarantees that all operations in the transaction are performed or not performed entirely. For example, a transfer transaction is performed, either a successful transfer or a failure. Succeeds, the amount is transferred from the transfer account to the destination account, and the amount of the two account changes accordingly, and the amount of the two accounts is unchanged. The transfer out account does not appear to be deducted from the money, and the destination account does not receive the money in the case.

Consistency: ensure that the database always maintains the consistency of the data-before the transaction operation is consistent, and the transaction is consistent after the operation, regardless of whether the transaction is successful or not. As in the above example, the database is consistent on the data before and after the transfer.

Isolation: If multiple transactions are executed concurrently, the result should be the same as the serial execution of multiple transactions. Obviously, the simplest isolation is the serial execution of all the transactions: first, First, and one to allow the next to execute. However, the database is inefficient, such as: Two different transactions just read the same batch of data, so it can be done concurrently. There are different isolation levels to control the effect of concurrent execution. This is detailed below.

Durability: persistence means that after a thing is done, the impact on the database is persistent, and the database should be recoverable even if the database is compromised by a failure. The usual implementation is to use the log.

Dirty Reads (dirty read) : Dirty reads occur when one transaction reads a modification that has not yet been committed by another transaction.

Non-repeating reads (non-repeatable read) : The same query occurs more than once in the same transaction, and non-repeating reads occur each time a different result set is returned due to modifications or deletions made by other committed transactions.

Phantom Reading (Phantom read) : The same query occurs multiple times in the same transaction, and Phantom reads occur each time a different result set is returned because of an insert operation made by another commit.

Transaction ISOLATION Level ( Transaction Isolation Levels ): The isolation level is the level of concurrency control over transactions. Ansi/iso SQL divides it into serialization (SERIALIZABLE), repeatable READ (repeatable read), Read committed (readscommited), Reads uncommitted (read uncommited) four levels. In order to achieve the isolation level, the database is typically locked (lock). Generally in the programming time only need to set the isolation level, as to the specific use of what the lock is set by the database. Four levels are introduced first, followed by examples of concurrency problems that occur in the next three levels (repeatable read, Read committed, read UNCOMMITTED).

serialization ( Serializable: All transactions are executed serially, one by one, to avoid Phantom reads ( Phantom reads 10 to 30 range Lock

Repeatable Read ( REPEATABLE Read): All data retrieved by Select cannot be modified, thus avoiding the inconsistency of read data before and after a transaction. However, there is no way to control phantom reading, because other transactions cannot change the selected data at this time, but can increase the data because the previous transaction does not have a range lock.

Read Committed ( Read commited): The data being read can be modified by other transactions. This can result in non-repeatable reads. That is, a read lock is obtained when the transaction reads the data, but is released immediately after the read (no need to wait until the end of the transaction), and the write lock is released after the transaction commits. After you release a 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 not submitted ( READ uncommited): This is the lowest isolation level, allowing other transactions to see data that is not committed. This level can cause dirty reads (Dirty read).

 Example: The following three kinds of isolation levels corresponding to the concurrency problem. Suppose there are two of transactions. Transactions1Execute Query1, and then the transaction2Execute Query2, then commit, and the next transaction1the query in1execute again. The query is based on the following table:

Users

ID Name Age

1 Joe 20

2 Jill 25

repeatable reading ( Phantom Reading,Phantom reads)

select (), causing other transactions to still insert new data.

Transaction 1 Transaction 1

/* Query 1 */

SELECT * from users

WHERE age between and 30;

/ * Query 2 * /

                                 INSERT INTO  users values  ( 3,  ' Bob ', 27 );                                  commit;/* query 1 */select * from userswhere  age BETWEEN10 AND 30; note Transaction 1 performed two times for the same query statement (QUERY 1).   If you adopt a higher level of isolation (that is, serialization), then the previous two queries should return the same result set. However, in the repeatable read isolation level, the result set is not the same as the two times. But why is it called repeatable reading level? That is because the level resolves the following non-repeatable read problems. Read Committed (non-repeatable read, non-repeatable reads) in a database system that uses locks to implement concurrency control, it is not repeatable to read because there is no read lock (Read lock) When performing a select operation. transaction 1                                             transaction2 /* query 1 */select * from users where id  = 1;                                                   /* query  2 */                                                  update users  SET age = 21 WHERE id = 1;                                                  commit;/ * query 1 */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. In this way, non-repeatable reads occur.

READ UNCOMMITTED ( dirty Read,dirty reads)

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

Transaction 1 Transaction2

/* Query 1 */

SELECT * FROM users WHERE id = 1;

/* Query 2 */

UPDATE users SET age = + WHERE id = 1;

/* Query 1 */

SELECT * FROM users WHERE id = 1;

RollBack

Above, you can wait until the following table:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/43/5D/wKiom1PZ8lDAQ3G5AAC_OF-fmls927.jpg "title=" Aaaa.jpg "alt=" Wkiom1pz8ldaq3g5aac_of-fmls927.jpg "/>