Understanding MySQL Transaction isolation mechanisms, locks, and various lock protocols

All along, the understanding of the database's transaction isolation mechanism always stays on the surface, and its contents are also looked over and forgotten. These two days decided to understand it in principle and to sort out their own knowledge. The process of accessing the information found a lot of fragmentary concepts if the string up enough to write a book, so here to comb a context, specific content reference citation or search on the Internet. Because MySQL is the most frequently contacted, some parts of the article are specific to MySQL, please note the reader.

Problems caused by database concurrency operations:

When multiple transactions access the database at the same time, the following 5 types of problems occur, including 3 types of data read problems (dirty read, non-repeatable read, Phantom Read), 2 types of data update problems (first category missing updates, the second category missing updates):dirty Reads (dirty read):a transaction reads the change data that has not yet been committed by the B transaction and operates on this data basis. If the B transaction is rolled back, then the data read by a transaction is not legal at all, called Dirty read. In Oracle, dirty reads do not occur due to version control.

1. non-repeatable read (unrepeatable read): a transaction reads the changed (or deleted) data that the B transaction has committed. For example, a transaction reads the data for the first time, then the B transaction changes the data and commits, the a transaction reads the data again, and the data is read differently two times.
2. Phantom Read (Phantom Read): a transaction reads the new data that the B transaction has submitted. Note and non-repeatable read differences, here is the new, non-repeatable read is changed (or deleted). The two scenarios are different, and for non-repeatable reads, only row-level locks are required to prevent the record data from being changed or deleted, but a table-level lock must be added to the Phantom read to prevent the addition of a single piece of data in the table.
3. first category missing updates: when a transaction is revoked, the data of the committed B transaction is overwritten.
4. The second category is missing updates: when a transaction commits, the data of the committed B transaction is overwritten.

These problems occur with the database under concurrent operation, and it is necessary to find a way to block the operation before performing an operation that might cause a problem, and then wait until the appropriate time to execute it. So how to choose the right time to block the execution of the operation, and how to ensure that after the execution of the scheduling process is completed and the results of the serial execution of the same results?

Level three lockdown protocol

The database wants to block the database operation at the right time, so the first thing to define is "right", because each system supports different business, and the requirements for real-time and validity of the data are not the same. Therefore, the concept of blocking level is proposed in the database theory, and different blocking levels are used for different synchronization requirements.

The Level three lockdown protocol reads as follows:

• first-Class blockade protocol: transaction T must be X-locked before modifying data R until the end of the transaction is released. The end of the transaction consists of a normal end (COMMIT) and an abnormal end (ROLLBACK). A first-level blocking protocol prevents loss of modification and guarantees that transaction T is recoverable. You can use the first-level blocking protocol to resolve lost modification issues.  in the first-level blocking protocol, if only the read data does not modify it, it does not need to be locked, it does not guarantee repeatable read and do not read "dirty" data.
• level Two blocking protocol: first-class blocking protocol plus transaction T before reading the data r must be added s lock, read the rear can release S lock. the level Two blocking protocol prevents the loss of modifications and further prevents the reading of "dirty" data. However, in the level two blocking protocol, the S lock can be released after reading the data, so it cannot guarantee repeatable reads.
• Level three lockdown protocol : The first-level blocking protocol plus the transaction T must have a s lock on the data R before it is released until the end of the transaction. the Level Three lockdown protocol prevents non-repeatable reads, in addition to preventing loss of modification and non-reading of "dirty" data.

Transaction ISOLATION Level:

Level three blocking protocol is reflected in the actual database system, is the level four transaction isolation mechanism. In general, four transaction isolation mechanisms are gradually restricting the degree of freedom of transactions to meet the requirements for different levels of concurrency control. Here are the four isolation levels of the database:

READ UNCOMMITTED, read Committed, repeatable read, Serializable

The strength of each concurrency problem is shown in the following table:

√: May appear x: does not appear

	Dirty Read	Non-REPEATABLE READ	Phantom reading
Read UNCOMMITTED	√	√	√
Read committed	X	√	√
REPEATABLE READ	X	X	√
Serializable	X	X	X

