Understanding MySql transaction isolation mechanism, locks, and various lock protocols, mysql transactions

Understanding MySql transaction isolation mechanism, locks, and various lock protocols, mysql transactions

The understanding of the transaction isolation mechanism of the database is always on the surface, and its content is also forgotten. In the past two days, I decided to understand it in principle and organize it into my own knowledge. Many fragmented concepts are found during the data reading process. If there are enough pieces of data to write a book, you can sort out the context here. For specific content, refer to the citation or search for it online. Because MySQL is the most commonly used, some of the features in this article are unique to MySQL. Please note that.

Problems caused by concurrent database operations:

When multiple transactions access the database at the same time, the following five types of problems occur, including three types of data reading problems (dirty reading, non-repeated reading, phantom reading ), two types of data update problems (first type loss update, second type loss update): dirty read (dirty read): Transaction A reads the changed data not committed by transaction B, and operate on this data. If transaction B rolls back, the data read by transaction A is not legal at all, which is called dirty read. In oracle, dirty reads are not performed due to version control.

The database may encounter these problems in concurrent operations. To solve this problem, you must find a way to block the operation before executing the operation that may cause the problem, so that it can wait until the appropriate time for execution. So how can we choose the right time to block the execution of the operation, and how can we ensure that the execution result is the same as that of the serial execution operation after the scheduling process is completed?

Level 3 blocking Protocol

If a database wants to block database operations at an appropriate time, first define the appropriate time, because the services supported by various systems vary widely, the requirements for real-time and effective data are also different. As a result, the concept of blocking level is put forward in the database theory, and different blocking levels are used for different synchronization requirements.

The third-level blocking protocol is as follows:

• Level-1 blocking Protocol: Transaction T must apply an X lock to the data R before it modifies it until the transaction ends. The transaction end includes the normal end (COMMIT) and the non-normal end (ROLLBACK ). The level-1 blocking protocol Prevents Loss of modifications and ensures that the transaction T is recoverable. The first-level blocking protocol can be used to solve the problem of loss and modification. In the level-1 blocking protocol, if only the read data is not modified, no locks are required. It cannot ensure repeat and not read "dirty" data.
• Level-2 blocking Protocol: The level-1 blocking protocol and transaction T must apply the S lock to the data before reading the data R. After reading the data, the S lock can be released. In addition to preventing the loss of modifications, the level-2 blocking protocol can further prevent reading of "dirty" data. However, in the second-level blocking protocol, the S lock can be released after data is read, so it cannot guarantee Repeatable read.
• Level-3 blocking Protocol: The level-1 blocking protocol and transaction T must be locked by S before reading data R until the transaction ends. The third-level blocking protocol not only prevents loss of modifications and does not read "dirty" data, but also further prevents repeated reads.

Transaction isolation level:

The three-level blocking protocol is reflected in the actual database system, which is a four-level transaction isolation mechanism. In general, the four transaction isolation mechanisms are gradually limiting the degree of freedom of transactions to meet the requirements for different concurrency control levels. The following are the four isolation levels of the database:

Read Uncommitted, Read Committed, Repeatable Read, Serializable

The following table lists the constraints on concurrency problems:

√: Possible occurrence ×: No

	Dirty read	Non-repeated read	Phantom read
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

The four levels solve the concurrency problem from weak to strong, and the corresponding system performance is from strong to weak. The default MySQL level is Repeatable Read.

Read Uncommitted

In the Read Uncommitted policy, the database follows the first-level blocking protocol and only limits the concurrent operations on data modification. A transaction cannot modify the data being modified by other transactions, but can read uncommitted changes from other transactions. If these changes are not committed, they will become dirty data.

Read committed

In the Read committed policy, the database follows the level-2 blocking protocol and only allows reading committed data. In other words, if a transaction modifies a row of data and has not been committed, the second transaction is not allowed to read this row of data. In InnoDB of MySql, although this operation is not allowed, MySQL does not block the Data Query operation, but queries the backup before the data is modified and returns it to the client. This mechanism of MySQL is called MVCC (Multi-version concurrency control), which means that the database maintains multiple versions of data during the transaction concurrency process, enable different transactions to read and write different data versions. (For MVCC implementation, see references ). This mechanism is reflected in the application. You can always query the database to obtain the data recently submitted in the database. The committed dirty data is isolated and cannot be queried. This prevents dirty reads.

Repeat Read

Repeat Read is more strict than Read Committed, but it is still in the scope of the level-2 blocking protocol, but the Read process is affected by more MVCC. In Read Committed, different results can be obtained by multiple identical queries in a transaction. This is called the non-repeated Read problem. This is allowed in some applications. Therefore, this isolation level is used by default on oracle and SQL server, but MySQL does not. It defaults to the Repeat Read level. At this level, it depends on MVCC. Only data with version no later than the current transaction version can be found in queries in the same transaction, that is, the transaction can only see the data before or affected by the transaction. At this level, transactions are not allowed to read new data committed after the transaction starts. This prevents repeated reads.

Based on the above mechanism, the data content in the transaction remains unchanged, but the data volume obtained by multiple queries cannot be consistent. In the process of executing a transaction, other transactions can completely execute data insertion. When data that just meets the query conditions is inserted, the data query result set will increase and phantom read will be triggered. Another case is that if a transaction wants to insert a piece of data and another transaction has already inserted data with the same primary key, the current transaction will be blocked and the execution will fail, although the current transaction cannot query this piece of data, it is also a phantom read. The gap lock mechanism provided by InnoDB can prevent Phantom reads to some extent. For details, see the last citation.

Serializable

Finally, the strongest transaction isolation mechanism Serializable follows the three-level blocking protocol, so that all transactions must be executed serially. As long as a transaction is queried on the table, before the transaction is committed, any modification to other transactions will be blocked. This solves all concurrency problems, but will cause a lot of waiting, blocking, and even deadlocks, reducing system performance.

Note that, under any isolation mechanism, one transaction cannot be deleted or modified, and the data that has been affected by another transaction is not committed. After a transaction adds, deletes, or modifies data, an exclusive lock will be applied to the row. The exclusive lock will block other transactions from performing operations on the row again. Because of the existence of the exclusive lock, none of the four isolation mechanisms will cause any update loss, because a piece of information cannot be modified by the second transaction.

Two-segment lock protocol

The database follows the "two-segment lock" protocol when scheduling concurrent transactions. The "two-segment lock" Protocol means that all transactions must lock and unlock data items in two phases.

It can be proved in mathematics that the scheduling with two locks can ensure that the scheduling result is the same as that with the serial scheduling. This mechanism ensures the equivalence between concurrent database scheduling and serial scheduling.

* Note:

References:

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

MYSQL transaction isolation

At the beginning of mysql, myisam Table engine did not support transactions. Innodb was bought later. Now oracle has bought it. Autocoomit is indeed a transaction. If it is equal to 1 in mysql documents, it is committed immediately. However, in transction, only commit or rollback is submitted. Hope to be useful to you. The autocommit mode. if set to 1, all changes to a table take effect immediately. if set to 0, you must use COMMIT to accept a transaction or ROLLBACK to cancel it. by default, client connections begin with autocommit set to 1. if you change autocommit mode from 0 to 1, MySQL performs an automatic COMMIT of any open transaction. another way to begin a transaction is to use a start transaction or BEGIN statement. see Section 12.4.1, "start transaction, COMMIT, and ROLLBACK Syntax ".

The protocol used by the database management system for isolation in the ACID feature of transactions

Lock Mechanism