如何进行MySQL加锁处理的分析
发布时间：2021-12-20 10:24:31 所属栏目：通讯 来源：互联网
导读：如何进行MySQL加锁处理的分析，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。 1. 准备测试数据 use test; CREATE TABLE t1(id int,name varchar(20)); alter table t1 add primary key
|
如何进行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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 statusG ------------ 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 statusG ------------ 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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 statusG 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 DESCG -> GROUP BY who_blocks ORDER BY num_waiters DESCG *************************** 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 DESCG ... 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 DESCG *************************** 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 DESCG ... *************************** 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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 t1G *************************** 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_isolationG *************************** 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 DESCG *************************** 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 DESCG *************************** 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 t1G *************************** 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 DESCG *************************** 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 DESCG *************************** 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加锁处理的分析问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。 （编辑：上海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
