What are MySQL's intent shared locks, intent exclusive locks and deadlocks?

1. InnoDB table-level locks

Row-level locks should usually be used to ensure the integrity of transactions, and this is also a common reason for choosing the InnoDB engine. one. However, in individual cases, table-level locks may also be required.

The transaction needs to update most or all of the data, and the table is relatively large. If the default row lock is used, not only will the transaction execution efficiency be low, but it may also cause other transactions to wait for a long time and lock conflicts. The transaction involves multiple tables. , which is more complicated and is likely to cause deadlock and rollback of a large number of transactions.

When we want to obtain the table lock, execute the following command:

When using table locks, efficiency issues involved:
To obtain a shared lock S or an exclusive lock X on a table, you must first make sure that the table has not been acquired by other transactions. After X lock, the data in this table has not been acquired by other transactions.

Suppose we have 10 million data in this table, how to determine which rows of these 10 million data have X locks?
If you want to obtain the S lock of the table, you need to determine which rows in the table have X locks. If some rows have X locks, you cannot obtain the S lock or X lock of this table. There is no better way except checking one by one, which leads to the problem of low efficiency.

The inefficiency caused by the need to add table locks is to traverse the data one by one to determine whether some data has been added with row locks. low problem. The intention shared lock and intention exclusive lock we are learning here can be solved. When you want to obtain the X lock of the table, you no longer need to check which row locks in the table are occupied by (X or S), you only need to quickly check IX Just lock it with IS.

2. Intention shared lock and intention exclusive lock

• Intention shared lock (IS lock): Transaction plan adds row shared lock to the record , before a transaction adds a shared lock to a row of records, it must first obtain the IS lock of the table

• Intention exclusive lock (IX lock): The transaction plan adds a record to the IS lock Row exclusive lock. Before a transaction adds an exclusive lock to a row record, it must first obtain the IX lock of the table

• ##Before adding row locks, the IS or IX lock of the table added by the InnoDB storage engine

• intention locks are

  compatible . There will be no conflict. It is mainly to assist others to speed up the efficiency when acquiring table locks.

• The purpose of intention lock is to obtain table locks more efficiently (X and X in the table S refers to

  table lock, not row lock!)

• Intention lock is

  table-level lock, coordinates table lock and Row lock coexistence . The main purpose is to show that a transaction is locking a row or trying to lock a row.

Analyze transaction 1 to obtain the row X lock and transaction 2 to obtain the table S lock:

When transaction 1 needs to give the 10th row of data to the table When adding an X lock, the InnoDB storage engine automatically adds an IX lock to the entire table. When transaction 2 wants to acquire the S lock of the entire table, it sees that another transaction has acquired the IX lock on this table, which means that there must be some data in this table that has been added to the X lock, which results in Transaction 2 cannot add S lock to the entire table. At this time, transaction 2 can only wait and cannot successfully obtain the table S lock

3. Deadlock

1. Deadlock in the database

MyISAM table lock is deadlock free , this is because

MyISAM does not support transactions, only table locks. It always obtains all required locks at one time, either all are satisfied or waiting, so no deadlock will occur. However, in InnoDB, except for transactions composed of a single SQL, the lock is obtained gradually, that is, the lock granularity is relatively small, which determines that deadlock is possible in InnoDB. Of course, if multiple tables are processed, deadlock may still occur.

Deadlock problems are generally caused by ourselves. Similar to the deadlock situation in multi-thread programming, most of them are caused by the different order in which our multiple threads acquire multiple lock resources. resulting in deadlock problems. Therefore, when we use different code segments to update multiple tables in the database, these tables should be updated in the same order to prevent lock conflicts from causing deadlock problems.

2. Deadlock scenarios and solutions

The deadlock scenario is as follows:

Transaction 1 successfully acquired row lock 1

Transaction 2 Successfully acquired row lock 2
...
Transaction 1 was unable to acquire row lock 2. While being blocked, there was no way to execute commit/rollback and could not release row lock 1
Transaction 2 was unable to acquire row lock 1 and was blocked. There is no way to execute commit/rollback while blocking, and the row lock cannot be released. 2

All transactions are blocked, which is equivalent to all threads in the process being blocked, causing Deadlock problem.

Methods to solve deadlocks: When multiple transactions/threads acquire multiple same resource locks, they should acquire resource locks in the same order.

The transaction is blocked or deadlocked. Mysqld (MySQL Server daemon) is set with a timeout period for transaction blocking. The transaction will not be blocked for a long time and the transaction will be processed after the timeout. On failure, the currently held lock is automatically released.

3. Operation

Set manual commit and repeatable read isolation levels and open transactions

Query the table data, in repeatable The read isolation level uses the snapshot read provided by MVCC and does not lock.

Transaction 1 acquires the exclusive lock with id=7, and transaction 2 acquires the exclusive lock with id=8.

Transaction 1 acquires the exclusive lock with id=8 again, and blocking occurs

Transaction 2 acquires id= again The exclusive lock of 7 is blocked.

At this time, MySQL Server detects that a deadlock has occurred, so it unblocks transaction 1, rollbacks transaction 1, and releases it. The occupied row lock, so transaction 2 successfully obtained the exclusive lock with id=7

3. Lock optimization suggestions

• Under the premise that the business can be completed correctly, To ensure efficiency, try to use a lower isolation level (dirty reads must be avoided)

• Design reasonable indexes and try to use indexes to access data, make locking more accurate, reduce the chance of lock conflicts, and improve concurrency capabilities

• Choose a reasonable transaction size. The probability of lock conflicts for small transactions is small (the larger the transaction, the larger the transaction size. The more SQL there are, it may contain more locks on table resources and row resources, which increases the probability of lock conflicts.) When different programs access a group of tables, they should try to agree to access each table in the same order. For a table , access the rows in the table in a fixed order whenever possible. This can greatly reduce the chance of deadlock

• Try to use equal conditions to access data, which canavoid the impact of gap locks on concurrent insertion(In fact, equal query will also Add gap lock) Do not apply for a lock level that exceeds the actual need

• If it is not necessary, avoid using explicit locking when querying, because under the committed read and repeatable read isolation levels, MVCC has provided a reading mechanism without manual locking

The above is the detailed content of What are MySQL's intent shared locks, intent exclusive locks and deadlocks?. For more information, please follow other related articles on the PHP Chinese website!