如何进行MySQL加锁处理的分析

如何进行MySQL加锁处理的分析，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

1. 准备测试数据
use test;

CREATE TABLE t1(id int,name varchar(20));

alter table t1 add primary key(id);

insert into t1 values(1,'a'),(4,'c'),(7,'b'),(10,'a'),(20,'d'),(30,'b');

2. 执行测试

set global tx_isolation='read-committed';
set session tx_isolation='read-committed';

组合一：id主键+RC
–SESSINO 1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id=10;
Query OK, 1 row affected (0.00 sec)

–SESSION 2
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set session innodb_lock_wait_timeout=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where id=10; ==>SESSION 2 被阻塞

–SESSION 3
SELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
       r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
       l.lock_index AS waiting_index_lock,
       b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
       SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
       SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC/G

*************************** 1. row ***************************
    waiting_trx_id: 28827
    waiting_thread: 5
         wait_time: 8
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY ==>在主键的ID=10的索引项上加行级排他锁
   blocking_trx_id: 28824
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 179
    blocking_query: NULL
1 row in set (0.01 sec)

mysql> select * from information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 28827:244:3:5
lock_trx_id: 28827
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 244
lock_page: 3
   lock_rec: 5
lock_data: 10 ==>在主键的ID=10的索引项上加行级排他锁
*************************** 2. row ***************************
    lock_id: 28824:244:3:5
lock_trx_id: 28824
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 244
lock_page: 3
   lock_rec: 5
lock_data: 10 ==>在主键的ID=10的索引项上加行级排他锁
2 rows in set (0.00 sec)

–SESSION 1 回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

–SESION 2回滚事务
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

组合二：id唯一索引+RC
这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？

–准备测试数据
use test;

drop table t1;

CREATE TABLE t1(id int,name varchar(20));

alter table t1 add primary key(name);

CREATE UNIQUE INDEX idx_id on t1(id);

insert into t1 values(1,'f'),(2,'zz'),(3,'b'),(5,'a'),(6,'c'),(10,'d');

commit;

–SESSION 1
mysql> delete from t1 where id=10;
Query OK, 1 row affected (0.00 sec)

–SESSION 2
mysql> delete from t1 where id=10; ==>被阻塞

*************************** 1. row ***************************
    waiting_trx_id: 28929
    waiting_thread: 5
         wait_time: 10
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: idx_id
   blocking_trx_id: 28928
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 13
    blocking_query: NULL
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 28929:248:4:7
lock_trx_id: 28929
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 7
lock_data: 10
*************************** 2. row ***************************
    lock_id: 28928:248:4:7
lock_trx_id: 28928
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 7
lock_data: 10
2 rows in set (0.00 sec)

从上面查询出锁的信息中没看到锁定主键相关记录的信息。下面我们在 SESSION 4 中使用 where name='a' 删除记录 (10,'d')

–SESSION 4
mysql> set session innodb_lock_wait_timeout=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from t1 where name='d'; ==>被阻塞

–SESSION 3查看锁信息

SELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
       r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
       l.lock_index AS waiting_index_lock,
       b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
       SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
       SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC/G

*************************** 1. row ***************************
    waiting_trx_id: 28929
    waiting_thread: 5 ==>SESSION 2的 MSYQL 线程ID 为5被阻塞
         wait_time: 339
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: idx_id ==>锁是加在 idx_id 索引上
   blocking_trx_id: 28928
   blocking_thread: 1 ==> SESSION 1 的 MYSQL 线程ID 为1 持有锁资源造成阻塞
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 342
    blocking_query: NULL
*************************** 2. row ***************************
    waiting_trx_id: 28931
    waiting_thread: 7 ==>SESSION 4的 MSYQL 线程ID 为7被阻塞
         wait_time: 27
     waiting_query: delete from t1 where name='d'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY ==>锁是加在主键上
   blocking_trx_id: 28928
   blocking_thread: 1 ==> SESSION 1 的 MYSQL 线程ID 为1 持有锁资源造成阻塞
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 342
    blocking_query: NULL
