[Database transactions and Locks] detail three: deep analysis of the isolation level of a transaction

Note: This article is reproduced from http://www.hollischuang.com/archives/943

This article details four types of transaction isolation levels, and illustrates, by example, what kind of reading can be resolved at different levels. The implementation principle of different isolation levels in relational database is also introduced.

In the DBMS, a transaction guarantees that a sequence of operations can be executed all or none (Atomicity), from one state to another (consistency). Because the transaction satisfies the long-nature. So once the transaction is committed, the data can be persisted, and because the transaction is satisfied with the isolation, so when multiple transactions simultaneously processing the same data, multiple transactions are directly independent of each other, so, in the process of multiple transactional concurrency, if the isolation level is not well controlled, it is possible to produce dirty read, Non-repeatable reading or reading phenomena such as Phantom reading.

In the acid four properties of a database transaction, isolation is one of the most relaxed. You can use the lock mechanism of the database or the multi-version concurrency control mechanism to obtain a higher isolation level during data operation. However, as the database isolation level increases, the concurrency of the data decreases. Therefore, how to make a good tradeoff between concurrency and isolation becomes a critical issue.

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

Ansi/iso SQL defines four standard isolation levels, from high to the bottom: Serializable (Serializable), REPEATABLE READ (repeatable reads), commit read (Read committed), uncommitted read (read UNCOMMITTED).

The concepts of these four transaction isolation levels, their usage, and what problems are resolved (read) are described in turn below.

Uncommitted read (READ UNCOMMITTED)

Uncommitted read (READ UNCOMMITTED) is the lowest isolation level. By name we can know that at this level of transaction isolation, one transaction can read data that is uncommitted to another transaction.

UNCOMMITTED read Database lock condition (Implementation principle)

The transaction does not lock the data when it reads the data.

Add row-level shared locks only to data when modifying data.

Phenomenon:

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

When transaction 2 updates the record, transaction 1 reads the record again, reading the modified version of transaction 2 to that record (because transaction two only increases the shared read lock, and the transaction can read the data again by adding a shared read lock), even if the modification has not yet been committed.

When transaction 1 updates a row record, transaction 2 cannot update the row record until the end of transaction 1. (because transaction pair data increases shared read lock, transaction Two cannot add exclusive write locks for data modification)

Example

below or borrow my analysis of the reading phenomenon in the database A cited example to illustrate the isolation between two transactions in the uncommitted read isolation level.

Transaction One	Transaction Two
/* 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 */

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

Therefore, uncommitted reads can cause dirty reads

Submit read (committed)

Commit read (read COMMITTED) can also be translated into read submitted, by name can also be analyzed, in a transaction to modify the data process, if the transaction has not been committed, other transactions can not read the data.

Read-Committed database lock condition

The transaction adds a row-level shared lock to the data currently being read (locking when read), and once the line is read, the row-level shared lock is released immediately;

When a transaction updates the moment of a data (that is, the moment the update occurs), it must be added to the exclusive lock on the row until the end of the transaction is released.

Phenomenon:

Transaction 1 During the entire process of reading a row of records, transaction 2 can read the row record (because transaction two can also increase the shared lock to read the data if the row-level shared lock is added to the record.) ）。

