A preface

This article is the second part of the content of the advanced MYSQL article. Learning this article is based on the knowledge seeker’s previous published articles, especially the introduction to MYSQL Architecture.

This set of tutorials

  • MYSQL is introduced (1)
  • MYSQL retrieval (2)
  • MYSQL retrieval (3)
  • MYSQL > insert into MYSQL
  • MYSQL things (5)
  • MYSQL data type (6)
  • MYSQL > alter table table_name;
  • MYSQL view (8)
  • MYSQL stored procedure (9)
  • MYSQL cursor and trigger (10)
  • MYSQL > alter table user permissions
  • Introduction to MYSQLl Architecture (12)
  • MYSQL lock wait and deadlock (13)
  • MYSQLl operating json (14)
  • MYSQL Execution Plan (15)
  • MYSQL index (16)

Public account: Knowledge seeker

Inheriting the spirit of open Source, Spreading technology knowledge;

The second lock wait

The meaning of lock wait is very easy to understand, that is, session (transaction session, opening an item represents A session) A obtains an exclusive lock on A row (in this case, it is usually A write lock), and then session B obtains an exclusive lock on the same row. A simple understanding is that a child snatches a toy, and the one who grabs the toy first plays with it first. The child who fails to grab the toy can only wait for the child to get tired of playing with the toy and then give it to you. Innodb_lock_wait_timeout specifies the duration of the deadlock. If the deadlock wait time is exceeded, an exception is reported.

Here’s an experiment for knowledge seekers:

Session A executes the following statement to open the transaction and update the statement with index 1; Session A obtains write lock permission for the statement id= 1.

BEGIN;
update  `order` set `year`= '2022' where id = '1';
Copy the code

Session B executes the following statement. The write lock of the data id =1 has been acquired by session A, so the lock wait occurs.

BEGIN;
update  `order` set `year`= '2022' where id = '1';
Copy the code

By default, the knowledge seeker waited 50 seconds and reported the following exception

Lock wait timeout exceeded; try restarting transaction
Copy the code

View default lock wait statements

show  VARIABLES like  'innodb_lock_wait_timeout'
Copy the code

Three deadlock

3.1 Deadlock generation

A deadlock is a situation in which two or more sessions are waiting for each other during resource grabs. Session A obtains A write lock with ID = 1, session B obtains A write lock with ID =2, and session A obtains A write lock with ID =2. When session A tries to obtain the write lock with ID =2, session B obtains the write lock with ID =2, so the lock wait occurs. When session B tries to obtain the write lock with ID =1, the write lock has been obtained by session A, and the lock wait occurs. Session A and session B are in the lock waiting state at the same time, and the lock is waiting for the other party to release. Therefore, A deadlock occurs.

Knowledge seekers do an experiment

Session A executes the statement to obtain the write lock permission with id =1.

BEGIN;
update  `order` set `year`= '2022' where id = '1';
Copy the code

Session B executes the statement to obtain the write lock permission with id =2.

BEGIN;
update `order` set `year`= '2022' where id = '2';
Copy the code

Session A executes A statement to obtain the write lock permission with id =2 and enters the lock wait state

update `order` set `year`= '2022' where id = '2';
Copy the code

Session B executes a statement to obtain the write lock permission with id =1 and enters the lock wait state

update  `order` set `year`= '2022' where id = '1';
Copy the code

When B enters the lock wait, the deadlock exception is reported directly

Deadlock found when trying to get lock; try restarting transaction
Copy the code

3.2 Checking deadlocks

Deadlocks can be viewed using show Engine Innodb status

. *** (1) TRANSACTION: // TRANSACTION A 253507, ACTIVE 474 SEC starting index read mysql tables in use 1, LOCK WAIT 3 LOCK struct(s), heap size 1136, 2 row LOCK (s), undo log entries 1 MySQL thread id 17001, OS thread handle 139824777217792, query id 2191731 ...... Updating 'order' set 'year' = '2022' WHERE ID = '2'// WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS SPACE ID 65 Page no 3 N bits 80 index PRIMARY of table 'ZSZXZ'. 'order' TRX ID 253507 lock_mode RECORD LOCKS SPACE ID 65 Page no 3 N bits 80 index PRIMARY of table 'ZSZXZ'. 'order' TRX ID 253507 lock_mode X locks rec but not gap waiting ..... *** (2) TRANSACTION: // TRANSACTION B 253508, ACTIVE 425 SEC starting index read mysql tables in use 1, Lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread ID 17002, OS thread handle 139824778569472, query id 2191735 ...... Updating 'ORDER' set 'year' = '2022' WHERE id = '1'// hold THE LOCK(S): RECORD LOCKS SPACE ID 65 Page No 3 N bits 80 index PRIMARY of table 'ZSZXZ'. 'order' TRX ID 253508 lock_mode X locks rec but not gap ...... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS SPACE ID 65 Page no 3 N bits 80 index PRIMARY of table 'ZSZXZ'. 'order' TRX ID 253508 lock_mode RECORD LOCKS SPACE ID 65 Page no 3 N bits 80 index PRIMARY of table 'ZSZXZ'. 'order' TRX ID 253508 lock_mode X locks rec but not gap waiting ......Copy the code

I have to say that the next letter represents the type of lock as follows

  • Shared lock (S)
  • Exclusive lock (X)
  • Intention Sharing (IS)
  • Intentional exclusion (IX)
  • Gap lock (GK), a gap lock that locks a range, excluding the current record itself;
  • RECORD LOCKS represent RECORD LOCKS;

MYSQL thread ID 17001; MYSQL thread id 17001; MYSQL thread ID 17002; Objects A and B are waiting for each other to release the lock, resulting in A deadlock;

Tip #; To check table locks: show status like ‘table%’;

Here’s how to solve deadlocks:

MYSQL > select * from MYSQL;

If the thing is not committed, roll back the thing directly;

3.3 How to Avoid deadlocks

  • Deadlock does not occur when using table locks on tables that are prone to deadlock.
  • Avoid cross-using the same locks