Mysql Innodb Lock Mechanism and mysqlinnodb Lock Mechanism

Mysql Innodb Lock Mechanism and mysqlinnodb Lock Mechanism
Latch and lock

Latch can be considered as a lock in an application. It can be called a latch (lightweight lock) because it requires a very short lock time. If the lock lasts for a long time, this results in poor application performance. In the InnoDB Storage engine, latch can be divided into mutex (mutex lock) and rwlock (read/write lock ), the objective is to ensure the correctness of the critical resources operated by concurrent threads, and there is no Deadlock Detection mechanism.

 

You can run the show engine InnoDB MUTEX command to view latch In the INNODB Storage ENGINE.

Mysql> show engine innodb mutex;

 

 

 

The lock can be regarded as a lock provided by the database and used to lock data in the database. Generally, the lock object is released only after the transaction commit or rollback (the release time may vary depending on the transaction isolation level), and the lock has a deadlock mechanism.

The lock in the InnoDB Storage engine can be viewed through the show engine innodb status, information_schema.INNODB_LOCKS, INNODB_TRX, INNODB_LOCK_WATIS information.

 

The thread obtains the lock process:

When you add a lock to the data, add latch to the page where the data is located, add lock to the data, and release the Latch on the page after the lock is added.

This mechanism is mainly used to ensure the consistency and integrity of the row data obtained by the thread.

If the lock is occupied by other threads, the thread first releases the page latch and waits for the lock. After obtaining the lock, the thread adds latch to the page again to check whether the page data has been changed, then try to get the corresponding lock again

 

 

Shared lock and exclusive lock

The innodb Storage engine provides the following two standard row-level locks:

Shared lock (S)Allow a transaction to read a row

Exclusive lock (X)Allow transactions that obtain exclusive locks to update or delete data

 

At the same time, the innodb Storage engine supports multi-granularity locking. In order to support locking at different granularities, innodb supports another additional locking method, called intention locking.

 

Intention sharing lock (IS)The transaction wants to obtain the share locks of some rows in a table.

Intention exclusive lock (IX)The transaction wants to obtain the exclusive locks for certain rows in a table.

 

Implementation of row locks

Mysql provides three row lock algorithms.

They are

Record LockRecord lock, the lock on a single record

Gap LockGap lock: locks a range, but does not include the record itself

Next-key LockGap Lock + Record Lock a range and Lock the Record itself

 

 

How Mysql locks

Non-Special notes are discussed at the RR isolation level by default.

The row lock of InnoDb is used to lock the index and scan the row and side of the scan. If a secondary index (non-clustered index) is used, in addition to the secondary index lock, you also need to scan the clustered index of the primary key based on the primary key information in the secondary index to lock the primary key,

The number of rows of locked data is affected by whether Mysql supports Index Condition PushDown (Mysql 5.6 supports ICP). The number of locked data may be much larger than the number of records meeting the conditions.

The reason why two locks are needed here is

If

Statement A uses A secondary index to update record X,

Statement B uses the clustered index to update record X,

If A only locks the secondary index, concurrent statement B will not feel the existence of Statement A, violating the constraints that must be executed serially to update/Delete the same record.

Select * from table where?

At the RC level: no locks are required, consistency is not locked, and snapshot reading is used to read the latest data of the locked row. Therefore, inconsistency may occur between the read data and the read data.

At the RR level: no locks are required. consistent non-locked reads use snapshot reads to read the row data version at the beginning of the transaction. Therefore, the data read before and after the transaction is the same.

At the Serializable level: the current read is used and locks are required. innodb converts the select statement to select... Lock in share mode

 

Insert?

Insert adds a record lock to the inserted rows, and does not prevent other concurrent transactions from inserting records before this record. Before insertion, an insert intention lock will be added to the gap where the inserted record is located (concurrent transactions can add an insert intention lock to the same gap ). If a duplicate-key error occurs in the insert transaction, the transaction adds a shared lock to the record of the duplicate index record. A deadlock occurs when the shared lock is concurrent. For example, two concurrent insert statements apply a shared lock to the same record, at this time, this record is added with the exclusive lock by other transactions. After the exclusive lock transaction deletes this record, two concurrent insert operations will experience deadlocks.

 

Delete?

The delete operation only sets the delete flag of the corresponding record in the primary key column to 1. The record is not deleted and still exists in the B + tree.

The real deletion operation is delayed and is finally completed in the purge operation.

