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';
select @@global.tx_isolation,@@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.00 sec)
组合一：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; ==>被阻塞
--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
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';
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED | 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)
mysql> show full processlist;
+----+------+-----------+------+---------+------+----------+----------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+----------------------------+
| 1 | root | localhost | test | Sleep | 995 | | NULL |
| 2 | root | localhost | test | Query | 950 | updating | delete from t1 where id=10 |
| 3 | root | localhost | NULL | Sleep | 1772 | | NULL |
| 4 | root | localhost | NULL | Sleep | 836 | | NULL |
| 5 | root | localhost | test | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+----------+----------------------------+
5 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'; ==>被阻塞
--在 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
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;
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:
1 row in set (0.00 sec)
--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 查看锁信息
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: 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)
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: 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; ==>执行成功
*************************** 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
*************************** 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 阻塞。
*************************** 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'
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 都还是被阻塞的
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
-> 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 阻塞
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
...
*************************** 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 阻塞 SESSION 2和 SESSION 3 ，SESSION 2阻塞了SESSION 3， SESSION 3 阻塞SESSION 4。这种情况下提交SESSION 6 的事务，
SESSION 2 SQL 可以执行成功，但SESSION 3、 SESION 4 和 SESSION 7 仍被阻塞。
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 处于被阻塞状态。
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: 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 字段)。
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'
3 rows in set (0.00 sec)
我们看到 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; ==> 被阻塞
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 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)
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: 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 查看锁信息：
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: 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); ==>被阻塞
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: 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 :查看锁信息
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: 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'); ==> 被阻塞
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: 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;
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:
1 row in set (0.00 sec)
SESSION 1：
delete from t1 where id = 10;
SESSION 2：
insert into t1 values(3,'bb'); ==> 被阻塞
SESSION 3：
insert into t1 values(100,'zzz') ==>被阻塞
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: 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 查看锁信息：
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: 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 加锁处理分析：http://hedengcheng.com/?p=771
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2136989/，如需转载，请注明出处，否则将追究法律责任。
554
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
