溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》
您好，登錄后才能下訂單哦！
如何進(jìn)行MySQL加鎖處理的分析，針對(duì)這個(gè)問(wèn)題，這篇文章詳細(xì)介紹了相對(duì)應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問(wèn)題的小伙伴找到更簡(jiǎn)單易行的方法。
1. 準(zhǔn)備測(cè)試數(shù)據(jù)
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. 執(zhí)行測(cè)試
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的索引項(xiàng)上加行級(jí)排他鎖
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的索引項(xiàng)上加行級(jí)排他鎖
*************************** 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的索引項(xiàng)上加行級(jí)排他鎖
2 rows in set (0.00 sec)
--SESSION 1 回滾事務(wù)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
--SESION 2回滾事務(wù)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
組合二：id唯一索引+RC
這個(gè)組合，id不是主鍵，而是一個(gè)Unique的二級(jí)索引鍵值。那么在RC隔離級(jí)別下，delete from t1 where id = 10; 需要加什么鎖呢？
--準(zhǔn)備測(cè)試數(shù)據(jù)
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)
從上面查詢(xún)出鎖的信息中沒(méi)看到鎖定主鍵相關(guān)記錄的信息。下面我們?cè)?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 線(xiàn)程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 線(xiàn)程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 線(xiàn)程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 線(xiàn)程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' ==>在主鍵的索引項(xiàng)鍵值為D的索引項(xiàng)上添加行級(jí)排它鎖
*************************** 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的記錄（索引項(xiàng)）上加行級(jí)排他鎖
4 rows in set (0.00 sec)
--SESSION 2發(fā)現(xiàn)死鎖（該問(wèn)題后續(xù)再研究）
mysql> delete from t1 where id=10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
--SESSION 1、2、4 回滾事務(wù)
在該組合的測(cè)試中，SESSION 1同時(shí)阻塞了 SESSION 2和 SESSION 4，由此證明刪除 ID=10 的記錄時(shí)
會(huì)同時(shí)在唯一索引和主鍵索引上加鎖行級(jí)排他鎖。
組合三：id列是二級(jí)非唯一索引，RC隔離級(jí)別
--準(zhǔn)備測(cè)試數(shù)據(jù)
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的數(shù)據(jù)
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的數(shù)據(jù)雖然有2條(10,b),(10,d)，但SESSION 2 只再 (10,b)的記錄
上等待鎖，這也說(shuō)明了 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 的索引項(xiàng)上的鎖
*************************** 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 的索引項(xiàng)上的鎖
*************************** 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在主鍵上有排他的行鎖等待，但沒(méi)有間隙鎖等待
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 有排他行鎖等待，但沒(méi)有間隙鎖等待
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 的索引項(xiàng)上的鎖
*************************** 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 的索引項(xiàng)上的鎖
*************************** 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（數(shù)據(jù)頁(yè)是3） n bits 80 index `PRIMARY` of table `test`.`t1` trx id 29504 lock_mode X locks rec but not gap waiting
==> SESSION 4 在主鍵上等待排他行鎖，但沒(méi)有等待間隙鎖
Record lock, heap no 5 PHYSICAL RECORD（數(shù)據(jù)行5；和起來(lái)理解就是行鎖在248號(hào)表空間的第三個(gè)數(shù)據(jù)頁(yè)的第五行）: 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
結(jié)論：
通過(guò)上面的測(cè)試我們可以推測(cè)出 SESSION 1 執(zhí)行的 delete from t1 where id=10，會(huì)在非唯一索引 idx_id
上的索引鍵值為10的兩個(gè)索引項(xiàng)上添加行級(jí)排他鎖，同時(shí)會(huì)在主鍵上鍵值為b和d的兩個(gè)索引項(xiàng)上添加行級(jí)排他
鎖。
組合四：id無(wú)索引+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'; ==>不會(huì)被阻塞
--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查詢(xún)中可以看到 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查詢(xún)中可以看到 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查詢(xún)中可以看到 SESSION 4阻塞了SESSION 7
*************************** 4. row ***************************
who_blocks: thread 2 from localhost
idle_in_trx: 0
max_wait_time: 384
num_waiters: 1 ==> 在下條SQL查詢(xún)中可以看到 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 執(zhí)行的SQL語(yǔ)句：
SESSION 1：delete from t1 where id=10; ==>執(zhí)行成功
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'; ==>執(zhí)行成功
SESSION 7: delete from t1 where name='a';
表數(shù)據(jù)：
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; ==>執(zhí)行成功
*************************** 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 鎖住了整主鍵但只會(huì)只會(huì)阻塞 where id= .. （WHERE 條件中使用了ID字段） 的 語(yǔ)句，
對(duì)于 where name=值 的語(yǔ)句只要不需要鎖定name='a' 的主鍵索引項(xiàng)是不會(huì)阻塞的，后面的實(shí)驗(yàn)可以說(shuō)明這點(diǎn)。
SESSION 3 被阻塞在主鍵鍵值為 a和b 的行上（被阻塞在兩個(gè)主鍵鍵值上）。
*************************** 2. row ***************************
waiting_trx_id: 30477 ==> SESSION 3 的事務(wù)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 執(zhí)行 delete from t1 where name='zz' 不會(huì)被阻塞，證明了 SESSION 1 delete from t1 where id=10 鎖住了整主鍵但只會(huì)只會(huì)阻塞
where id= .. （WHERE 條件中使用了ID字段） 的 語(yǔ)句，對(duì)于 where name=值 的語(yǔ)句只要不需要鎖定name='a' 的主鍵索引項(xiàng)是不會(huì)阻塞的
SHOW ENGINE innodb status\G
SESSION 7 執(zhí)行 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 的行
======================================================================
現(xiàn)在提交 SESSION 1 的事務(wù)，SESSION 4應(yīng)該會(huì)被 SESSION 6阻塞。
SESSION 1 提交事務(wù)：
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
此時(shí)發(fā)現(xiàn) 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個(gè)SESSION
idle_in_trx: 5288
max_wait_time: 74
num_waiters: 2
*************************** 2. row ***************************
who_blocks: thread 3 from localhost ==> SESSION 3 阻塞了2個(gè)SESSION
idle_in_trx: 0
max_wait_time: 5385
num_waiters: 2
*************************** 3. row ***************************
who_blocks: thread 4 from localhost ==> SESSION 4 阻塞了1 個(gè)SESSION
idle_in_trx: 0
max_wait_time: 5224
num_waiters: 1
*************************** 4. row ***************************
who_blocks: thread 2 from localhost ==> SESSION 2 阻塞了1 個(gè)SESSION
idle_in_trx: 0
max_wait_time: 74
num_waiters: 1
4 rows in set (0.00 sec)
檢查 SESSION 6 阻塞了哪兩個(gè) 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 執(zhí)行 delete from t1 where name='zz' 鎖定了主鍵為 ZZ 的記錄，而 SESSION 2 執(zhí)行 delete from t1 where id=10
和 SESSION 3 delete from t1 where id=15 因?yàn)?ID 列沒(méi)有索引需要鎖住整個(gè)主鍵，所以被 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 的事務(wù)，
SESSION 2 SQL 可以執(zhí)行成功，但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 執(zhí)行 delete from t1 where id=15 需要鎖定整個(gè)主鍵，SESSION 4 執(zhí)行 delete from t1 where id=2
需要鎖定整個(gè)主鍵， SESSION 7 delete from t1 where name='a' 需要鎖定主鍵鍵值為 a 的索引項(xiàng)，所以被 SESION
3 和 SESSION 4 阻塞。
SESSION 6 阻塞 SESSION 2和 SESSION 3 ，SESSION 2阻塞了SESSION 3， SESSION 3 阻塞SESSION 4。這種情況下提交SESSION 6 的事務(wù)，
SESSION 2 SQL 可以執(zhí)行成功，但SESSION 3、 SESION 4 和 SESSION 7 仍被阻塞。
SESSION 6 提交事務(wù)：
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都執(zhí)行成功，與我們預(yù)想的 SESSION 2 SQL執(zhí)行成功，SESSION 3 被 SESSION 2 阻塞的不一致，
原因是 id=10 的數(shù)據(jù)已經(jīng)被 SESSION 1 刪除了， SESSION 2 沒(méi)有需要?jiǎng)h除的數(shù)據(jù)，也就不需要加鎖，所以SESSION 3 沒(méi)有被SESION
2 阻塞。
現(xiàn)在的情況是 SESSION 6 提交事務(wù)后，SESSION 4 和 SESSION 7 處于被阻塞狀態(tài)。
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（這只是假象其實(shí)SESSION 4 沒(méi)有阻塞
SESSION 7，因?yàn)镾ESSION 4 執(zhí)行的是 delete from t1 where id=2 會(huì)鎖住主鍵但只會(huì)阻塞在 WHERE 字句中使用了 ID 字段
的SQL，不會(huì)阻塞在WHERE 字句中沒(méi)有使用ID 字段并且與SESSION 4 改寫(xiě)不同記錄的SQL，SESSSION 7 執(zhí)行的SQL是 Selete from
t1 where name='a' 在 WHERE 字句中沒(méi)有使用 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 執(zhí)行 delete from t1 where id=2 需要鎖住整個(gè)主鍵。
SESSION 7 執(zhí)行 delete from t1 where name='a'，由于 NAME是主鍵列只需要鎖定主鍵鍵值為 a 的記錄。
各SESSION 執(zhí)行的SQL語(yǔ)句：
SESSION 3：delete from t1 where id=15; ==>執(zhí)行成功，但還沒(méi)提交事務(wù)
SESSION 4：delete from t1 where id=2;
SESSION 7: delete from t1 where name='a';
=====================================================================================
提交 SESSION 3后SESSION 4和 SESSION 7 的SQL 都執(zhí)行成功。
SESSION 2/4/7 提交事務(wù)。
問(wèn)題：如果SESSON 1 執(zhí)行 delete from t1 where id=15 查看鎖信息時(shí)是否會(huì)顯示都是被 SESSION 1阻塞的。
答：不會(huì)，以下測(cè)試可以證明。
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無(wú)索引+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 回滾事務(wù)
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語(yǔ)句需要?jiǎng)h除多條記錄，但SESSION 2 只顯示在主鍵健值為 a 的記錄上
被阻塞，由此證明MYSQL INNODB 是單條記錄進(jìn)行加上，需要在第一條符合過(guò)濾條件的記錄加上成本執(zhí)行
完DELETE后才會(huì)再對(duì)第二條符合過(guò)濾條件的記錄進(jìn)行加鎖。
SESSION 1 SQL執(zhí)行完后，對(duì)所有符合過(guò)濾條件的記錄都加了鎖，不提交或回滾事務(wù)不會(huì)釋放。
SESSINO 1 和 SESSION 2 回滾事務(wù)。
結(jié)論：
不同列在無(wú)索引的情況下，SESSION 1 在 WHERE 字句中使用ID，SESSION 2 在 WHERE 字句使用ID2，
SESSION 1和 SESSION 2 都會(huì)要求鎖定整個(gè)主鍵，兩個(gè)SESSION 更新不同的記錄也會(huì)出現(xiàn)阻塞。
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 回滾事務(wù)。
結(jié)論：
若id列上沒(méi)有索引，SQL會(huì)走聚簇索引的全掃描進(jìn)行過(guò)濾，由于過(guò)濾是由MySQL Server層面進(jìn)行的。
因此每條記錄，無(wú)論是否滿(mǎn)足條件，都會(huì)被加上X鎖。但是，為了效率考量，MySQL做了優(yōu)化，對(duì)于
不滿(mǎn)足條件的記錄，會(huì)在判斷后放鎖，最終持有的，是滿(mǎn)足條件的記錄上的鎖，但是不滿(mǎn)足條件的
記錄上的加鎖/放鎖動(dòng)作不會(huì)省略。同時(shí)，優(yōu)化也違背了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 申請(qǐng)的鎖模式有 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 的鎖信息沒(méi)顯示 GAP 模式，但實(shí)際上 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并回滾事務(wù)
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 申請(qǐng)的鎖模式有 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 的鎖信息沒(méi)顯示 GAP 模式，但實(shí)際上 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'); ==> 執(zhí)行成功
commit;
結(jié)論：
通過(guò)上面的測(cè)試推斷出間隙鎖只添加在 idx_id 索引上，主鍵上是沒(méi)有間隙鎖的。
組合八：id無(wú)索引+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 添加在主鍵上的超過(guò)字段最大值的間隙鎖阻塞。
*************************** 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 ==>超過(guò)字段最大值的間隙鎖
*************************** 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 的鎖信息沒(méi)顯示 GAP 模式，但實(shí)際上 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)
結(jié)論：
在 ID 字段沒(méi)有索引的情況下，會(huì)對(duì)整個(gè)索引添加間隙鎖。其他會(huì)話(huà)都無(wú)法插入新的數(shù)據(jù)。
問(wèn)題：是否整個(gè)主鍵都加了行鎖呢？
SESSION 2 終止 SQL，并回滾會(huì)話(huà)
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，并回滾會(huì)話(huà)
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 執(zhí)行的SQL 是 delete from t1 where id = 10 對(duì)應(yīng)的主鍵值是 b,
SESSION 2 執(zhí)行的SQL 是 delete from t1 where id=2 對(duì)應(yīng)的主鍵值是 zz。
現(xiàn)在的鎖信息顯示 SESSION 1 在主鍵值為 a 的記錄上持有排他行鎖，證明在RR 隔離等級(jí) ID 列沒(méi)有索引的情況下，
會(huì)對(duì)整個(gè)表加排他行鎖和間隙鎖。
關(guān)于如何進(jìn)行MySQL加鎖處理的分析問(wèn)題的解答就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，如果你還有很多疑惑沒(méi)有解開(kāi)，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識(shí)。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼
