MySQL Deadlock example

MySQL has three levels of Lock: page level, table level, row level.
The MyISAM and memory storage engines use a table-level lock (Table-level locking), and the BDB storage engine uses a page lock (page-level
Locking), but also supports table-level locks; The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default.
MySQL features of these 3 types of locks can be broadly summarized as follows:
Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.
Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

Example 1:

--Get row locks by row
--Get the shared lock of the primary key
INSERT INTO Logging_exceptionlog_history_id_2 SELECT * from logging_exceptionlog_history_id;
--Get the mutex of the primary key
Delete from logging_exceptionlog_history_id where ID > ID < 1000;

Show engine InnoDB status; View InnoDB engine status to view recent deadlock conditions

Example 2:

Table T1

CREATE TABLE ' T1 ' (
' ID ' int (one) not NULL auto_increment,
' Name ' varchar (+) DEFAULT NULL,
' Age ' int (one) DEFAULT NULL,
' Posttime ' timestamp NULL DEFAULT current_timestamp on UPDATE current_timestamp,
PRIMARY KEY (' ID ')
) Engine=innodb auto_increment=2 DEFAULT Charset=utf8;

Session 1
--Global auto Commit is set to 0, that is, do not commit automatically, then open another session query Autocommit state, the current session may not have worked
Set GLOBAL autocommit = 0;

Session 2

--Open another session query autocommit status

Show VARIABLES like '%autocommit% ';

--Insert a record
INSERT into T1 (Name, age) VALUES (' xxx ', 23);

Session 3

Show VARIABLES like '%autocommit% ';
--Use the Shared Lock query table, be sure to add lock in share mode, otherwise there will be no deadlock
SELECT * from T1 lock in share mode;

View

SELECT * from Innodb_lock_waits;

SELECT * from Innodb_locks;

SELECT * from Innodb_trx;

Connection query, only need to use this SQL to query the above three tables of information

Select a.requesting_trx_id, C.lock_mode as Wait_lock_mode,
C.lock_type as Wait_lock_type, c.lock_table as Wait_ Lock_table,
C.lock_index as Wait_lock_index, c.lock_data as Wait_lock_data,
E.trx_state as Wait_trx_state, E.trx _query as Wait_trx_query,
A.blocking_trx_id,b.lock_mode as Block_lock_mode, B.lock_type as Block_lock_type,
B.lock_table as Block_lock_table, B.lock_index as Block_lock_index, b.lock_data as Block_lock_data,
D.trx_state as BL Ock_trx_state, d.trx_query as Block_trx_query
from INFORMATION_SCHEMA. Innodb_lock_waits a
     INNER JOIN information_schema. Innodb_locks b on a.blocking_lock_id = b.lock_id
     INNER JOIN information_schema. Innodb_locks C on a.requested_lock_id = c.lock_id
     INNER JOIN information_schema. Innodb_trx D on a.blocking_trx_id = d.trx_id
     INNER JOIN information_schema. Innodb_trx e on a.requesting_trx_id = e.trx_id

MySQL Deadlock example