• 博客访问: 108982
  • 博文数量: 63
  • 用 户 组: 普通用户
  • 注册时间: 2012-02-10 17:23
个人简介

暂无介绍

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类
文章存档

2017年(9)

2016年(33)

2015年(12)

2014年(4)

2012年(5)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: 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';

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









阅读(127) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册