2 rows in set (0.01 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 28931:248:3:7
lock_trx_id: 28931
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'd' ==>在主键的索引项键值为D的索引项上添加行级排它锁
*************************** 2. row ***************************
    lock_id: 28928:248:3:7
lock_trx_id: 28928
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'd'
*************************** 3. row ***************************
    lock_id: 28929:248:4:7
lock_trx_id: 28929
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 7
lock_data: 10
*************************** 4. row ***************************
    lock_id: 28928:248:4:7
lock_trx_id: 28928
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 7
lock_data: 10 ==>在唯一索引 idx_id 的ID=10的记录（索引项）上加行级排他锁
4 rows in set (0.00 sec)

–SESSION 2发现死锁（该问题后续再研究）

mysql> delete from t1 where id=10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

–SESSION 1、2、4 回滚事务

在该组合的测试中，SESSION 1同时阻塞了 SESSION 2和 SESSION 4，由此证明删除 ID=10 的记录时
会同时在唯一索引和主键索引上加锁行级排他锁。

组合三：id列是二级非唯一索引，RC隔离级别

–准备测试数据

drop INDEX idx_id on t1;
delete from t1;
CREATE INDEX idx_id on t1(id);
insert into t1 values(2,'zz'),(6,'c'),(10,'b'),(10,'d'),(11,'f'),(15,'a');

mysql> select * from test.t1;
+——+——+
| id   | name |
+——+——+
|    2 | zz   |
|    6 | c    |
|   10 | b    |
|   10 | d    |
|   11 | f    |
|   15 | a    |
+——+——+
6 rows in set (0.00 sec)

–SESSION 1 删除ID=10的记录
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';

set autocommit=0;

delete from t1 where id=10;

–SESSION 2 删除ID=10的数据

set global tx_isolation='read-committed';
set session tx_isolation='read-committed';

set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

delete from t1 where id=10; ==>被阻塞

–在 SESSON 5中查看锁的信息
SELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
       r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
       l.lock_index AS waiting_index_lock,
       b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
       SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
       SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC/G

*************************** 1. row ***************************
    waiting_trx_id: 29501
    waiting_thread: 2 ==> SESSION 2
         wait_time: 38
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: idx_id
   blocking_trx_id: 29496
   blocking_thread: 1 ==> SESSION 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 83
    blocking_query: NULL
1 row in set (0.11 sec)

SELECT * FROM information_schema.`INNODB_LOCKS`/G

*************************** 1. row ***************************
    lock_id: 29501:248:4:4 ==> session 2 的锁ID
lock_trx_id: 29501
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 4
lock_data: 10, 'b'
*************************** 2. row ***************************
    lock_id: 29496:248:4:4 ==> session 1 的锁ID
lock_trx_id: 29496
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 4
lock_data: 10, 'b'
2 rows in set (0.00 sec)

从上面的锁信息我们看到 ID=10的数据虽然有2条(10,b),(10,d)，但SESSION 2 只再 (10,b)的记录
上等待锁，这也说明了 MYSQL 的行锁是一条一条的获取，需要在 (10,d)上的锁获取成功完成删除
操作后，再获取(10,d)上的锁。

–SESSION 3 删除 name =5 的记录
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

use test;
delete from t1 where name='b'; ==>被阻塞

*************************** 1. row ***************************
    waiting_trx_id: 29501
    waiting_thread: 2
         wait_time: 2897
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: idx_id
   blocking_trx_id: 29496
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 2942
    blocking_query: NULL
*************************** 2. row ***************************
    waiting_trx_id: 29503
    waiting_thread: 3
         wait_time: 116
     waiting_query: delete from t1 where name='b'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 29496
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 2942
    blocking_query: NULL
2 rows in set (0.01 sec)

SELECT * FROM information_schema.`INNODB_LOCKS`/G

*************************** 1. row ***************************
    lock_id: 29503:248:3:4 ==> SESSION 3 的锁ID
lock_trx_id: 29503
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b' ==> SESSION 3 在等待主键索引键值为 b 的索引项上的锁
*************************** 2. row ***************************
    lock_id: 29496:248:3:4 ==> SESSION 1 的锁ID
lock_trx_id: 29496
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b' ==> SESSION 1 持有主键索引键值为 b 的索引项上的锁
*************************** 3. row ***************************
    lock_id: 29501:248:4:4
lock_trx_id: 29501
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 4
lock_data: 10, 'b'
*************************** 4. row ***************************
    lock_id: 29496:248:4:4
lock_trx_id: 29496
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 4
lock_data: 10, 'b'
4 rows in set (0.00 sec)

SHOW ENGINE innodb status/G

————
TRANSACTIONS
————
Trx id counter 29504
Purge done for trx's n:o < 29501 undo n:o < 0 state: running but idle
History list length 755
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started
MySQL thread id 5, OS thread handle 0x7f8e1ffff700, query id 74 localhost root init
SHOW ENGINE innodb status
—TRANSACTION 29502, not started
MySQL thread id 4, OS thread handle 0x7f8e54c43700, query id 51 localhost root cleaning up
—TRANSACTION 29503, ACTIVE 1137 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x7f8e54c84700, query id 70 localhost root updating
delete from t1 where name='b'
——- TRX HAS BEEN WAITING 1137 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 29503 lock_mode X locks rec but not gap waiting
==> SESSION 3在主键上有排他的行锁等待，但没有间隙锁等待
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000007338; asc     s8;;
2: len 7; hex 240000015221ce; asc $   R! ;;
3: len 4; hex 8000000a; asc     ;;

——————
—TRANSACTION 29501, ACTIVE 3918 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7f8e54cc5700, query id 48 localhost root updating
delete from t1 where id=10
——- TRX HAS BEEN WAITING 3918 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 4 n bits 80 index `idx_id` of table `test`.`t1` trx id 29501 lock_mode X locks rec but not gap waiting
==> SESSION 2 在非唯一索引 idx_id 有排他行锁等待，但没有间隙锁等待
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 62; asc b;;

——————
—TRANSACTION 29496, ACTIVE 3963 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f8e54d06700, query id 37 localhost root cleaning up

–SESSION 4 删除 name='d' 的记录
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

use test;
delete from t1 where name='d'; ==> 被阻塞

*************************** 1. row ***************************
    waiting_trx_id: 29501
    waiting_thread: 2
         wait_time: 4865
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: idx_id
   blocking_trx_id: 29496
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 4910
    blocking_query: NULL
*************************** 2. row ***************************
    waiting_trx_id: 29503
    waiting_thread: 3
         wait_time: 2084
     waiting_query: delete from t1 where name='b'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 29496
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 4910
    blocking_query: NULL
*************************** 3. row ***************************
    waiting_trx_id: 29504
    waiting_thread: 4 ==> SESSION 4
         wait_time: 24
     waiting_query: delete from t1 where name='d'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 29496
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 4910
    blocking_query: NULL
3 rows in set (0.00 sec)

SELECT * FROM information_schema.`INNODB_LOCKS`/G

*************************** 1. row ***************************
    lock_id: 29504:248:3:5 ==> SESSION 4 的锁ID
lock_trx_id: 29504
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 5
lock_data: 'd' ==> SESSION 4 在等待主键索引键值为 d 的索引项上的锁
*************************** 2. row ***************************
    lock_id: 29496:248:3:5 ==> SESSION 1 的锁ID
lock_trx_id: 29496
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 5
lock_data: 'd' ==> SESSION 1 持有主键索引键值为 d 的索引项上的锁
*************************** 3. row ***************************
    lock_id: 29503:248:3:4
lock_trx_id: 29503
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b'
*************************** 4. row ***************************
    lock_id: 29496:248:3:4
lock_trx_id: 29496
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b'
*************************** 5. row ***************************
    lock_id: 29501:248:4:4
lock_trx_id: 29501
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 4
lock_data: 10, 'b'
*************************** 6. row ***************************
    lock_id: 29496:248:4:4
lock_trx_id: 29496
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 4
lock_data: 10, 'b'
6 rows in set (0.00 sec)

SHOW ENGINE innodb status/G

————
TRANSACTIONS
————
Trx id counter 29505
Purge done for trx's n:o < 29501 undo n:o < 0 state: running but idle
History list length 755
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0, not started
MySQL thread id 5, OS thread handle 0x7f8e1ffff700, query id 89 localhost root init
SHOW ENGINE innodb status
—TRANSACTION 29504, ACTIVE 736 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f8e54c43700, query id 86 localhost root updating
delete from t1 where name='d'
——- TRX HAS BEEN WAITING 736 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248（行锁所在表空间是248） page no 3（数据页是3） n bits 80 index `PRIMARY` of table `test`.`t1` trx id 29504 lock_mode X locks rec but not gap waiting
==> SESSION 4 在主键上等待排他行锁，但没有等待间隙锁
Record lock, heap no 5 PHYSICAL RECORD（数据行5；和起来理解就是行锁在248号表空间的第三个数据页的第五行）: n_fields 4; compact format; info bits 32
0: len 1; hex 64; asc d;;
1: len 6; hex 000000007338; asc     s8;;
2: len 7; hex 240000015221f1; asc $   R! ;;
3: len 4; hex 8000000a; asc     ;;

——————
—TRANSACTION 29503, ACTIVE 2796 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x7f8e54c84700, query id 70 localhost root updating
delete from t1 where name='b'
——- TRX HAS BEEN WAITING 2796 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 3 n bits 80 index `PRIMARY` of table `test`.`t1` trx id 29503 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 1; hex 62; asc b;;
1: len 6; hex 000000007338; asc     s8;;
2: len 7; hex 240000015221ce; asc $   R! ;;
3: len 4; hex 8000000a; asc     ;;

——————
—TRANSACTION 29501, ACTIVE 5577 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x7f8e54cc5700, query id 48 localhost root updating
delete from t1 where id=10
——- TRX HAS BEEN WAITING 5577 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 248 page no 4 n bits 80 index `idx_id` of table `test`.`t1` trx id 29501 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 8000000a; asc ;;
1: len 1; hex 62; asc b;;

——————
—TRANSACTION 29496, ACTIVE 5622 sec
3 lock struct(s), heap size 360, 4 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f8e54d06700, query id 37 localhost root cleaning up

结论：
通过上面的测试我们可以推测出 SESSION 1 执行的 delete from t1 where id=10，会在非唯一索引 idx_id
上的索引键值为10的两个索引项上添加行级排他锁，同时会在主键上键值为b和d的两个索引项上添加行级排他
锁。

组合四：id无索引+RC

–删除 idx_id 索引
use test;
DROP INDEX idx_id on t1;

–SESSION 1 删除ID=10的记录

set global tx_isolation='read-committed';
set session tx_isolation='read-committed';

set autocommit=0;

use test;
delete from t1 where id=10;

–SESSION 2删除ID=10的记录
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

use test;
delete from t1 where id=10; ==>被阻塞

–SESSION 5 查看锁信息

*************************** 1. row ***************************
    waiting_trx_id: 30476
    waiting_thread: 2
         wait_time: 15
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30471
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 39
    blocking_query: NULL
1 row in set (0.11 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G

*************************** 1. row ***************************
    lock_id: 30476:248:3:4
lock_trx_id: 30476
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b'
*************************** 2. row ***************************
    lock_id: 30471:248:3:4
lock_trx_id: 30471
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b'
2 rows in set (0.00 sec)

–SESSION 3 删除ID=15 的记录
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

use test;
delete from t1 where id=15; ==>被阻塞

–SESSION 4 删除 id=2 的记录
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

use test;
delete from t1 where id=2; ==>被阻塞

–SESSION 6 删除 name='zz'
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;
use test;
delete from t1 where name='zz'; ==>不会被阻塞

–SESSION 7 删除 name='a' 的记录 (15,a)
set global tx_isolation='read-committed';
set session tx_isolation='read-committed';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

use test;
delete from t1 where name='a'; ==>被阻塞

–SESSION 5 查看锁信息

SELECT CONCAT('thread ',b.trx_mysql_thread_id,' from ',p.host) AS who_blocks,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       MAX(TIMESTAMPDIFF(SECOND,r.trx_wait_started,NOW())) AS max_wait_time,
       COUNT(*) AS num_waiters
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.`blocking_trx_id`
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.`trx_id`= w.`requesting_trx_id`
LEFT JOIN   information_schema.`PROCESSLIST` AS p ON p.id= b.`trx_mysql_thread_id`
GROUP BY who_blocks ORDER BY num_waiters DESC/G

*************************** 1. row ***************************
   who_blocks: thread 3 from localhost
idle_in_trx: 0
max_wait_time: 363
num_waiters: 2 ==>在下条SQL查询中可以看到 SESSION 3阻塞了SESSION 4和SESSION 7
*************************** 2. row ***************************
   who_blocks: thread 1 from localhost
idle_in_trx: 554
max_wait_time: 530
num_waiters: 2 ==> 在下条SQL查询中可以看到 SESSION 1阻塞了SESSION 2和SESSION 3
*************************** 3. row ***************************
   who_blocks: thread 4 from localhost
idle_in_trx: 0
max_wait_time: 202
num_waiters: 1 ==> 在下条SQL查询中可以看到 SESSION 4阻塞了SESSION 7
*************************** 4. row ***************************
   who_blocks: thread 2 from localhost
idle_in_trx: 0
max_wait_time: 384
num_waiters: 1 ==> 在下条SQL查询中可以看到 SESSION 2阻塞了SESSION 3
4 rows in set (0.00 sec)

*************************** 1. row ***************************
    waiting_trx_id: 30476
    waiting_thread: 2
         wait_time: 374
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30471
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 398
    blocking_query: NULL
*************************** 2. row ***************************
    waiting_trx_id: 30477
    waiting_thread: 3
         wait_time: 228
     waiting_query: delete from t1 where id=15
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30471
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 398
    blocking_query: NULL
*************************** 3. row ***************************
    waiting_trx_id: 30477
    waiting_thread: 3
         wait_time: 228
     waiting_query: delete from t1 where id=15
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30476
   blocking_thread: 2
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=10
*************************** 4. row ***************************
    waiting_trx_id: 30478
    waiting_thread: 4
         wait_time: 207
     waiting_query: delete from t1 where id=2
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=15
*************************** 5. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 46
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30478
   blocking_thread: 4
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=2
*************************** 6. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 46
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=15
6 rows in set (0.00 sec)

SELECT * FROM information_schema.`INNODB_LOCKS`/G

*************************** 1. row ***************************
    lock_id: 30485:248:3:7
lock_trx_id: 30485
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 2. row ***************************
    lock_id: 30478:248:3:7
lock_trx_id: 30478
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 3. row ***************************
    lock_id: 30477:248:3:7
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 4. row ***************************
    lock_id: 30477:248:3:4
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b'
*************************** 5. row ***************************
    lock_id: 30476:248:3:4
lock_trx_id: 30476
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b'
*************************** 6. row ***************************
    lock_id: 30471:248:3:4
lock_trx_id: 30471
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b'
6 rows in set (0.00 sec)

各SESSION 执行的SQL语句：
SESSION 1：delete from t1 where id=10; ==>执行成功

SESSION 2：delete from t1 where id=10;

SESSION 3：delete from t1 where id=15;

SESSION 4：delete from t1 where id=2;

SESSION 6: delete from t1 where name='zz'; ==>执行成功

SESSION 7: delete from t1 where name='a';

表数据：
mysql> select * from t1;
+——+——+
| id   | name |
+——+——+
|   15 | a    |
|   10 | b    |
|    6 | c    |
|   10 | d    |
|   11 | f    |
|    2 | zz   |
+——+——+
6 rows in set (0.00 sec)

SESSION 1：delete from t1 where id=10; ==>执行成功

*************************** 6. row ***************************
lock_id: 30471:248:3:4
lock_trx_id: 30471
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
lock_rec: 4
lock_data: 'b'
6 rows in set (0.00 sec)

delete from t1 where id=10 锁住了整主键但只会只会阻塞 where id= .. （WHERE 条件中使用了ID字段）的语句，
对于 where name=值的语句只要不需要锁定name='a' 的主键索引项是不会阻塞的，后面的实验可以说明这点。

SESSION 3 被阻塞在主键键值为 a和b 的行上（被阻塞在两个主键键值上）。

*************************** 2. row ***************************
    waiting_trx_id: 30477 ==> SESSION 3 的事务ID
    waiting_thread: 3
         wait_time: 228
     waiting_query: delete from t1 where id=15
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30471
   blocking_thread: 1 ==> SESSIO 1 阻塞 SESSION 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 398
    blocking_query: NULL

*************************** 3. row ***************************
    lock_id: 30477:248:3:7 ==> SESSION 3 的锁ID
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a' ==> SESSION 3 被阻塞在主键索引键值为 a 的行
*************************** 4. row ***************************
    lock_id: 30477:248:3:4 ==> SESSION 3 的锁ID
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 4
lock_data: 'b' ==> SESSION 3 被阻塞在主键索引键值为 b 的行

SESSION 4 锁信息：
*************************** 4. row ***************************
    waiting_trx_id: 30478
    waiting_thread: 4
         wait_time: 207
     waiting_query: delete from t1 where id=2
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=15

SESSION 4被 SESSION 3阻塞。

*************************** 2. row ***************************
lock_id: 30478:248:3:7
lock_trx_id: 30478
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
lock_rec: 7
lock_data: 'a' ==> SESSION 4 被阻塞在主键索引键值为 a 的行

SESSION 6 执行 delete from t1 where name='zz' 不会被阻塞，证明了 SESSION 1 delete from t1 where id=10 锁住了整主键但只会只会阻塞
where id= .. （WHERE 条件中使用了ID字段）的语句，对于 where name=值的语句只要不需要锁定name='a' 的主键索引项是不会阻塞的

SHOW ENGINE innodb status/G

SESSION 7 执行 delete from t1 where name='a' 被阻塞，下面是 SESSION 7的锁信息

*************************** 5. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 46
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30478
   blocking_thread: 4
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=2
*************************** 6. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 46
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=15
6 rows in set (0.00 sec)

从上面的信息看到 SESSION 7 被 SESSION 3和 SESSION 4 阻塞。

SESSION 7 被阻塞在主键索引键值为 a 的行

======================================================================
现在提交 SESSION 1 的事务，SESSION 4应该会被 SESSION 6阻塞。

SESSION 1 提交事务：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

此时发现 SESSION 2/3/4/7 都还是被阻塞的

    -> GROUP BY who_blocks ORDER BY num_waiters DESC/G
*************************** 1. row ***************************
   who_blocks: thread 6 from localhost ==> SESSION 6 阻塞了2个SESSION
idle_in_trx: 5288
max_wait_time: 74
num_waiters: 2
*************************** 2. row ***************************
   who_blocks: thread 3 from localhost ==> SESSION 3 阻塞了2个SESSION
idle_in_trx: 0
max_wait_time: 5385
num_waiters: 2
*************************** 3. row ***************************
   who_blocks: thread 4 from localhost ==> SESSION 4 阻塞了1 个SESSION
idle_in_trx: 0
max_wait_time: 5224
num_waiters: 1
*************************** 4. row ***************************
   who_blocks: thread 2 from localhost ==> SESSION 2 阻塞了1 个SESSION
idle_in_trx: 0
max_wait_time: 74
num_waiters: 1
4 rows in set (0.00 sec)

检查 SESSION 6 阻塞了哪两个 SESSION
SELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
       r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
       l.lock_index AS waiting_index_lock,
       b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
       SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
       SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC/G

…
    blocking_query: delete from t1 where id=2
*************************** 4. row ***************************
    waiting_trx_id: 30476
    waiting_thread: 2
         wait_time: 221
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30480
   blocking_thread: 6
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 5435
    blocking_query: NULL

…
*************************** 6. row ***************************
    waiting_trx_id: 30477
    waiting_thread: 3
         wait_time: 221
     waiting_query: delete from t1 where id=15
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30480
   blocking_thread: 6
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 5435
    blocking_query: NULL
6 rows in set (0.00 sec)

我们看到 SESSION 6 阻塞了SESSION 3和SESION 2。

SELECT * FROM information_schema.`INNODB_LOCKS`/G

…
*************************** 4. row ***************************
    lock_id: 30477:248:3:2
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
*************************** 5. row ***************************
    lock_id: 30476:248:3:2
lock_trx_id: 30476
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
*************************** 6. row ***************************
    lock_id: 30480:248:3:2
lock_trx_id: 30480
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
6 rows in set (0.00 sec)

SESSION 6 执行 delete from t1 where name='zz' 锁定了主键为 ZZ 的记录，而 SESSION 2 执行 delete from t1 where id=10
和 SESSION 3 delete from t1 where id=15 因为 ID 列没有索引需要锁住整个主键，所以被 SESION 6 阻塞。

SESSION 4 被 SESSON 3阻塞
ELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
       r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
       l.lock_index AS waiting_index_lock,
       b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
       SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
       SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC/G

*************************** 1. row ***************************
    waiting_trx_id: 30478
    waiting_thread: 4
         wait_time: 5532
     waiting_query: delete from t1 where id=2
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=15

…

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30485:248:3:7
lock_trx_id: 30485
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 2. row ***************************
    lock_id: 30478:248:3:7 ==> SESSION 4 的锁ID
lock_trx_id: 30478
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 3. row ***************************
    lock_id: 30477:248:3:7 ==> SESSION 3 的锁ID
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 4. row ***************************
    lock_id: 30477:248:3:2 ==> SESSION 3 的锁ID
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
*************************** 5. row ***************************
    lock_id: 30476:248:3:2 ==> SESSION 2 的锁ID
lock_trx_id: 30476
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
*************************** 6. row ***************************
    lock_id: 30480:248:3:2 ==> SESSION 6 的锁ID
lock_trx_id: 30480
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
6 rows in set (0.00 sec)

SESSION 6 阻塞 SESSION 2和 SESSION 3 ，SESSION 2阻塞了SESSION 3， SESSION 3 阻塞SESSION 4。这种情况下提交SESSION 6 的事务，
SESSION 2 SQL 可以执行成功，但SESSION 3、 SESION 4 和 SESSION 7 仍被阻塞。

SESSION 7 被 SESSION 3 和 SESSION 4 阻塞

…

*************************** 2. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 5371
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=15
*************************** 3. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 5371
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30478
   blocking_thread: 4
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=2

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30485:248:3:7 ==> SESSION 7 的锁ID
lock_trx_id: 30485
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 2. row ***************************
    lock_id: 30478:248:3:7 ==> SESSION 4 的锁ID
lock_trx_id: 30478
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 3. row ***************************
    lock_id: 30477:248:3:7 ==> SESSION 3 的锁ID
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 7
lock_data: 'a'
*************************** 4. row ***************************
    lock_id: 30477:248:3:2 ==> SESSION 3 的锁ID
lock_trx_id: 30477
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
*************************** 5. row ***************************
    lock_id: 30476:248:3:2 ==> SESSION 2 的锁ID
lock_trx_id: 30476
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
*************************** 6. row ***************************
    lock_id: 30480:248:3:2 ==> SESSION 6 的锁ID
lock_trx_id: 30480
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'zz'
6 rows in set (0.00 sec)

SESSION 3 执行 delete from t1 where id=15 需要锁定整个主键，SESSION 4 执行 delete from t1 where id=2
需要锁定整个主键， SESSION 7 delete from t1 where name='a' 需要锁定主键键值为 a 的索引项，所以被 SESION
3 和 SESSION 4 阻塞。

SESSION 6 提交事务：
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

SESSION 2：
mysql> delete from t1 where id=10;
Query OK, 0 rows affected (2 hours 4 min 18.26 sec)

SESSION 3：
mysql> delete from t1 where id=15;
Query OK, 1 row affected (2 hours 1 min 52.24 sec)

我们看到 SESION 2和 SESSION 3 SQL都执行成功，与我们预想的 SESSION 2 SQL执行成功，SESSION 3 被 SESSION 2 阻塞的不一致，
原因是 id=10 的数据已经被 SESSION 1 删除了， SESSION 2 没有需要删除的数据，也就不需要加锁，所以SESSION 3 没有被SESION
2 阻塞。

现在的情况是 SESSION 6 提交事务后，SESSION 4 和 SESSION 7 处于被阻塞状态。

*************************** 1. row ***************************
    waiting_trx_id: 30478
    waiting_thread: 4
         wait_time: 7682
     waiting_query: delete from t1 where id=2
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 7703
    blocking_query: NULL
*************************** 2. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 7521
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30477
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 7703
    blocking_query: NULL
*************************** 3. row ***************************
    waiting_trx_id: 30485
    waiting_thread: 7
         wait_time: 7521
     waiting_query: delete from t1 where name='a'
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30478
   blocking_thread: 4
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=2
3 rows in set (0.00 sec)

我们看到 SESSION 3 阻塞了 SESSION 4和 SESSION 7 ，SESSION 4 阻塞了 SESSION 7（这只是假象其实SESSION 4 没有阻塞
SESSION 7，因为SESSION 4 执行的是 delete from t1 where id=2 会锁住主键但只会阻塞在 WHERE 字句中使用了 ID 字段
的SQL，不会阻塞在WHERE 字句中没有使用ID 字段并且与SESSION 4 改写不同记录的SQL，SESSSION 7 执行的SQL是 Selete from
t1 where name='a' 在 WHERE 字句中没有使用 ID 字段）。

我们看到 SESSION 3/4/7 都需要锁定主键键值为 a 的记录。
SESSION 4 执行 delete from t1 where id=2 需要锁住整个主键。
SESSION 7 执行 delete from t1 where name='a'，由于 NAME是主键列只需要锁定主键键值为 a 的记录。

各SESSION 执行的SQL语句：

SESSION 3：delete from t1 where id=15; ==>执行成功，但还没提交事务

SESSION 4：delete from t1 where id=2;

SESSION 7: delete from t1 where name='a';

=====================================================================================
提交 SESSION 3后SESSION 4和 SESSION 7 的SQL 都执行成功。

SESSION 2/4/7 提交事务。

问题：如果SESSON 1 执行 delete from t1 where id=15 查看锁信息时是否会显示都是被 SESSION 1阻塞的。
答：不会，以下测试可以证明。

delete from t1;

insert into t1 values(1,'f'),(2,'zz'),(3,'b'),(15,'a'),(6,'c'),(10,'d');

commit;

mysql> select * from t1;
+——+——+
| id   | name |
+——+——+
|   15 | a    |
|    3 | b    |
|    6 | c    |
|   10 | d    |
|    1 | f    |
|    2 | zz   |
+——+——+
6 rows in set (0.00 sec)

SESSION 1:
delete from t1 where id=15

SESSION 2:
delete from t1 where id=2; ==> 被阻塞

SESSION 3:
delete from t1 where id=10; ==> 被阻塞

SESSION 4:
delete from t1 where id=1; ==> 被阻塞

*************************** 1. row ***************************
   who_blocks: thread 1 from localhost
idle_in_trx: 153
max_wait_time: 80
num_waiters: 3
*************************** 2. row ***************************
   who_blocks: thread 2 from localhost
idle_in_trx: 0
max_wait_time: 46
num_waiters: 2
*************************** 3. row ***************************
   who_blocks: thread 3 from localhost
idle_in_trx: 0
max_wait_time: 11
num_waiters: 1
3 rows in set (0.00 sec)

*************************** 1. row ***************************
    waiting_trx_id: 30516
    waiting_thread: 2
         wait_time: 113
     waiting_query: delete from t1 where id=2
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30514
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 186
    blocking_query: NULL
*************************** 2. row ***************************
    waiting_trx_id: 30517
    waiting_thread: 3
         wait_time: 79
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30514
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 186
    blocking_query: NULL
*************************** 3. row ***************************
    waiting_trx_id: 30517
    waiting_thread: 3
         wait_time: 79
     waiting_query: delete from t1 where id=10
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30516
   blocking_thread: 2
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=2
*************************** 4. row ***************************
    waiting_trx_id: 30518
    waiting_thread: 4
         wait_time: 44
     waiting_query: delete from t1 where id=1
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30514
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 186
    blocking_query: NULL
*************************** 5. row ***************************
    waiting_trx_id: 30518
    waiting_thread: 4
         wait_time: 44
     waiting_query: delete from t1 where id=1
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30516
   blocking_thread: 2
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=2
*************************** 6. row ***************************
    waiting_trx_id: 30518
    waiting_thread: 4
         wait_time: 44
     waiting_query: delete from t1 where id=1
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30517
   blocking_thread: 3
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 0
    blocking_query: delete from t1 where id=10
6 rows in set (0.01 sec)

组合四-2：id和ID2无索引+RC
CREATE TABLE t2(id int,id2 int,name varchar(20));

alter table t2 add primary key(name);

insert into t2 values(1,31,'f'),(2,32,'zz'),(3,33,'b'),(15,45,'a'),(6,36,'c'),(10,40,'d');

commit;

SESSION 1 :
delete from t2 where id=15;

SESSION 2:
delete from t2 where id2=32; ==>被阻塞

SESSION 5 查看锁信息：

*************************** 1. row ***************************
    waiting_trx_id: 30564
    waiting_thread: 2
         wait_time: 38
     waiting_query: delete from t2 where id2=32
waiting_table_lock: `test`.`t2`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30562
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 76
    blocking_query: NULL
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30564:250:3:5 ==> SESSION 2 锁ID
lock_trx_id: 30564
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t2`
lock_index: PRIMARY
lock_space: 250
lock_page: 3
   lock_rec: 5
lock_data: 'a'
*************************** 2. row ***************************
    lock_id: 30562:250:3:5 ==> SESSION 1 锁ID
lock_trx_id: 30562
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t2`
lock_index: PRIMARY
lock_space: 250
lock_page: 3
   lock_rec: 5
lock_data: 'a'
2 rows in set (0.00 sec)

SESSION 1 和 SESSION 2 回滚事务

SESSION 1 :
delete from t2 where id in (2,10,3);

SESSION 2:
delete from t2 where id2 in (45,36,31); ==>被阻塞

*************************** 1. row ***************************
    waiting_trx_id: 30576
    waiting_thread: 2
         wait_time: 232
     waiting_query: delete from t2 where id2 in (45,36,31)
waiting_table_lock: `test`.`t2`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30571
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 241
    blocking_query: NULL
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30576:250:3:4
lock_trx_id: 30576
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t2`
lock_index: PRIMARY
lock_space: 250
lock_page: 3
   lock_rec: 4
lock_data: 'b'
*************************** 2. row ***************************
    lock_id: 30571:250:3:4
lock_trx_id: 30571
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t2`
lock_index: PRIMARY
lock_space: 250
lock_page: 3
   lock_rec: 4
lock_data: 'b'
2 rows in set (0.00 sec)

mysql> select * from t2;
+——+——+——+
| id   | id2 | name |
+——+——+——+
|   15 |   45 | a    |
|    3 |   33 | b    |
|    6 |   36 | c    |
|   10 |   40 | d    |
|    1 |   31 | f    |
|    2 |   32 | zz   |
+——+——+——+
6 rows in set (0.00 sec)

从上面的锁信息我们看到虽然SQL语句需要删除多条记录，但SESSION 2 只显示在主键健值为 a 的记录上
被阻塞，由此证明MYSQL INNODB 是单条记录进行加上，需要在第一条符合过滤条件的记录加上成本执行
完DELETE后才会再对第二条符合过滤条件的记录进行加锁。
SESSION 1 SQL执行完后，对所有符合过滤条件的记录都加了锁，不提交或回滚事务不会释放。

SESSINO 1 和 SESSION 2 回滚事务。

结论：
不同列在无索引的情况下，SESSION 1 在 WHERE 字句中使用ID，SESSION 2 在 WHERE 字句使用ID2，
SESSION 1和 SESSION 2 都会要求锁定整个主键，两个SESSION 更新不同的记录也会出现阻塞。

SESSINO 1：
mysql> select * from t2;
+——+——+——+
| id   | id2 | name |
+——+——+——+
|   15 |   45 | a    |
|    3 |   33 | b    |
|    6 |   36 | c    |
|   10 |   40 | d    |
|    1 |   31 | f    |
|    2 |   32 | zz   |
+——+——+——+
6 rows in set (0.00 sec)

mysql> delete from t2 where id=2;
Query OK, 1 row affected (0.00 sec)

SESSINO 2：
delete from t2 where id2=45; ==> 被阻塞

SESSION 5 :查看锁信息

*************************** 1. row ***************************
    waiting_trx_id: 30585
    waiting_thread: 2
         wait_time: 9
     waiting_query: delete from t2 where id2=45
waiting_table_lock: `test`.`t2`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30584
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 22
    blocking_query: NULL
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30585:250:3:3
lock_trx_id: 30585
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t2`
lock_index: PRIMARY
lock_space: 250
lock_page: 3
   lock_rec: 3
lock_data: 'zz'
*************************** 2. row ***************************
    lock_id: 30584:250:3:3
lock_trx_id: 30584
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t2`
lock_index: PRIMARY
lock_space: 250
lock_page: 3
   lock_rec: 3
lock_data: 'zz'
2 rows in set (0.00 sec)

从上面的锁信息看到 SESSION 1 只锁住了主键键值为 zz 的一条记录。SESSION 2被阻塞在
这条记录上。

SESSION 1和 SESSION 2 回滚事务。

结论：
若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。
因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于
不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的
记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。

组合五：id主键+RR，加锁与组合一：[id主键，Read Committed]一致。
组合六：id唯一索引+RR，与组合二：[id唯一索引，Read Committed]一致。

组合七：id非唯一索引+RR

delete from t1;

insert into t1 values(2,'zz'),(6,'c'),(10,'b'),(10,'d'),(11,'f'),(15,'a');

alter table t1 add INDEX idx_id (id);

mysql> show index from t1/G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
Seq_in_index: 1
Column_name: name
    Collation: A
Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: idx_id
Seq_in_index: 1
Column_name: id
    Collation: A
Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

mysql> select * from t1;
+——+——+
| id   | name |
+——+——+
|    1 | f    |
|    2 | zz   |
|    3 | b    |
|    6 | c    |
|   10 | d    |
|   15 | a    |
+——+——+
6 rows in set (0.00 sec)

SESSINO 1:
set global tx_isolation='REPEATABLE-READ';
set session tx_isolation='REPEATABLE-READ';

select @@global.tx_isolation,@@tx_isolation/G
*************************** 1. row ***************************
@@global.tx_isolation: REPEATABLE-READ
@@tx_isolation: REPEATABLE-READ
1 row in set (0.00 sec)

set autocommit=0;

delete from t1 where id = 10;

SESSINO 2:
set global tx_isolation='REPEATABLE-READ';
set session tx_isolation='REPEATABLE-READ';
set autocommit=0;
set session innodb_lock_wait_timeout=1000000;

insert into t1 values(10,'aa'); ==> 被阻塞

SESSION 5 查看锁信息：
SELECT r.trx_id AS waiting_trx_id,r.`trx_mysql_thread_id` AS waiting_thread,
       TIMESTAMPDIFF(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP) AS wait_time,
       r.`trx_query` AS waiting_query,l.`lock_table` AS waiting_table_lock,
       l.lock_index AS waiting_index_lock,
       b.trx_id AS blocking_trx_id,b.`trx_mysql_thread_id` AS blocking_thread,
       SUBSTRING(p.host,1,INSTR(p.host,':')-1) AS blocking_host,
       SUBSTRING(p.host,INSTR(p.host,':')+1) AS blocking_port,
       IF(p.command="Sleep",p.time,0) AS idle_in_trx,
       b.`trx_query` AS blocking_query
FROM information_schema.`INNODB_LOCK_WAITS` AS w
INNER JOIN information_schema.`INNODB_TRX` AS b ON b.trx_id=w.blocking_trx_id
INNER JOIN information_schema.`INNODB_TRX` AS r ON r.trx_id = w.requesting_trx_id
INNER JOIN information_schema.`INNODB_LOCKS` AS l ON w.requested_lock_id=l.lock_id
LEFT JOIN information_schema.`PROCESSLIST` AS p ON p.id=b.trx_mysql_thread_id
ORDER BY wait_time DESC/G

*************************** 1. row ***************************
    waiting_trx_id: 30617
    waiting_thread: 2
         wait_time: 20
     waiting_query: insert into t1 values(10,'aa')
waiting_table_lock: `test`.`t1`
waiting_index_lock: idx_id
   blocking_trx_id: 30618
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 27
    blocking_query: NULL
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30617:248:4:8 ==> SESSION 2 锁ID
lock_trx_id: 30617
lock_mode: X,GAP ==> SESSION 2 申请的锁模式有 X 和 GAP（间隙锁）
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 8
lock_data: 10, 'b'
*************************** 2. row ***************************
    lock_id: 30618:248:4:8
lock_trx_id: 30618
lock_mode: X ==> 虽然 SESSION 1 的锁信息没显示 GAP 模式，但实际上 SESSION 1 是加了 GAP锁的
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 8
lock_data: 10, 'b'
2 rows in set (0.00 sec)

SESSINO 2：Ctrl+C 终止SQL并回滚事务
mysql> insert into t1 values(10,'aa');
^CCtrl-C — sending "KILL QUERY 2" to server …
Ctrl-C — query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

SESSION 2：
insert into t1 values(7,'ab'); ==> 被阻塞

*************************** 1. row ***************************
    waiting_trx_id: 30624
    waiting_thread: 2
         wait_time: 17
     waiting_query: insert into t1 values(7,'ab')
waiting_table_lock: `test`.`t1`
waiting_index_lock: idx_id
   blocking_trx_id: 30618
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 626
    blocking_query: NULL
1 row in set (0.00 sec)

SESSION 1 阻塞了 SESSION 2。

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30624:248:4:8
lock_trx_id: 30624
lock_mode: X,GAP ==> SESSION 2 申请的锁模式有 X 和 GAP（间隙锁）
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 8
lock_data: 10, 'b'
*************************** 2. row ***************************
    lock_id: 30618:248:4:8
lock_trx_id: 30618
lock_mode: X ==> 虽然 SESSION 1 的锁信息没显示 GAP 模式，但实际上 SESSION 1 是加了 GAP锁的
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: idx_id
lock_space: 248
lock_page: 4
   lock_rec: 8
lock_data: 10, 'b'
2 rows in set (0.00 sec)

SESSINO2:
mysql> insert into t1 values(7,'ab');
^CCtrl-C — sending "KILL QUERY 2" to server …
Ctrl-C — query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

insert into t1 values(3,'bb'); ==> 执行成功
commit;

结论：
通过上面的测试推断出间隙锁只添加在 idx_id 索引上，主键上是没有间隙锁的。

组合八：id无索引+RR

delete from t1 where id=3;
commit;

alter table t1 drop index idx_id;

SESSION 1：
delete from t1 where id = 10;

SESSION 2：

insert into t1 values(3,'bb'); ==> 被阻塞

SESSION 3：
insert into t1 values(100,'zzz') ==>被阻塞

*************************** 1. row ***************************
    waiting_trx_id: 30646
    waiting_thread: 2
         wait_time: 311
     waiting_query: insert into t1 values(3,'bb')
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30641
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 340
    blocking_query: NULL
*************************** 2. row ***************************
    waiting_trx_id: 30647
    waiting_thread: 3
         wait_time: 32
     waiting_query: insert into t1 values(100,'zzz')
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30641
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 340
    blocking_query: NULL
2 rows in set (0.00 sec)

SESSION 1阻塞了SESSION 2 和 SESSION 3。

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30647:248:3:1
lock_trx_id: 30647
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 1
lock_data: supremum pseudo-record ==>SESSION 3 被SESSION 1 添加在主键上的超过字段最大值的间隙锁阻塞。
*************************** 2. row ***************************
    lock_id: 30641:248:3:1
lock_trx_id: 30641
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 1
lock_data: supremum pseudo-record ==>超过字段最大值的间隙锁
*************************** 3. row ***************************
    lock_id: 30646:248:3:3
lock_trx_id: 30646
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 3
lock_data: 'c' ==> SESSION 2 被SESSION 1 中添加在主键上name='c' 和 name='b' 之间的间隙锁阻塞。
*************************** 4. row ***************************
    lock_id: 30641:248:3:3
lock_trx_id: 30641
lock_mode: X ==> 虽然 SESSION 1 的锁信息没显示 GAP 模式，但实际上 SESSION 1 是加了 GAP锁的
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 3
lock_data: 'c'
4 rows in set (0.00 sec)

结论：
在 ID 字段没有索引的情况下，会对整个索引添加间隙锁。其他会话都无法插入新的数据。

问题：是否整个主键都加了行锁呢？

SESSION 2 终止 SQL，并回滚会话
mysql> insert into t1 values(3,'bb');
^CCtrl-C — sending "KILL QUERY 2" to server …
Ctrl-C — query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

SESSION 3 终止 SQL，并回滚会话
mysql> insert into t1 values(100,'zzz');
^CCtrl-C — sending "KILL QUERY 3" to server …
Ctrl-C — query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

SESSINO 2:
delete from t1 where id=2; ==> 被阻塞

SESSION 5 查看锁信息：

*************************** 1. row ***************************
    waiting_trx_id: 30649
    waiting_thread: 2
         wait_time: 27
     waiting_query: delete from t1 where id=2
waiting_table_lock: `test`.`t1`
waiting_index_lock: PRIMARY
   blocking_trx_id: 30641
   blocking_thread: 1
     blocking_host:
     blocking_port: localhost
       idle_in_trx: 1500
    blocking_query: NULL
1 row in set (0.00 sec)

mysql> SELECT * FROM information_schema.`INNODB_LOCKS`/G
*************************** 1. row ***************************
    lock_id: 30649:248:3:2
lock_trx_id: 30649
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'a' ==> SESSION 2 被 SESSION 1 阻塞在主键健值为 a 的记录上。
*************************** 2. row ***************************
    lock_id: 30641:248:3:2
lock_trx_id: 30641
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t1`
lock_index: PRIMARY
lock_space: 248
lock_page: 3
   lock_rec: 2
lock_data: 'a'
2 rows in set (0.00 sec)

SESSION 1 执行的SQL 是 delete from t1 where id = 10 对应的主键值是 b,
SESSION 2 执行的SQL 是 delete from t1 where id=2 对应的主键值是 zz。
现在的锁信息显示 SESSION 1 在主键值为 a 的记录上持有排他行锁，证明在RR 隔离等级 ID 列没有索引的情况下，
会对整个表加排他行锁和间隙锁。

关于如何进行MySQL加锁处理的分析问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。

原创文章，作者：kirin，如若转载，请注明出处：https://blog.ytso.com/204365.html

如何进行MySQL加锁处理的分析

相关推荐

发表回复