Transaction 1 Reads the moment of a row, transaction 2 cannot modify the row data, but as long as transaction 1 has finished reading the row data, transaction 2 can modify the data on that line. (transaction one adds a shared lock to the data in the instant it is read, and no other transaction can add an exclusive lock to the row's data.) But when a transaction reads the row data, the row-level shared lock is released, and once the lock is released, transaction two can add an exclusive lock to the data and modify the data.

When transaction 1 updates a row record, transaction 2 cannot update the row record until the end of transaction 1. (Transaction one, when updating data, adds an exclusive lock to the row's data, knowing that the end of the transaction will release the lock, so that transaction one can not increase the data read of the shared lock until transaction two is not committed.) So, submit read can solve 脏读 the phenomenon)

Example

Transaction One	Transaction Two
/* 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; COMMIT;  /*lock-based REPEATABLE READ */

At the commit read isolation level, transaction one cannot read data until transaction two commits. Transaction one can read data only after transaction two commits.

However, as we can see from the above example, the result of a transaction one or two reads is not consistent, so the read cannot be solved by reading without repetition.

In short, this isolation level of commit reads ensures that any data read is committed and avoids 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.

REPEATABLE READ (repeatable reads)

REPEATABLE READ (repeatable READS), because the read-by-commit isolation level produces non-repeatable read behavior. Therefore, the problem of non-repeatable reads can be resolved by a higher level of isolation than the submission read. This isolation level is called repeatable reading (the name is not very willful!!) ）

REPEATABLE READ Database lock condition

When a transaction reads a data in an instant (that is, the moment it begins to read), it must be shared with a row level lock until the end of the transaction is released;

When a transaction updates the moment of a data (that is, the moment the update occurs), it must be added to the exclusive lock on the row until the end of the transaction is released.

Phenomenon

Transaction 1 During the entire process of reading a row of records, transaction 2 can read the row record (because transaction two can also increase the shared lock to read the data if the row-level shared lock is added to the record.) ）。

Transaction 1 During the entire process of reading a row of records, transaction 2 cannot modify the row data (the entire process of reading the transaction will increase the shared lock on the data until the transaction commits to release the lock, so that no other transaction in the entire process can add exclusive locks to that row of data.) Therefore, repeatable reading can solve 不可重复读 the phenomenon of reading)

When transaction 1 updates a row record, transaction 2 cannot update the row record until the end of transaction 1. (Transaction one, when updating data, adds an exclusive lock to the row's data, knowing that the end of the transaction will release the lock, so that transaction one can not increase the data read of the shared lock until transaction two is not committed.) So, submit read can solve 脏读 the phenomenon)

Example

Transaction One	Transaction Two
/* 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 example above, transaction two can change the row data only after the transaction has been committed. So, as long as the transaction begins and ends, no matter how many times he reads the data, the result is the same.

From the above example, we can conclude that the repeatable read isolation level solves the non-repeatable read behavior. But repeatable reading this isolation level, there is another kind of reading phenomenon he can not solve, it is the illusion of reading. Look at the following example:

Transaction One	Transaction Two
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;
	/* Query 2 */ INSERT INTO users VALUES ( 3, ‘Bob‘, 27 ); COMMIT;
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;

The above two transactions are performed in the following cases:

1. The first query condition for transaction one is age BETWEEN 10 AND 30; if there are 10 records that match the criteria. At this point, he will add row-level shared locks to the qualifying 10 records. These 10 records cannot be changed by any other transaction.

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

3. When a transaction is executed again SELECT * FROM users WHERE age BETWEEN 10 AND 30; , the result returns a record of 11, which is the same as the one that the transaction two has just inserted.

Therefore, the two-range query results for transaction One are not the same. This is also the Phantom reading we mentioned.

Serializable (Serializable)

Serializable (Serializable) is the highest isolation level, which is not resolved by all the isolation levels mentioned earlier, and can be resolved in the Serializable isolation level.

As we have said, the reason for the Phantom reading is that transaction one does not increase the range lock at the time of the scope query (range-locks: A "WHERE" clause is used to describe the range lock in the query to select), which results in a phantom read.

Serializable database lock condition

When a transaction reads data, it must first be shared with a table-level lock until the end of the transaction is released;

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

Phenomenon

When transaction 1 is reading a record in Table A, transaction 2 can also read the a table, but cannot update, add, delete, and end the a table until transaction 1. (Because transaction one-to-one tables increase table-level shared locks, other transactions can only increase shared lock read data and no other operations)

When transaction 1 is updating the records in table A, transaction 2 cannot read any records of table A, and it is more unlikely that the a table will be updated, added, deleted until the end of transaction 1. (Transaction-to-table adds table-level exclusive locks, other transactions cannot add shared or exclusive locks to tables, and cannot do anything)

Although serializable solves the phenomenon of dirty reading, non-repeatable reading, and Phantom reading. However, serializing a transaction has the following effects:

1. Cannot read records that have been modified but not committed by another transaction.

2. Other transactions cannot modify records that have been read by the current transaction until the current transaction is complete.

3. The index key value of a new record inserted by another firm before the current transaction is completed cannot be in the index key range read by any statement of the current transaction.

The four transaction isolation levels are increasing in isolation, but at the same time becoming less concurrent. There are several isolation levels that allow developers to choose the most appropriate isolation level for their business needs during the development process.

Resources

Wikipedia-Transaction isolation

Database isolation level and its implementation principle

[Database transactions and Locks] detail three: deep analysis of the isolation level of a transaction