Types of locks in MySQL

The InnoDB storage engine implements two standard row-level locks:

Shared lock S lock allows a transaction to read a row of data exclusive lock X lock allows a transaction to delete or update a row of data

If it is a transaction T1 the shared lock of row R, then another transaction T2 can get the shared lock of row R immediately, because reading does not change the data of row R, which is said to be lock compatible lock Compatible. However, if there are other transactions T3 want to obtain an exclusive lock for row R, you must wait for the transaction T1, T2 to release the shared lock of row R---this situation is called lock incompatibility

You can see that the X lock is incompatible with any lock, while the S lock only and S lock the financial. It is important to note that both S and X are row locks, and compatibility refers to the compatibility of row locks on a single line of records

In addition, INNODB supports multi-granularity granular locking. This lock allows transactions to exist at the same time as locks on the navigation level and at the table level. To support lock-in operations on non-granularity, the InnoDB storage engine supports an additional locking method called intent Lock (Intention lock). An intent lock is to divide a locked object into multiple levels, meaning that a transaction wants to be shackled on a finer granularity (fine granularity).

If the locked object as a tree, then the topmost object lock, that is, the most fine-grained objects locked, then the first need to lock the coarse-grained objects, such as, if you need to record R on the page x lock, then the data A, table, page intent Lock IX, and finally record R x lock, If any of these causes a wait, then the operation will need to wait for the completion of the coarse-grained lock, for example, before the record R and kill the X lock, there has been a transaction to table 1 has a S table lock, then table 1 has a S lock, then the transaction needs to record R on the table IX, due to incompatibility, Then the thing needs to wait for the table lock operation to complete

InnoDB Storage Engine supports intent lock design is concise, its intent lock is table level lock. The purpose of the design is primarily to reveal the type of lock that the next row is requested on a transaction. It supports two kinds of intent locks

 is lock transaction wants to get a shared lock of a few rows in a table exclusive lock IX lock transaction want to get an exclusive lock on a few rows in a table

Because the InnoDB storage engine supports row-level locks, intent locks are not actually blocking any requests other than full table sweep

Information about the current lock request can be viewed through show ENGINE INNODB status

You can see the SQL statement select * FROM T where a<4 lock in share mode is waiting. RECODE LOCKS Space ID page No 3 n bits, index ' PRIMARY ' of table ' test '. ' T ' Trx ID 48b89bd lock_mode X locks Rec But gap indicates a locked resource. Locks Rec But not gap represents a lock on an index, not a range

Prior to the INNODB 1.0 release, users could only view the lock requests in the current database through show full processlist,show ENGINE INNODB status, and then in the case of a lock on things, starting with INNODB 1.0. Table Innodb_trx, Innodb_locks, Innodb_lock_waits are added under the INFORMATION_SCHEMA schema. With these three tables, users can more easily monitor the current transaction concurrency to analyze possible lock issues. Look at the INNODB_TRX structure.

By state, you can observe that a transaction with a trx_id of 730FEE is currently running, while the transaction trx_id to 731f4 is in the lock wait status, and the SQL statement that is run is select * from the parent lock in Shar mode. The table only shows the InnoDB transactions that are currently running, and does not accurately identify some of the conditions of the lock. If you need to view the lock, you also need to access innodb_locks

The user can clearly see the current lock information, trx_id for a transaction of 730FEE to the table parent plus an X row lock. The transaction with ID 7311F4 wants table parent to request a row lock of S. Lock_data are all 1, applying for the same resources, so there will be waiting. This resolves Innodb_trx why the trx_state of one transaction is running and the other is lock waitle

It is also important to note that discovering lock_data is not a trustworthy value. For example, when a user runs a range lookup, Lock_data may return only the primary key value of the first row. At the same time, if the current resource is locked. If the locked page is being brushed from the buffer pool because of the capacity of the InnoDB storage engine buffer pool, the value is also shown as null when viewing the Innodb_locks table. That is, the InnoDB storage engine does not look for a second time from the disk

After looking through the innodb_locks to see if each table is locked, the user can determine the waiting situation caused by this. When the transaction is small, the user can judge it artificially and intuitively. But when the volume of transactions is very large, locks and waits often occur. It's not easy to judge at this time. However, the innodb_lock_waits can be used to directly reflect the current transaction waiting. Table Innodb_lock_waits consists of 4 fields

With the SQL statement above, the user can clearly see which transaction is blocking the other transaction. Of course this just gives the transaction and lock ID, and if necessary, the user can get more visual details according to the table Innodb_trx, Innodb_locks, Innodb_lock_waits. For example, a user can perform a federated query like this

Types of locks in MySQL