Thoroughly understand the four isolation levels and transaction isolation levels of transactions

Thoroughly understand the four isolation levels and transaction isolation levels of transactions
Introduction

In DBMS, transactions ensure that all or all of the operation sequences can be executed (Atomicity), from one State to another (consistency ). Because the transaction is persistent. Therefore, once a transaction is committed, the data can be persisted. Because the transaction is isolated, when multiple transactions process the same data at the same time, multiple transactions do not affect each other directly. Therefore, in the process of concurrent operations on multiple transactions, if the isolation level is not properly controlled, there may be dirty reads, non-repeated reads, Phantom reads, and other reading phenomena.

Among the ACID attributes of database transactions, isolation is the most relaxed one. You can use the database lock mechanism or multi-version concurrency control mechanism to obtain a higher isolation level during data operations. However, as the database isolation level increases, the concurrency of data will also decrease. Therefore, how to make a good balance between concurrency and isolation becomes a crucial issue.

There are four database transaction isolation levels, from low to high: Read uncommitted, Read committed, Repeatable read, Serializable, these four levels can solve dirty reads, non-repeated reads, and Phantom reads one by one.

Note: we will discuss isolation-level scenarios, mainly in the case of multiple transaction concurrency. Therefore, the next sections will focus on transaction concurrency.

Read uncommitted

Read not committed. As the name suggests, a transaction can read data from another uncommitted transaction.

Example: The boss wants to pay the programmer. The programmer's salary is 36 thousand/month. However, the boss accidentally pressed the wrong number when sending the salary, which is 39 thousand/month. The money has already been transferred to the programmer's account, but the transaction has not been submitted. At this moment, the programmer checked his salary this month and found that he had 3 thousand yuan more than usual, so he thought he was very happy to raise his salary. However, the boss promptly discovered the error and immediately rolled back the transaction that was almost committed. He changed the number to 36 thousand and then committed it.

Analysis: This is dirty read, two concurrent transactions, "transaction A": Lead pay, "transaction B": Check the salary of the programmer, transaction B reads the data that transaction Ahas not submitted. The actual programmer's salary this month is 36 thousand, but the programmer sees 39 thousand.

Uncommitted read database locks (implementation principle)

The transaction did not lock the data while reading the data.
When a transaction modifies data, it only adds row-level shared locks to the data.

Performance:

• When transaction 1 reads a row record, transaction 2 can also read and update the row record. When transaction 2 updates the record, transaction 1 reads the record again, you can read the modified version of the record in transaction 2, even if the modification has not been committed.
• When transaction 1 updates a row record, transaction 2 cannot update the row record until transaction 1 ends.

Example

Set session transaction isolation level read uncommitted;

Set autocommit = 0;

The following example illustrates the isolation between two transactions at the uncommitted read isolation level.

Transaction 1	Transaction 2
/* Query 1 */ SELECT age FROM users WHERE id = 1; /* will read 20 */
	/* Query 2 */  UPDATE users SET age = 21 WHERE id = 1; /* No commit here */
/* Query 1 */ SELECT age FROM users WHERE id = 1; /* will read 21 */
	ROLLBACK; /* lock-based DIRTY READ */

Transactions are queried twice in total. During the two queries, transaction 2 modifies the data and does not commit the data ). However, the second query of transaction 1 finds the Modification result of transaction 2. We have introduced the read Phenomenon Analysis of databases. This phenomenon is called dirty read.

Therefore, uncommitted read will cause dirty read.

When transaction 1 updates a row record, transaction 2 cannot update the row record until transaction 1 ends:

We can see that a record is not submitted when it is updated on the right;

At this time, transaction 2 on the left also updates this record, blocking.

Read committed

Commit read, as the name implies, means that a transaction can only read data after another transaction is committed.

Example: A programmer takes a credit card to enjoy his life (card is only 36 thousand of course). When he pays the ticket (the programmer starts the transaction), the charging system detects that his card contains 36 thousand in advance, this is the time !! The programmer's wife needs to transfer all the money out to serve as the household, and submit the money. When the charging system is preparing to deduct the fee, it checks the amount in the card and finds that there is no money (the amount of the second check must be completed after the wife transfers the amount ). Programmers will be very depressed, obviously card is rich...

Analysis: This is committed read. If a transaction updates the data, the read operation transaction can only read the data after the UPDATE operation transaction is committed, it can solve the dirty read problem. However, in this case, two identical queries within the transaction range return different data, which means the data cannot be read repeatedly.

Database locks committed for read (implementation principle)