The four levels of resolution for concurrency problems are weak to strong, the corresponding system performance is strong to weak, and the default level of MySQL is repeatable Read.

Read UNCOMMITTED

Under the READ UNCOMMITTED policy, the database adheres to a first-level blocking protocol, restricting only concurrent operations that modify data. A transaction cannot modify data that is being modified by another transaction, but it can read changes that have not yet been committed in other transactions, which, if not committed, will become dirty data.

Read committed

Under the Read Committed policy, the database follows the level two blocking protocol, allowing only the data that has been committed to be read, which, in turn, is not allowed if a transaction modifies a row of data and has not yet committed, and the second transaction reads the line of data. In MySQL InnoDB, although this operation is not allowed, but MySQL does not block the data query operation, but will query the data is modified before the backup, returned to the client. This mechanism of MySQL is called MVCC (multi-version concurrency control), which means that the database maintains multiple versions of the data during transactional concurrency, allowing different transactions to read and write to different versions of the data (MVCC implementations refer to the articles in the reference). Such a mechanism is reflected in the application that at any time the database query can always get the most recently submitted data in the database. The dirty data that is submitted is quarantined and cannot be queried, that is, to prevent dirty reads from occurring.

Repeat Read

Repeat read is a bit stricter than read committed, but it is still in the category of level two blocking protocol, but the reading process is affected by more MVCC. Under Read committed, allowing multiple identical queries in a transaction to get different results, is called non-repeatable read problems. This is allowed in some applications, so this isolation level is default on Oracle, SQL Server, but MySQL does not, it defaults to repeat read level. At this level, depending on MVCC, queries in the same transaction can only find data with a version number that is not higher than the current transactional version, where the transaction can only see data that was before the transaction started or affected by that thing. Conversely, at this level, transactions are not allowed to read newly committed data after the start of the transaction. That prevents the occurrence of non-repeatable reads.

Depending on the mechanism above, the content of the data within the transaction is not changed, but the amount of data obtained by multiple queries is not guaranteed to be consistent. Because data insertions are fully executable during a transaction execution, when data is inserted that exactly matches the query criteria, the result set of the data query is incremented, causing a phantom read. In another case, if a transaction wants to insert a single piece of data, and another transaction has already inserted data with the same primary key, the current transaction is blocked and eventually fails, although the current transaction cannot query this data at all, which is also a phantom read. The gap lock mechanism provided by InnoDB can prevent the occurrence of phantom reading to a certain extent, and refer to the last citation in detail.

Serializable

Finally, the strongest transaction isolation mechanism is serializable, which follows the level three blocking protocol so that all transactions must be serialized, and whenever a transaction queries a table, any modifications to the transaction will be blocked until the transaction commits. This solves all concurrency problems, but it can cause a lot of waiting, blocking, and even deadlock, which reduces system performance.

It is important to note that under any kind of isolation mechanism, one transaction is not allowed to delete or modify data that has been affected by another transaction and not committed. Since the transaction increases, deletes, and changes the data, it adds an exclusive lock to the row, and an exclusive lock blocks other transactions from manipulating the row data again. It is also because of the existence of the exclusive lock, these four isolation mechanisms will not have any kind of update loss phenomenon, because a message does not allow the second transaction to be modified at all.

Two-stage lock protocol

The database follows the "two-segment lock" protocol when scheduling concurrent transactions, which means that all transactions must lock and unlock data items in two stages

1. Extended stage: Before you read or write any data item, apply and obtain a blockade of the data item.
2. Shrink stage: In each transaction, all blocking requests must precede the unlock request.

Mathematically, it can be proved that the scheduling with two-stage lock ensures the same scheduling result as the serialization schedule. This mechanism guarantees the equivalence between database concurrency scheduling and serial scheduling.

Note

Resources:

http://blog.csdn.net/fg2006/article/details/6937413

http://blog.csdn.net/chen77716/article/details/6742128

Http://www.2cto.com/database/201304/201415.html

http://snailxr.iteye.com/blog/1143615

Http://blog.sina.com.cn/s/blog_711b11fd0101bhks.html

Http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html

Understanding MySQL Transaction isolation mechanisms, locks, and various lock protocols