Deep analysis of the isolation level of a transaction

Reprint Source: http://www.hollischuang.com/archives/943

This article describes four transaction isolation levels in detail, and illustrates how different levels can solve the reading phenomenon by giving examples. The implementation principle of different isolation levels in relational database is also introduced.

In a DBMS, a transaction guarantees that a sequence of operations can be performed all or none (Atomicity), transitioning from one state to another (consistency). Due to the long transaction satisfaction. So once the transaction is committed, the data can be persisted, because the transaction is satisfied with the isolation, so when multiple transactions processing the same data at the same time, multiple transactions are directly unrelated to each other, so, in the process of multiple transactions concurrency, if the control is not good isolation level, it is possible to produce dirty read, Do not repeat read or read the phenomenon of reading.

In the acid four properties of a database transaction, isolation is one of the most frequently relaxed. The locking mechanism of the database or the multiple version concurrency control mechanism can be used to obtain higher isolation level during the process of data operation. However, as the level of database isolation increases, the concurrency capability of the data decreases. So, how to make a good trade-off between concurrency and isolation becomes a critical issue.

In software development, almost every type of problem has a variety of best practices for our reference, and many DBMS define a number of different "transaction isolation levels" to control the degree of locking and concurrency.

There are four standard isolation levels for Ansi/iso SQL definitions, ranging from high to sequential: Serializable (Serializable), REPEATABLE READ (repeatable reads), commit read (Read committed), uncommitted read (read UNCOMMITTED).

The following is a description of the four transaction isolation level concepts, usage, and what problems are being addressed (read) uncommitted read

Uncommitted read (READ UNCOMMITTED) is the lowest isolation level. By name we know that at this level of transaction isolation, one transaction can read data that is not committed by another transaction. UNCOMMITTED Read Database lock condition (Implementation principle)

Transactions do not lock data when reading data.

To increase the row-level shared locks on data only when modifying the data.

Phenomenon:

Transaction 1 When a row record is read, transaction 2 can also read and update this row of records (because transaction one does not add any locks to the data)

When transaction 2 updates the record, transaction 1 reads the record again and reads the modified version of transaction 2 for that record (because transaction two only adds a shared read lock, transaction one can increase the shared read lock read data), even if the modification has not yet been committed.

Transaction 1 When a record of a row is updated, transaction 2 cannot update this line of records until transaction 1 ends. (Because transaction two pairs of data adds shared read lock, transaction II cannot increase data modification by exclusive write locks) example

The following is a brief example of what I read in the database to illustrate the isolation between two transactions at the isolation level of uncommitted reads.

