Shulou(Shulou.com)05/31 Report--

This article introduces how to analyze various types of SQL locking in MySQL INNODB storage engine. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

MYSQL5.6

Isolation level of things: RR

Innodb_locks_unsafe_for_binlog=OFF

I

NSERT:

The current transaction is recorded as TX1

① determines whether the primary key or the only conflict is created.

YES, request implicit lock-> explicit lock X record lock-> S record lock

1 > the record already exists in the table and a Duplicate error is returned (holding S record lock to be released after transaction (TX1) commit or rollback)

2 > the record is also in an uncommitted state (request S record lock) in other transactions (TX2), which is called primary key or unique conflict uncertain state (this will happen later on in update).

1. If the transaction (TX2) commits, the TX1 returns a Duplicate error (holding the S record lock to be released after the transaction (TX1) commit or rollback)

two。 If the transaction (TX2) is rolled back, the TX1 acquires the S record lock successfully, which is consistent with the following situation

NO, request Itention INSERT LOCK (gap), where next_key lock, gap lock, record lock (all S X) will block it

1 > if it is not blocked, get Itention INSERT LOCK (gap), and then get X record lock

2 > if blocked, wait

PS: the lock types generated by the INSERT statement are INSERT Itention LOCK (gap), X record lock, S record lock (application for X to S due to primary key conflict), IX (intended to write locks at the table level, which will be ignored here), autoinc lock (self-adding locks, table level, and later analysis).

UPDATE:

PK- primary key

UK- is the only one

NK- non-unique index

OTHER-- unindexed field

1. The condition is competition

① update tb set pk=new where pk=old

② update tb set uk=new where pk=old

③ update tb set nk=xxx where pk=old

④ update tb set other=xxx where pk=old

Competition plus X record lock and corresponding uk plus X record lock

For ① operations, pk=old pk=new uk=tb (competitive) has a total of 3 X record lock

For ② operations, pk=old uk=new uk=tb (competitive) has a total of 3 X record lock

For ③ operations, pk=old uk=tb (contention) has a total of 2 X record lock

For ④ operations, pk=old uk=tb (contention) has a total of 2 X record lock

two。 The condition is UK

① update tb set pk=new where uk=old

② update tb set uk=new where uk=old

③ update tb set nk=new where uk=old

④ update tb set other=new where uk=old

Same as above

3. The condition is NK

① update tb set pk=new where nk=old

② update tb set uk=new where nk=old

③ update tb set nk=new where nk=old

④ update tb set other=new where nk=old

For ① operation

1 > nk does not match to the record, nk plus a gap lock, open interval

2 > nk match to record

1. Primary key conflict (definite state and uncertain state), nk plus X record LOCK, corresponding primary key plus X record lock, primary key S record lock, no lock on unique key

two。 No primary key conflict, nk plus X record LOCK, pk=old pk=new plus X record lock, nk flanked by x gap lock, uk X record lock, uk S record lock, uk S gap lock

For ② operation

1 > nk does not match to the record, nk plus a gap lock, open interval

2 > nk match to record

1. Unique conflict (divided into deterministic state and uncertain state), nk plus X record LOCK, corresponding primary key plus X record lock, unique key S record lock

two。 No unique conflict, nk plus X record lock, corresponding primary key plus X record lock, uk=new and uk=old plus X record lock

For ③ operation

1 > nk does not match to the record, nk plus a gap lock, open interval

2 > nk matches to the record, nk plus a X record lock, nk with gap lock open interval, corresponding primary key plus X record lock

For ④ operation

1 > nk does not match to the record, nk plus a gap lock, open interval

2 > nk matches to the record, nk plus a X record lock, nk with gap lock open interval, corresponding primary key plus X record lock

For the same gap, X gap lock is directly non-conflicting

4. The condition is OTHER

① update tb set pk=new where other=old

② update tb set uk=new where other=old

③ update tb set nk=new where other=old

④ update tb set other=new where other=old

All primary key records plus X record lock and all gap plus X gap lock.

On how to carry out various types of MySQL INNODB storage engine SQL locking analysis is shared here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.