On isolation level of relational database transaction

We know that in a relational database there are four properties for a transaction:

• atomicity (atomicity): a transaction is executed as a whole, and the operations contained therein are either completely executed or not executed.

• Consistency (consistency): a transaction should ensure that the state of the database transitions from one consistent state to another consistent state. The meaning of a consistent state is that the data in the database should satisfy the integrity constraints.

• Isolation (Isolation): When multiple transactions are executing concurrently, the execution of one transaction should not affect the execution of other transactions.

• Persistence (Durability): committed transaction modifications to the database should be persisted in the database.

  
  

We assume that there is now a A, a, a, a different two transactions, and that if transaction a starts before transaction B, and when execution occurs concurrently, there are only two state commits and no commits, and we arrange the combination to get:

• A transaction starts and commits after completion B transaction commits

• A transaction does not commit after it is started and the commit is completed

• A transaction start did not commit B transaction commit

• A transaction start did not commit B transaction not committed

We got the top four cases after the sort combination, and we just have to consider the 2nd and 3 points in the four cases. Below we will analyze the situation of the three-point, roughly divided into a transaction submission and not submitted.

The relational database in the face of the data is nothing more than four basic operations, increase, delete, change, check. Assuming there is a A, B, two different transactions, we combine the state of the transaction with four basic operations, and we get the following results.

A:A Transaction Submitted

1. A transaction increase data has been submitted to B transaction Add data

2. A transaction increase data submitted B transaction delete data

3. A transaction increase data submitted B transaction modification data

4. A transaction increase data submitted B transaction query data

5. A transaction delete data submitted B transaction increased data

6. A transaction delete data submitted B transaction delete data

7. A transaction delete data submitted B transaction modified data

8. A transaction delete data submitted B transaction query data

9. A transaction modification data has been submitted for B transaction Add data

10. A transaction modification data submitted B transaction delete data

11. A transaction modification data submitted B transaction modification data

12. A transaction modification data submitted B transaction query data

13. A transaction query data has been submitted B transaction added data

14. A transaction query data submitted B transaction delete data

15. A transaction query data submitted B transaction modification data

16. A transaction query data submitted B transaction query data

B:A TRANSACTION UNCOMMITTED situation

1. A transaction increase data uncommitted B transaction increase data

2. A transaction increase data uncommitted b transaction Delete data

3. A transaction increase data uncommitted B transaction modification data

4. A transaction increase data not submitted B transaction query data

5. A transaction delete data uncommitted B transaction Add data

6. A transaction delete data uncommitted b transaction Delete data

7. A transaction delete data uncommitted B transaction modification data

8. A transaction delete data uncommitted B transaction query data

9. A transaction modification data uncommitted B transaction Add data

10. A transaction modification data uncommitted B transaction Delete data

11. A transaction modification data uncommitted B transaction modification data

12. A transaction modification data not submitted B transaction query data

13. A transaction query data not submitted B transaction Add data

14. A transaction query data did not commit B transaction Delete data

15. A transaction query data not submitted B transaction modification data

16. A transaction query data not submitted B transaction query data

The above is a A, a transaction in the concurrency of all combinations, we make a summary of the above anomalies, designers have long for us to summarize the concept of their meanings, according to the relevant information on the Internet, can get the following concepts:

• Missing updates:when you revoke a transaction, overwrite the updated data that was committed by the other transaction (Aand theBthe transaction executes concurrently,Aafter the transaction executes the update, commits;Btransactions inAafter the transaction is updated,BAn update to the row's data was made before the transaction ended, and then the two update operations were lost, and then rolled back.

• dirty reads: One transaction reads uncommitted update data to another transaction (Aand theBthe transaction executes concurrently,Bafter the transaction executes the update,ATransaction QueryBthe transaction has no data submitted,Btransaction is rolled back, theAthe data obtained by the transaction is not real data in the database. That is, dirty data, which is inconsistent with the data in the database).

• non-REPEATABLE READ: One transaction reads the updated data that has been committed by another transaction (Aand theBthe transaction executes concurrently,Atransaction query data, and thenBtransaction to update the data,Awhen the data is queried again, it is discovered that the data has changed).

• Overwrite updates: This is a special case in non-repeatable reads, where one transaction overwrites the updated data submitted by another transaction (that is,Atransaction to update the data, and thenBtransaction to update the data,AThe transaction query found itself updated with the data changed).

• Virtual Read (Phantom Read):one transaction reads the newly inserted data that has been committed by another transaction (Aand theBthe transaction executes concurrently,Atransaction query data,Btransaction inserts or deletes data,Athe transaction is queried again to discover that the result set has no previous data or that the previous data has disappeared.

The existence of transaction isolation level is to prevent the above phenomenon, according to the data verification:

ANSI / ISO The standard isolation levels defined by SQL are as follows:

Serializable (Serializable)

The highest isolation level.

The implementation of serializable in a DBMS based on lock-mechanism concurrency control requires that read and write locks on selected objects remain until the end of the transaction to be released. When you use a "WHERE" clause in a query for select to describe a range, you should get a "range lock (Range-locks)". This mechanism avoids the "phantom Read (Phantom reads)" Phenomenon.

You do not need to acquire locks when using concurrency control that is not based on locks. But when the system detects a "write conflict" for several concurrent transactions, only one of them is allowed to commit. For a detailed description of this mechanism, see "snapshot Isolation"

REPEATABLE READ (repeatable reads)

In the REPEATABLE read (repeatable READS) isolation level, the DBMS that is based on the lock mechanism concurrency control needs to keep the read lock (read locks) and write lock on the selected object (writes locks) until the end of the transaction, but does not require a "range lock (Range-locks)" , so "phantom reads" (phantom reads) may occur

Submit read (committed)

In the read COMMITTED level, the DBMS that is based on the lock mechanism concurrency control needs to keep the write lock on the selected object (write locks) until the end of the transaction, but the read lock is released (read locks) immediately after the select operation is complete (therefore "non-repeatable reads" The phenomenon may occur, as described below). As with the previous isolation level, "range Lock (Range-locks)" is not required.

In short, submitting read this isolation level guarantees that any data read is committed data, avoiding the reading of intermediate uncommitted data, dirty reads (dirty reads). But it is not guaranteed to read the same data when the transaction is re-read, because other transactions can modify the data just read after each time the data is read.

Uncommitted read (READ UNCOMMITTED)

Uncommitted read (READ UNCOMMITTED) is the lowest isolation level. Allow dirty reads (dirty reads), transactions can see the other transaction "not committed" modification.

By requiring more restrictions than the lower level of isolation, the higher level provides greater isolation. The standard allows transactions to run at a stronger transaction isolation level. (such as performing a commit read (read COMMITTED) transaction on a repeatable read (repeatable READS) isolation level is not a problem)

The above is only the database level isolation level, at some application level has also defined the corresponding isolation level for the above situation, for example, spring's isolation level definition, everyone is interested in to see the relevant information.

This article is from the "Chen Yanxi" blog, make sure to keep this source http://chenyanxi.blog.51cto.com/4599355/1613702

On isolation level of relational database transaction