The reason for the delay to the purge operation is that innodb supports mvcc multi-version control, so records cannot be deleted immediately when the transaction is committed. Only when the corresponding row record is not referenced by any other transaction, can be deleted by purge

During the delete operation:

Apply the X lock to the record if the matching record is found and the record is valid

Find the record that meets the condition, but the record is invalid (marked as deleted), add the next key lock and ,;

If no matching record is found, a Gap lock is applied to the first non-conforming record to ensure that no matching record is inserted;

 

Update?

For the next-key lock of records that meet the condition, if it is equivalent matching and uses a unique index or clustered index, you can only add the record lock

 

 

For records with NULL values in a unique index, no record lock will be added. Instead, the next-key lock will be used because NULL is not equal to NULL. If NULL is compared with any value, NULL will be returned, including NULL, but NULL is NULL

 

Deadlock Case Analysis

Create table 'destlocktest'

(

'Id' bigint (20) unsigned not null AUTO_INCREMENT,

'A' bigint (20) unsigned not null,

'B' bigint (20) unsigned NOT NULL,

'C' bigint (20) unsigned not null,

'D' bigint (20) unsigned not null,

'E' bigint (20) unsigned not null,

Primary key ('id '),

Unique key 'I _ a' ('A '),

KEY 'I _ B' ('B '),

KEY 'I _ C' ('C ')

) ENGINE = InnoDb;

 

Insert into deadlocktest (a, B, c, d, e) values (1,999, 5 );

Insert into deadlocktest (a, B, c, d, e) values (2,998, 6 );

Insert into deadlocktest (a, B, c, d, e) values (3,997, 6 );

Insert into deadlocktest (a, B, c, d, e) values (4,996, 5 );

...

Insert into deadlocktest (a, B, c, d, e) values (, 1, 5 );

 

3 insert deadlocks

Transaction	Transaction B	Transaction C
Begin;	Begin;	Begin;
Insert into deadlocktest (a, B, c, d, e) values (4,996, 5 );
	Insert into deadlocktest (a, B, c, d, e) values (4,996, 5 );
		Insert into deadlocktest (a, B, c, d, e) values (4,996, 5 );
Rollback;
	1 row affected	Deadlock found when trying to get lock; try restarting transaction

 

Transaction A obtains the exclusive lock and the data is successfully inserted.

Transaction B transaction C, because the record duplicate-key error is switched to hold the row share lock

Transaction A rolls back and releases the exclusive lock. Transaction B and transaction C need to obtain the exclusive lock of the row. However, because each other holds the shared lock of the corresponding row and waits for each other, A deadlock occurs.

 

Two update deadlocks

Transaction	Transaction B
Begin;	Begin;
Update deadlocktest force index (I _ B) set e = sleep (5) where B> 0;
	Update deadlocktest force index (I _c) set e = sleep (5) where c> 2;
Deadlock found when trying to get lock; try restarting transaction	Rows matched: 4 Changed: 4 Warnings: 0

Two update transactions with Different lock sequence lead to deadlocks

The row lock of InnoDb is used to lock the index and scan the row and side of the scan. If a secondary index (non-clustered index) is used, in addition to the secondary index lock, you also need to scan the clustered index of the primary key based on the primary key information in the secondary index to lock the primary key.

Deadlocks of more than three delete statements

Transaction	Transaction B	Transaction B
Begin;	Begin;	Begin
Delete from deadlocktest where a = 550
	Delete from deadlocktest where a = 550
		Delete from deadlocktest where a = 550
Commit;	0 rows affected	Deadlock found when trying to get lock; try restarting transaction

The delete operation only sets the delete flag of the corresponding record in the primary key column to 1, and the actual deletion delay is in the purge.

Delete: If a record that meets the conditions is found, but the record is invalid (marked as deleted), add the next key lock and ,;

Deadlock log

The deadlocks of the three delete statements are hard to reproduce. I used the following script to complete them.

MY_DB = "mysql-hxxx-Pxxx-uxxx-pxxx"

While:
Do
Echo "use test; begin; delete from deadlocktest where a = 499; rollback;" | $ MY_DB
Done

Conditions for the appearance of such delete deadlocks

1. Deletion of equi-type queries on a unique index

2. More than three concurrent delete operations

3. The transaction isolation level is RR.

4. INNODB Storage Engine

 

References

Https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

Http://hedengcheng.com /? P = 771 # _ Toc374698320

Http://hedengcheng.com /? P = 844