1. The classification of lock

Innodb locks are divided into S locks (shared locks) and X locks (exclusive locks), such as:

Shared lock (S)

select * from supplier where id=5 lock in share mode;
Copy the code

Exclusive lock (X)

select * from supplier where id=5 for update;
Copy the code

Or insert, DELETE,update statements, which are all exclusive locks

compatibility

The compatibility of the two locks is as follows:

X S
X N N
S N Y

Intent locks

It can be understood as the parent node of S lock and X lock, that IS, to obtain S lock or X lock, the intent lock must be obtained in advance, that IS, IS or IX lock. Comprehensively, the compatibility of these two types of locks IS as follows

X S IX IS
X N N N N
S N Y N Y
IX N N Y Y
IS N Y Y Y

Implicit lock

Also drunk, why do so many concepts. This type of lock can be considered as an implicit lock, when there is no conflict, the lock will be upgraded to an explicit lock, or through an example:

select * from test01 where age=21 for update;
Copy the code

Mysql > select * from user where lock is not found:

Transaction 2

insert into test01(id,name,age) values(8,'zzh',22);
Copy the code

Transaction 2 will be blocked because [21,23] has a gap lock

2. Lock analysis and practice

Specific in different isolation levels of transactions, different scenarios lock analysis, lock analysis in Hedecheng this article has been explained in detail, there is no more to say. The main point is to summarize some locking analysis in other cases.

2.1 Insert Intention Locks

Take a look at the official website to explain the lock

Transaction 1 

 insert into test01(id,name,age)values(12,"zzh",33);
Copy the code

Transaction 2

 insert into test01(id,name,age)values(12,"zzh",33);
Copy the code

Transaction 3

 insert into test01(id,name,age)values(12,"zzh",33);
Copy the code

Operation as shown below:

Transaction 1

rollback
Copy the code

  • Transaction 2: Insert intent lock with lock mode X
  • Transaction 3: insert intent lock with wait lock mode X; Have a record lock with lock mode S
  • Implicit condition: Transaction 2 also has a record lock with lock mode S, which causes the deadlock. The deadlock log will not print the lock already owned by the transaction that finally successfully acquired the lock

Therefore, transaction 2-> transaction 3 and transaction 3-> transaction 2 deadlocks occur

There is another similar example on the website, which I won’t go into here, for similar reasons.

Let’s look at another case:

Transaction 1 (transaction ID =2944) 

select * from test01 where age=21 for update;
Copy the code

Transaction 2(transaction ID =2945)

insert into test01(id,name,age)values(2,"zzh",22);
Copy the code

Transaction 3(transaction ID =2946)

select * from test01 where age=21 lock in share mode;
Copy the code

Perform the following

  • Transaction 1: Primary key (X,RECORD LOCK), age_IDx (X,RECORD LOCK)
  • Transaction 2: (S,RECORD LOCK) on primay key (S,RECORD LOCK)
  • Transaction 3: (S,RECORD LOCK) in age_IDx,

Then we commit transaction 1 transaction 1

commit;
Copy the code

  • Transaction 2: lock (S) on the row (S), lock (S) on the row (S)
  • Transaction 3: the S lock was acquired successfully, but on age_IDx, unlike transaction 2.

We re-execute transaction 1

Transaction 1(transaction ID =29467)

select * from test01 where age=21 for update;
Copy the code

Execution result blocked:

Transaction 3

Transaction 2 failed, but its S lock was not released due to the insertion of the intended lock conflict, so transaction 1 is still blocked

2.2 the select… for update

This statement locks in two cases

  • RECORD exists: in RC mode (X,RECORD LOCK), in RR mode (X, next-key LOCK), the LOCK is incompatible with each other
  • Record not present: add (X,GAP) lock and the lock is compatible but not with Insert Intention Locks, which can easily cause deadlocks

2.2.1 Wait deadlocks occur between Indexes

Transaction 1 

select * from test01 where age=21 for update;
Copy the code

Transaction 2

insert into test01(id,name,age)values(3,"zzh",22);
Copy the code

Transaction 1

insert into test01(id,name,age)values(3,"zzh",100);
Copy the code

Execute the results in the following order:

  • Transaction 1: hold age_idx (X, next-key LOCK), insert intent LOCK (S,RECORD LOCK)
  • Transaction 2: Wait for age_IDx insert intent lock (insert intent lock not only on primary key)
  • Implied condition: transaction 2 waits for the intent lock to be inserted on age_IDx, but the intent lock is successfully inserted on id=3, so there is a conflict between transaction 1 and the intent lock

2.2.2 Deadlocks Caused by Non-existent Records

Transaction 1(transaction ID =29684) 

select * from test01 where age=21 for update;
Copy the code

Transaction 2 (Transaction ID =29683)

select * from test01 where age=21 for update;
Copy the code

Transaction 1

insert into test01(id,name,age)values(3,"zzh",22);
Copy the code

The result is as follows

  • Transaction 1 and transaction 2 both hold a gap lock for the record age=22. The gap lock is compatible because the record does not exist
  • However, the record does not have a compatible gap lock and the insert intention lock is not compatible, so the insert intention lock requested by transaction 1 to the index age=22 will wait.

Transaction 2

insert into test01(id,name,age)values(3,"zzh",22);
Copy the code

From the above analysis, it can also be concluded that there are two cases of waiting for transaction 2:

(1) Just like transaction 1, it waits for the intent lock to be inserted at age=22. At this time, it finds that transaction 1 is already waiting for the intent lock to be inserted at this position, so it waits for the S lock at this position

(2) Transaction 1 waits for the intent lock at age=22, but the intent lock at id=3 is successfully added. Therefore, if transaction 2 waits for the intent lock at id=3, there will also be a conflict, so wait for the S lock at this position

So, there are two situations that can cause a transaction to deadlock. Let’s look at the deadlock log:

  • Transaction 1 inserts the intent lock at wait age=22
  • Transaction 2 is waiting for the S lock at id=3, which is the second case we analyze, while transaction 2 has the GAP lock at age=22

As we have analyzed above, there should be two cases of waiting for transaction 2, and these two cases may be the statement of transaction 2

insert into test01(id,name,age)values(3,"zzh",22);
Copy the code

We learned that if we select a record when it doesn’t exist… For update statement, the statement will lock to the vacant position and gap, and this will be dangerous, if we use the table is the primary key, and if the query a record does not exist, it will make the future to insert lock all the space added a gap, and can lead to later can’t insert any data in the table, this is extremely dangerous.

3. Summary

In this paper, we introduce the lock in mysql and some deadlocks that may be encountered in practice. We focus on the analysis through several demos. The analysis of locking has been deeply explained in the article hedecheng mentioned in the article, so this paper does not summarize it. The point is to actually analyze the whole process through some practical examples. The key is to insert intent locks and select… Some analysis of locking in for Update. The next article will focus on mysql’s MVCC mechanism