Affairs One	Business Two
/* Query 1 * * SELECT age from users WHERE id = 1; * 'LL read 20 * *
	/* Query 2 * * UPDATE users SET age = WHERE id = 1; * No commit here/*
/* Query 1 * * SELECT age from users WHERE id = 1; * 'LL read 21 * *
	ROLLBACK; /* lock-based DIRTY READ * *

The transaction was queried two times, and in the course of two queries, transaction two modified the data and did not commit (commit). However, the second query of transaction one found the result of the modification of transaction two. In the analysis of the reading of the database we have introduced, this phenomenon we call dirty reading.

Therefore, uncommitted reading can result in dirty read submission read (Read Committed)

Submit read (Read Committed) can also be translated into read has been submitted, by name can also be analyzed, in a transaction to modify the data, if the transaction has not yet committed, other transactions can not read the data. commit Read database lock condition

The transaction adds row-level shared locks to the currently read data (only lock when read), and immediately releases the row-level shared lock as soon as the row is read;

When a transaction updates a data (that is, the instant of an update), it must be preceded by a row-level exclusive lock, which is not released until the end of the transaction.

Phenomenon:

Transaction 1 in the entire process of reading a row record, transaction 2 can read the row record (because transaction two can also add a shared lock to read data for a transaction that adds a row-level share lock to the row record.) ）。

Transaction 1 When a row is read, transaction 2 cannot modify the row data, but the row data can be modified by transaction 2 as long as transaction 1 reads through the line data. (transaction one adds a shared lock to the data at the moment of reading, and no other transaction can increase exclusive locks on the row data.) However, once the transaction has read the row data, a row-level shared lock is released, and once the lock is released, transaction two can add exclusive locks to the data and modify the data.

Transaction 1 When a record of a row is updated, transaction 2 cannot update this line of records until transaction 1 ends. (Transaction one when updating data, will increase exclusive locks on the row of data, know the end of the transaction will release the lock, so, before transaction two is not committed, a transaction can not increase the data sharing lock data read.) So, submission reading can solve dirty read phenomena) for example

Affairs One	Business Two
/* Query 1 * * SELECT * FROM users WHERE id = 1;
	/* Query 2 * * UPDATE users SET age = WHERE id = 1; COMMIT; /* in multiversion concurrency Control, or lock-based READ committed * *
/* Query 1 * * SELECT * FROM users WHERE id = 1; COMMIT; /*lock-based Repeatable READ * *

In the commit read isolation level, a transaction cannot read data until transaction two is committed. Transaction one can read data only after transaction two is committed.

However, as we can see from the above example, the results of transaction one or two reads are not consistent, so submission reading does not solve the unreadable read phenomenon.

In short, this isolation level ensures that any data that is read is submitted, avoiding dirty reads (dirty reads). However, it is not guaranteed that the same data can be read when the transaction is reread, because other transactions can modify the data just read after each data is read. REPEATABLE READ (repeatable reads)

REPEATABLE READ (repeatable reads), because the Read isolation level can produce unreadable reads. Therefore, it is possible to resolve the problem of non repeatable reading at a higher level of isolation than committing to read. This isolation level is called repeatable reading (the name is not very capricious.) database locks that can be read repeatedly

A transaction must be preceded by a row-level shared lock before it is released at the moment when it reads a data (that is, the instant it begins to read);

When a transaction updates a data (that is, the instant of an update), it must be preceded by a row-level exclusive lock, which is not released until the end of the transaction.

Phenomenon

Transaction 1 in the entire process of reading a row record, transaction 2 can read the row record (because transaction two can also add a shared lock to read data for a transaction that adds a row-level share lock to the row record.) ）。

Transaction 1 During the entire process of reading a row of records, transaction 2 cannot modify the row data (transaction one adds a shared lock to the data throughout the read process until the transaction commits the lock, so no other transaction can increase exclusive locks on the row data throughout the process.) Therefore, repeatable reading can solve the unreadable reading phenomenon.

Transaction 1 When a record of a row is updated, transaction 2 cannot update this line of records until transaction 1 ends. (Transaction one when updating data, will increase exclusive locks on the row of data, know the end of the transaction will release the lock, so, before transaction two is not committed, a transaction can not increase the data sharing lock data read.) So, submission reading can solve dirty read phenomena) for example

Affairs One	Business Two
/* Query 1 * * SELECT * FROM users WHERE id = 1; COMMIT;
	/* Query 2 * * UPDATE users SET age = WHERE id = 1; COMMIT; /* in multiversion concurrency Control, or lock-based READ committed * *

In the example above, transaction two cannot change the row data until the transaction is committed. So, as long as a transaction is between the beginning and the end of the time, no matter how many times he reads the row of data, the result is the same.

From the above example we can conclude that the repeatable read isolation level resolves the unreadable read phenomenon. But there's another kind of reading that can be read in this isolation level. He can't solve it, that is, phantom reading. Look at the following example:

Affairs One	Business Two
/* Query 1 * * SELECT * from users WHERE age BETWEEN 30;
	/* Query 2 * * INSERT into Users VALUES (3, ' Bob ', 27); COMMIT;
/* Query 1 * * SELECT * from users WHERE age BETWEEN 30;

The implementation of the above two transactions and the following phenomena are as follows:

1. The first query condition for transaction one is age BETWEEN and 30, if this is the case where 10 records meet the criteria. At this point, he would add row-level shared locks to these 10 records that match the criteria. No other transaction can change these 10 records.

2. Transaction two executes an SQL statement that inserts a piece of data into a table. Because no transactions are adding table-level locks to the table at this time, the operation can be performed smoothly.

3. Once the transaction is executed again with SELECT * from Users WHERE age BETWEEN and 30, the result returns a record of 11, more than just one, which is the one that transaction two just inserted.

Therefore, the two-time range query results for a transaction are not the same. This is the Phantom reading we mentioned. Serializable (Serializable)

Serializable (Serializable) is the highest isolation level, and all of the previously mentioned isolation levels cannot be resolved in a serializable isolation level.

As we said, the reason for the Phantom reading is that the transaction does not increase the scope lock while the scope query is in progress (range-locks: A "WHERE" clause is used in the query for select to describe the scope lock), resulting in phantom reads. serializable database lock conditions

When a transaction reads data, it must first add table-level shared locks to it until the transaction ends;

When a transaction updates data, it must first have table-level exclusive locks on it until the end of the transaction is released.

Phenomenon

When transaction 1 is reading a record in Table A, transaction 2 can also read a table, but cannot update, add, or delete a table until the end of transaction 1. (because a table-level shared lock is added to a pair of transactions, other transactions can only increase the shared lock read data, no other operations)

When transaction 1 is updating a record in table A, then transaction 2 cannot read any of the records in table A, making it more impossible to update, add, or delete a table until the end of transaction 1. (table-Level exclusive locks are added to a pair of transactions, other transactions cannot add shared or exclusive locks to the table, and nothing can be done)

Although the serializable solution of dirty read, not repeatable read, and read the phenomenon of Phantom reading. However, serialization transactions can have the following effects:

1. Unable to read records that other transactions have been modified but not submitted.

2. Other transactions cannot modify records that are currently read by the transaction until the current transaction is completed.

3. Before the current transaction completes, a new record inserted by another transaction, whose key value cannot be in the range of index keys read by any statement of the current transactions.