The transaction adds a row-level shared lock to the currently read data (lock only when read). Once the row is read, the row-level shared lock is immediately released;
When a transaction updates a certain data (that is, when an update occurs), it must be first added with a row-level exclusive lock until the transaction ends.

Example

Transaction 1	Transaction 2
/* Query 1 */ SELECT * FROM users WHERE id = 1;
	/* Query 2 */  UPDATE users SET age = 21 WHERE id = 1; COMMIT; /* in multiversion concurrency control, or lock-based READ COMMITTED */
/* Query 1 */ SELECT * FROM users WHERE id = 1 lock in share mode; COMMIT;  /*lock-based REPEATABLE READ */

** Before transaction 2 is committed, transaction 1 cannot change this record or apply a lock in share mode ). Only common queries can be executed.

At the read isolation level, transaction 1 cannot read data before transaction 2 is committed. Only after transaction 2 is committed Can transaction 1 read data.

However, as we can see from the above example, the results of one or two transaction reads are inconsistent, so committed read cannot solve the read phenomenon of non-repeated reads.

In short, the isolation level of committed read ensures that any data read is committed, avoiding dirty read (dirty reads ). However, it is not guaranteed that the same data can be read when the transaction reads the data again, because after each data reading, other transactions can modify the data just read.

Repeatable read

Repeated reading means that the modification operation is no longer allowed when the transaction starts to read data (the transaction is started ).

Example: A programmer takes a credit card to enjoy his life (card is only 36 thousand of course). When he pays the bill (the transaction is started and cannot be updated or modified by other transactions ), the billing system detects 36 thousand of his cards in advance. At this time, his wife cannot transfer out the money. Next, the billing system will be able to charge fees.

Analysis: repeated reading can solve the problem of non-repeated reading. Here, we should understand that the change, that is, the UPDATE operation, corresponds to non-repeated reads. However, there may be phantom read problems. Because phantom read problems correspond to INSERT operations rather than UPDATE operations.

Database locks that can be reread

When a transaction reads data (that is, when it starts reading data), it must first apply a row-level shared lock to it until the transaction ends;
When a transaction updates a certain data (that is, when an update occurs), it must be first added with a row-level exclusive lock until the transaction ends.

Symptom

In the whole process of reading a row record, transaction 2 can read the row record (because the transaction adds a row-level shared lock to a row record, transaction 2 can also add a shared lock to read the data .).

Transaction 1 cannot modify the row data during the entire process of reading a row record (transaction 1 will add a shared lock to the data during the entire process of reading, the lock is released until the transaction is committed. Therefore, no other transaction can add an exclusive lock to the row of data throughout the process. Therefore, Repeatable read can solveNon-repeated read)

Example

Transaction 1	Transaction 2
/* Query 1 */ SELECT * FROM users WHERE id = 1; COMMIT;
	/* Query 2 */  UPDATE users SET age = 21 WHERE id = 1; COMMIT; /* in multiversion concurrency control, or lock-based READ COMMITTED */

In the preceding example, the row data can be changed only after transaction 1 is committed. Therefore, the results are the same no matter how many times the transaction reads the data in the row from the beginning to the end.

Phantom read

Example: A programmer spent yuan a day, and his wife checked his consumption record today (full table scan, and his wife started transaction ), it really cost two thousand yuan. At this time, the programmer spent 10 thousand to buy a computer, that is, adding a new purchase record and submitting it. When his wife printed the programmer's consumption record list (his wife's transaction was committed), he found that he had spent 12 thousand yuan, and it seemed to be an illusion. This is phantom reading.

Transaction 1: The wife checks the consumption records. The query results of transaction 1 within two ranges are different. This is the Phantom read we mentioned.

The Repeatable read isolation level can solve non-repeated read, but it cannot solve phantom read. Solve phantom read needs serialization.

Serializable serialization

Serializable is the highest level of transaction isolation. At this level, transactions are executed serially in sequence to avoid dirty reads, repeated reads, and Phantom reads. However, this transaction isolation level is inefficient, which consumes database performance and is generally not used.

Serializable database locks

When a transaction reads data, it must first add a table-level shared lock to it until the transaction ends;
When a transaction updates data, it must first apply a table-level exclusive lock to it until the transaction ends.

It is worth mentioning that the default transaction isolation level of most databases is Read committed, such as SQL Server and Oracle. The default isolation level of MySQL is Repeatable read.

References:

In-depth analysis of the transaction isolation level

Understanding the four isolation levels of transactions

Please indicate the source of forwarding: http://www.cnblogs.com/jycboy/p/transaction.html