Shulou(Shulou.com)06/02 Report--

This article mainly introduces "the principle and usage of MySQL database transaction and lock". In daily operation, I believe that many people have doubts about the principle and usage of MySQL database transaction and lock. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the principle and usage of MySQL database transaction and lock". Next, please follow the editor to study!

MySQL transaction

The characteristic of the transaction, ACID, represents atomicity, consistency, isolation, and durability, respectively. A well-run transaction processing system must have these standard characteristics.

1. Atomicity (atomicity)

A transaction must be regarded as an indivisible minimum unit of work, and all operations in the whole transaction either commit successfully or fail to roll back. For a transaction, it is impossible to perform only part of the operations. This is the atomicity of the transaction.

two。 Consistency (consistency)

A database always transitions from one consistent state to another.

3. Isolation (isolation)

In general, changes made by one firm are not visible to other transactions until they are finally committed.

4. Persistence (durability)

Once the transaction commits, its changes are permanently saved to the database. Even if the system crashes, the modified data will not be lost.

Isolation level

Isolation is actually more complicated than expected. Four isolation levels are defined in the SQL standard, each of which specifies the changes made in a transaction, which are visible and which are not visible within or between transactions. Lower levels of isolation usually perform higher concurrency and lower system overhead.

READ UNCOMMITTED (read not submitted)

At the READ UNCOMMITTED level, modifications in a transaction are visible to other transactions, even if they are not committed. Transactions can read uncommitted data, which is also known as dirty reading. This level can cause a lot of problems. READ UNCOMMITTED will not be much better than other levels in terms of performance, but it lacks many of the benefits of other levels, unless there is a really necessary reason, it is rarely used in practical applications.

READ COMMITTED (submit for read)

The default isolation level for most database systems is READ COMMITTED (but MySQL is not). READ COMMITTED satisfies the simple definition of isolation mentioned earlier: at the beginning of a transaction, you can only "see" the changes made by the committed transaction. In other words, any changes made by one transaction from the beginning to the commit are invisible to other transactions. This level is sometimes called unrepeatable nonrepeatable read, because executing the same query twice may result in different results.

REPEATABLEB READ (repeatability)

REPEATABLEB READ solves the problem of dirty reading. This level ensures that the results of reading the same record multiple times in the same transaction are consistent. But in theory, the repeatable isolation level still doesn't solve another Phantom Read problem. The so-called illusory reading means that when a transaction is reading a record in a certain range of a group, another transaction inserts a new record in that range, and when the previous transaction reads the record in that range again, it will produce a Phantom Row. InnoDB and XtraDB storage engine solve the problem of phantom reading through multi-version concurrency controller (MVCC,Multiversion Concurrency Control). Repeatable readability is the default transaction isolation level for MySQL.

SERIALIZABLE (serializable)

SERIALIZABLE is the highest isolation level. It avoids the problem of phantom reading mentioned earlier by forcing the serial execution of transactions. In addition, SERIALIZABLE locks every row of data read, which can lead to a large number of timeouts and lock contention problems. This isolation level is also rarely used in practical applications, and it is considered only when there is a great need to ensure data consistency and it is acceptable that there is no concurrency.

InnoDB uses MVCC to support high concurrency and implements four isolation levels. Its default level is REPEATABLEB READ (repeatability) and prevents phantom reading through a gap lock (next-key locking) policy. Gap locks cause InnoDB to lock not only the rows involved in the query, but also gaps in the index to prevent phantom rows from being inserted.

ANSI SQL isolation level

Isolation level dirty reading possibility non-repeatability possibility phantom reading possibility plus locked reading READ UNCOMMITTEDYESYESYESNOREAD COMMITTEDNOYESYESNOREPEATABLEB READNONOYESNOSERIALIZABLENONONOYES

Let's test whether MySQL can avoid phantom reading at the REPEATABLEB READ isolation level (provided by the InnoDB engine)

Test on the customer table

Start the transaction in transaction An and query the result at the age of 12 to 16.

The result is as follows: at this time, the A transaction is not committed.

B transaction, insert a piece of data with age = 14 in the customer table

The result of select statement is

At this point, the B transaction is not committed and the customer table is still

Indicates that the REPEATABLEB READ isolation level can avoid dirty reading. After the B transaction commit, the customer table is

At this point, the A transaction executes the select statement and the result is still

Indicates that the MySQL database can avoid phantom reading under the REPEATABLEB READ isolation level (InnoDB engine). Execute select again after commit and the result is

The operation of the B transaction is now resumed.

Conclusion: MySQL database can avoid phantom reading under REPEATABLEB READ isolation level (InnoDB engine). The transaction of the database is to ensure the security of the data, and the consistency in the transaction characteristics is relatively important.

Lock

Locks are used to ensure the isolation of transactions. Locks are implemented based on indexes.

Read and write locks:

Shared locks (shared lock) and exclusive locks (exclusive lock) are also called read lock and write lock

Read locks are shared and do not block each other, and multiple customers can read the same resource at the same time without interfering with each other. The read lock blocks the write lock. Write locks are exclusive, that is, one write lock blocks other read and write locks, for security policy reasons, so as to ensure that only one user can perform a write at a given time. And prevent other users from reading unified resources that are being written.

A write lock has a higher priority than a read lock, and a request for a write lock may be inserted in front of the read-write queue.

Row locks and table locks:

Table lock is the most basic locking strategy of MySQL, and it is the least expensive one. Locks the entire table. Operations that do not hit the index lock the table

Row locks can support concurrent processing to the maximum extent, while bringing the largest lock overhead InnoDB and XtraDb engine to achieve row-level locking. One or more rows are locked, and the data of other rows in the table can be read and written. If you hit the index, a row or rows will be locked.

Deadlock:

Two or more transactions occupy each other on the same resource and request to lock the resources occupied by the other party, which leads to a vicious circle. InnoDB handles deadlocks by rolling back things that hold the fewest row-level exclusive locks. The behavior and order of locks are related to the storage engine. When statements are executed in the same order, some storage engines produce deadlocks and some do not.

Pessimistic lock and optimistic lock:

Pessimistic lock, assuming that missing updates must exist; sql is followed by for update (exclusive lock).

Optimistic lock, assuming that missing updates do not necessarily occur. There is a version in update, and it is updated according to the version number when updating.

Record lock, gap lock and key lock:

Record lock, which locks a row of data and conditionally hits the primary key index. Where id =? If you add a range query (where id >?) to the primary key, the record lock will degenerate into a gap lock, locking all at the beginning of the most recent value that does not meet the criteria.

A gap lock locks a range and conditionally hits a non-primary key, a non-unique index, and exists only at the isolation level of repeated reads. If it is (where age >?), lock all (before closing and then opening) at the beginning of the most recent value that does not meet the criteria, that is, locking the gap in the index.

Key lock, InnoDB default row-level lock, conditional hit non-primary key, non-unique index, will lock a range, the checked range and the next adjacent interval are locked. Where age =? The range of values on both sides of the value is locked (closed before and on).

Intention sharing lock and intention exclusive lock:

Intention shared lock, when a transaction attempts to add a shared lock to the entire table, it first needs to obtain the intention shared lock for the table.

Intention exclusive lock, when a transaction attempts to add an exclusive lock on the entire table, it first needs to obtain the intention exclusive lock of the table.

At this point, the study of "the principle and usage of MySQL database transactions and locks" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.