重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
如何进行innodb事务锁的研究
今天就跟大家聊聊有关如何进行innodb 事务锁的研究，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。
湘西土家族网站建设公司创新互联公司,湘西土家族网站设计制作，有大型网站制作公司丰富经验。已为湘西土家族1000+提供企业网站建设服务。企业网站搭建\外贸网站建设要多少钱，请找那个售后服务好的湘西土家族做网站的公司定做！
1. select * for update 语句添加的是排他行锁。
2. select ... from table_name where ... for update 语句在行计划使用索引常量查找或索引范围扫描时（索引覆盖查询的情况下）会在主键上添加排他行锁。
3. select .. for update 语句使用全索引扫描时，在使用覆盖索引的情况下也会对主键的所有记录添加排他行锁。
4. update 语句执行计划使用索引常量查找或索引范围扫描时，除了在辅助索引添加排他行锁也会在主键对应的记录上添加排他行锁（即便使用了覆盖索引也是如此）。
5. update 语句执行计划使用辅助索引全扫描时，除了在辅助索引的所有记录添加排他行锁也会在主键的所有记录上添加排他行锁（即便使用了覆盖索引也是如此）。
6. 测试辅助索引是唯一索引的情况下是否会有间隙锁
准备测试数据：
CREATE TABLE t5 (
a int(11) NOT NULL,
b int not null,
c int not null,
PRIMARY KEY (`a`),
UNIQUE key(b),
UNIQUE key(c)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
insert into t5 values(1,1,1);
insert into t5 values(2,2,2);
insert into t5 values(3,3,3);
insert into t5 values(4,4,4);
insert into t5 values(5,5,5);
insert into t5 values(6,6,6);
insert into t5 values(7,7,7);
MySQL> select * from t5;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 6 | 6 |
| 7 | 7 | 7 |
+---+---+---+
7 rows in set (0.00 sec)
1. select * for update 语句添加的是排他行锁。
--SESSION 1
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
set session innodb_lock_wait_timeout=1000000;
--session 2
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)
mysql> set session innodb_lock_wait_timeout=1000000;
Query OK, 0 rows affected (0.00 sec)
--SESSION 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3 where a=5 for update;
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
--SESSION 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t3 where a=5 for update; --被阻塞
--SESSION 3 查看锁信息
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675084:253:3:6
lock_trx_id: 324675084
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t3`
lock_index: PRIMARY
lock_space: 253
lock_page: 3
lock_rec: 6
lock_data: 5
*************************** 2. row ***************************
lock_id: 324675083:253:3:6
lock_trx_id: 324675083
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t3`
lock_index: PRIMARY
lock_space: 253
lock_page: 3
lock_rec: 6
lock_data: 5
2 rows in set (0.00 sec)
结论：
通过实验我们看到 select * from t3 where a=5 for update 添加到是排他行锁。
2. select ... from table_name where ... for update 语句在行计划使用索引常量查找或索引范围扫描时（索引覆盖查询的情况下）会在主键上添加排他行锁。
mysql> explain select b from t5 where b=5 for update\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: const
possible_keys: b
key: b
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
执行计划使用的是用索引常量查找。
--SESSION 1
mysql> begin;
mysql> select b from t5 where b=5 for update;
+---+
| b |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
--SESSION 2
mysql> select c from t5 where c=5 for update; --被阻塞
--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: 324675156
waiting_thread: 2
wait_time: 77
waiting_query: select c from t5 where c=5 for update
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324675155
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 150
blocking_query: NULL
1 row in set (0.00 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675156:255:3:6
lock_trx_id: 324675156
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 6
lock_data: 5
*************************** 2. row ***************************
lock_id: 324675155:255:3:6
lock_trx_id: 324675155
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 6
lock_data: 5
2 rows in set (0.00 sec)
回滚SESSION1 和 SESSION 2的事务
--SESSINO 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 where b=5 for update;
+---+
| b |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
--SESSION2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 where b=5 for update; --被阻塞
--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: 324675159
waiting_thread: 2
wait_time: 8
waiting_query: select b from t5 where b=5 for update
waiting_table_lock: `test`.`t5`
waiting_index_lock: b
blocking_trx_id: 324675158
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 21
blocking_query: NULL
1 row in set (0.00 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675159:255:4:6
lock_trx_id: 324675159
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 6
lock_data: 5
*************************** 2. row ***************************
lock_id: 324675158:255:4:6
lock_trx_id: 324675158
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 6
lock_data: 5
2 rows in set (0.00 sec)
我们看到 select b from t5 where b=5 for update 这条SQL语句在辅助索引 b 的索引键为5的索引项上添加了排他行锁。
通过上面两个例子我们看到 SESSION 1 执行的SQL的执行计划使用的是用索引常量查找,该SQL只会在辅助索引 b=5 的记录上加排他行锁，
同时会在主键对应的记录(a=5)的记录添加排他行锁。
3. select .. for update 语句使用全索引扫描时，在使用覆盖索引的情况下会对辅助索引所有的索引项加排他锁，同时会对主键的所有记录添加排他行锁。
mysql> explain select b from t5 for update\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: NULL
key: b
key_len: 4
ref: NULL
rows: 7
Extra: Using index
1 row in set (0.00 sec)
mysql> explain select C from t5 for update\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: NULL
key: c
key_len: 4
ref: NULL
rows: 7
Extra: Using index
1 row in set (0.00 sec)
上面两条SQL的执行计划都使用了覆盖索引进行了索引全扫描。
--SESSION 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 for update;
+---+
| b |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
--SESSION 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 for update; --被阻塞
--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: 324675162
waiting_thread: 2
wait_time: 19
waiting_query: select b from t5 for update
waiting_table_lock: `test`.`t5`
waiting_index_lock: b
blocking_trx_id: 324675161
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 29
blocking_query: NULL
1 row in set (0.00 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675162:255:4:2
lock_trx_id: 324675162
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 324675161:255:4:2
lock_trx_id: 324675161
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 2
lock_data: 1
2 rows in set (0.00 sec)
SESSION 2 被阻塞在辅助索引 b 的索引健值为 1 的索引项上。
SESSION 1和SESSION 2 回滚事务
--SESSINO 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 for update;
+---+
| b |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)
--SESSION 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 where b=7 for update; --被阻塞
--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: 324675164
waiting_thread: 2
wait_time: 41
waiting_query: select b from t5 where b=7 for update
waiting_table_lock: `test`.`t5`
waiting_index_lock: b
blocking_trx_id: 324675163
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 57
blocking_query: NULL
1 row in set (0.00 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675164:255:4:8
lock_trx_id: 324675164
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 8
lock_data: 7
*************************** 2. row ***************************
lock_id: 324675163:255:4:8
lock_trx_id: 324675163
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 8
lock_data: 7
2 rows in set (0.00 sec)
SESSION 2 被阻塞在辅助索引 b 的索引健值为 7 的索引项上。
结合SESSION 2 被阻塞在辅助索引 b 的索引健值为 1 的索引项上的情况，可以判定 select b from t5 for update 这条SQL
在辅助索引 b 的所有索引项上添加了排他行锁。
SESSION 1和 SESSION 2回滚事务。
--SESSION 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 for update;
+---+
| b |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)
--SESSION 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select c from t5 for update; --被阻塞
--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: 324675166
waiting_thread: 2
wait_time: 48
waiting_query: select c from t5 for update
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324675165
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 65
blocking_query: NULL
1 row in set (0.00 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675166:255:3:2
lock_trx_id: 324675166
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1
*************************** 2. row ***************************
lock_id: 324675165:255:3:2
lock_trx_id: 324675165
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1
2 rows in set (0.00 sec)
SESSION 2 被阻塞在主键健值为 1 的索引项上。
SESSION 1 和 SESSION 2回滚事务。
--SESSION 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select b from t5 for update;
+---+
| b |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
7 rows in set (0.00 sec)
--SESSION 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select c from t5 where c=7 for update; --被阻塞
--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: 324675168
waiting_thread: 2
wait_time: 44
waiting_query: select c from t5 where c=7 for update
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324675167
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 63
blocking_query: NULL
1 row in set (0.00 sec)
mysql>
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675168:255:3:8
lock_trx_id: 324675168
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 8
lock_data: 7
*************************** 2. row ***************************
lock_id: 324675167:255:3:8
lock_trx_id: 324675167
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 8
lock_data: 7
2 rows in set (0.00 sec)
SESSION 2 被阻塞在主键健值为 7 的索引项上。
结合SESSION 2 被阻塞在主键索引健值为 1 的索引项上的情况，可以判定 select b from t5 for update 这条SQL
在主键 的所有索引项上添加了排他行锁。
结合select b from t5 for update 这条SQL在辅助索引 b 的所有索引项上添加了排他行锁，判定 select .. for update 语句使用辅助索引（覆盖索引）
进行索引全扫描时会对辅助索引的所有索引项和主键的所有索引项添加排他行锁。
4. update 语句执行计划使用索引常量查找或索引范围扫描时，除了在辅助索引对应的索引项添加排他行锁也会在主键对应的记录上添加排他行锁（即便使用了覆盖索引也是如此）。
4.1 SQL语句的执行计划
sql_1
mysql> explain update t5 set b=b\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using temporary
1 row in set (0.00 sec)
sql_1 执行计划中type:index 表示按照索引顺序进行全表扫描，它的优点是避免了排序，缺点就是把全表扫描的连续IO 变成了随机IO。
sql_2
mysql> explain select b from t5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: NULL
key: b
key_len: 4
ref: NULL
rows: 7
Extra: Using index
1 row in set (0.00 sec)
SQL_2 执行计划使用的是覆盖索引。type: index 、 key: b、 Extra: Using index使用了覆盖索引全扫描。
SQL_3
mysql> explain update t5 set c=c\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using temporary
1 row in set (0.00 sec)
SQL_3 执行计划中type:index 表示按照索引顺序进行全表扫描，它的优点是避免了排序，缺点就是把全表扫描的连续IO 变成了随机IO。
SQL_4
mysql> explain update t5 set b=b where b=5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: range
possible_keys: b
key: b
key_len: 4
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
SQL_4 虽然只更新一条记录，但执行计划并没有使用常量检索，而是使用了索引范围扫描。
SQL_5
mysql> explain select b from t5 where b=5 for update\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: const
possible_keys: b
key: b
key_len: 4
ref: const
rows: 1
Extra: NULL
1 row in set (0.00 sec)
SQL_5 是 与 SQL_4 等价的 SELECT 语句，SQL_5就使用了常量检索，由此推断 UPDATE 语
句是无法使用常量检索。即便 UPDATE 操作的只是主键中的一行记录也不会使用常量检索。
SQL_6
mysql> explain update t5 set c=c where c=5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: range
possible_keys: c
key: c
key_len: 4
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
SQL_6 的执行计划是在辅助索引C上进行索引范围扫描。
SQL_7
mysql> explain update t5 set b=b where b in (1,3)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: range
possible_keys: b
key: b
key_len: 4
ref: const
rows: 2
Extra: Using where; Using temporary
1 row in set (0.00 sec)
SQL_7 通过在辅助索引 b 进行索引范围扫描，访问了2条记录后获得了需要的数据。
SQL_8
mysql> explain update t5 set b=b where b in (1,3,5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: b
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using where; Using temporary
1 row in set (0.00 sec)
SQL_8 是按照索引顺序进行全表扫描，它的优点是避免了排序，缺点就是把全表扫描的连续IO 变成了随机IO。
4.2 锁分析
--SESSION 1
mysql> use test
mysql> begin;
mysql> update t5 set b=b where b in (1,3);
--SESSION 2
mysql> use test;
mysql> begin;
mysql> update t5 set c=c where c in (1,3); --被阻塞
--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: 324675599 --SESSION 2 的事务ID，等待锁的事务ID
waiting_thread: 2 --等待锁的 MSYQL 线程 ID
wait_time: 30
waiting_query: update t5 set c=c where c in (1,3)
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324675598 --SESSION 1 的事务ID，持有锁的事务ID
blocking_thread: 1 --持有锁的MYSQL 线程ID
blocking_host:
blocking_port: localhost
idle_in_trx: 52
blocking_query: NULL
1 row in set (0.12 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675599:255:3:2
lock_trx_id: 324675599
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1 -- SESSION 2被阻塞在主键键值为1的索引项上
*************************** 2. row ***************************
lock_id: 324675598:255:3:2
lock_trx_id: 324675598
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1 --SESSION 1 持有主键健值为1的索引项上的排他行锁
2 rows in set (0.00 sec)
--SESSION 4
mysql>begin;
mysql> update t5 set c=c where c=3; --被阻塞
--SESSION 5
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t5 set b=b where b=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
SESSION 5的UPDATE语句没有被阻塞
--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: 324675599 --等待锁的事务ID（SESSION 2的事务ID）
waiting_thread: 2 --等待锁的MYSQL线程ID（SESSION 2 的 MYSQL 线程ID）
wait_time: 1081
waiting_query: update t5 set c=c where c in (1,3)
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324675598 --持有锁的事务ID （SESSION 1的事务ID）
blocking_thread: 1 --持有锁的MYSQL线程ID（SESSION 1的MSYQL 线程ID）
blocking_host:
blocking_port: localhost
idle_in_trx: 1103
blocking_query: NULL
*************************** 2. row ***************************
waiting_trx_id: 324675601 --等待锁的事务ID（SESSION 4的事务ID）
waiting_thread: 4 --等待锁的MYSQL线程ID（SESSION 4 的 MYSQL 线程ID）
wait_time: 63
waiting_query: update t5 set c=c where c=3
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324675598 --持有锁的事务ID （SESSION 1的事务ID）
blocking_thread: 1 --持有锁的MYSQL线程ID（SESSION 1的MSYQL 线程ID）
blocking_host:
blocking_port: localhost
idle_in_trx: 1103
blocking_query: NULL
2 rows in set (0.01 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324675601:255:3:4
lock_trx_id: 324675601
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 4
lock_data: 3 --SESSION 4 被阻塞在主键键值为3的索引项
*************************** 2. row ***************************
lock_id: 324675598:255:3:4
lock_trx_id: 324675598
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 4
lock_data: 3
*************************** 3. row ***************************
lock_id: 324675599:255:3:2
lock_trx_id: 324675599
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1 -- SESSION 2 被阻塞在主键键值为1的索引项
*************************** 4. row ***************************
lock_id: 324675598:255:3:2
lock_trx_id: 324675598
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1
4 rows in set (0.00 sec)
通过上面的测试我们看到，在辅助索引为唯一索引时，SQL语句执行计划为索引访问扫描或
常量检索时事务只会在符合 WHERE 字句过滤条件的辅助索引项和符合条件的主键索引项
上添加排他行锁，不符合过滤条件的索引项不会添加锁。
5. update 语句执行计划使用辅助索引全扫描时，除了在辅助索引的所有记录添加排他行锁也会在主键的所有记录上添加排他行锁（即便使用了覆盖索引也是如此）。
5.1 SQL 执行计划
SQL_1
mysql> explain update t5 set b=b where b in (1,3,5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: index
possible_keys: b
key: PRIMARY
key_len: 4
ref: NULL
rows: 7
Extra: Using where; Using temporary
1 row in set (0.00 sec)
SQL_1是按照索引顺序进行全表扫描，它的优点是避免了排序，缺点就是把全表扫描的连续IO 变成了随机IO。按照索引顺序进行全表扫描会在主键所有的主键索引项上添加排他行锁，
因为INNODB 的主键索引页子叶其实就是表的数据页，所以也就是在全表所有的记录上添加了排他行锁。
SQL_2
mysql> explain update t5 set c=c where c in (1,3)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: range
possible_keys: c
key: c
key_len: 4
ref: const
rows: 2
Extra: Using where; Using temporary
1 row in set (0.00 sec)
SQL_2 执行计划使用的是索引范围扫描。
5.2 锁分析
--SESSION 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t5 set b=b where b in (1,3,5);
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3 Changed: 0 Warnings: 0
--SESSION 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t5 set c=c where c in (1,3); --被阻塞
--SESSION 4
mysql> use test
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t5 set c=c where c=7; --被阻塞
SESSION 1 执行的是 SQL_1 ，该SQL使用的是按索引顺序进行全表扫描，会在主键所有的索引项上添加排他行锁，所以把 SESSION 4阻塞了。
--SESSIO 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: 324676114 --等待锁的事务ID（SESSION 2的事务ID）
waiting_thread: 2 --等待锁的MYSQL线程ID（SESSION 2 的 MYSQL 线程ID）
wait_time: 1212
waiting_query: update t5 set c=c where c in (1,3)
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324676113 --持有锁的事务ID （SESSION 1的事务ID）
blocking_thread: 1 --持有锁的MYSQL线程ID（SESSION 1的MSYQL 线程ID）
blocking_host:
blocking_port: localhost
idle_in_trx: 1224
blocking_query: NULL
*************************** 2. row ***************************
waiting_trx_id: 324676115 --等待锁的事务ID（SESSION 4的事务ID）
waiting_thread: 4 --等待锁的MYSQL线程ID（SESSION 4 的 MYSQL 线程ID）
wait_time: 12
waiting_query: update t5 set c=c where c=7
waiting_table_lock: `test`.`t5`
waiting_index_lock: PRIMARY
blocking_trx_id: 324676113 --持有锁的事务ID （SESSION 1的事务ID）
blocking_thread: 1 --持有锁的MYSQL线程ID（SESSION 1的MSYQL 线程ID）
blocking_host:
blocking_port: localhost
idle_in_trx: 1224
blocking_query: NULL
2 rows in set (0.00 sec)
我们看到是 SESSION 1阻塞了 SESSION 2和SESSION 4。
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324676115:255:3:8 --SESSION 4 的事务ID
lock_trx_id: 324676115
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 8
lock_data: 7 -SESSION 4 被阻塞在主键键值为7的索引项
*************************** 2. row ***************************
lock_id: 324676113:255:3:8
lock_trx_id: 324676113
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 8
lock_data: 7
*************************** 3. row ***************************
lock_id: 324676114:255:3:2 --SESSION 2的事务ID
lock_trx_id: 324676114
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1 --SESSION 2 被阻塞在主键键值为1的索引项
*************************** 4. row ***************************
lock_id: 324676113:255:3:2
lock_trx_id: 324676113
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: PRIMARY
lock_space: 255
lock_page: 3
lock_rec: 2
lock_data: 1
4 rows in set (0.00 sec)
通过上面的测试证明按照索引顺序进行全表扫描会在主键所有的主键索引项上添加排他行
锁，因为INNODB 的主键索引页子叶其实就是表的数据页，所以也就是在全表所有的记录
上添加了排他行锁。
6. 测试辅助索引是唯一索引的情况下是否会有间隙锁
6.1 查看执行计划
mysql> explain update t5 set b=b where b>1 and b<4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
type: range
possible_keys: b
key: b
key_len: 4
ref: const
rows: 1
Extra: Using where; Using temporary
1 row in set (0.00 sec)
6.2 锁测试
--SESSION 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update t5 set b=b where b>1 and b<4;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2 Changed: 0 Warnings: 0
--SESSION 2
mysql> begin;
mysql> update t5 set b=b where b=1; --没有被阻塞
mysql> update t5 set b=b where b=4; --被阻塞
虽然SESSION 1的SQL语句不需要更新b=4的记录，但还是对b=4的索引项添加了排他行锁。
--SESSION 4
mysql> update t5 set b=b where b=5; --没有被阻塞
--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: 324676117
waiting_thread: 2
wait_time: 137
waiting_query: update t5 set b=b where b=4
waiting_table_lock: `test`.`t5`
waiting_index_lock: b
blocking_trx_id: 324676116
blocking_thread: 1
blocking_host:
blocking_port: localhost
idle_in_trx: 278
blocking_query: NULL
1 row in set (0.00 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324676117:255:4:5
lock_trx_id: 324676117
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 5
lock_data: 4
*************************** 2. row ***************************
lock_id: 324676116:255:4:5
lock_trx_id: 324676116
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 5
lock_data: 4
2 rows in set (0.00 sec)
锁信息中没有间隙锁只有排他行锁。测试说明在 WHERE 字句中使用范围条件过滤时，在辅助索引为唯一索引的情况下不会产生间隙锁，但会锁住范围条件中最大值的索引项（SQL语
句实际上是不需要这条记录的）。
--SESSINO 1
mysql> begin;
mysql> update t5 set b=b where b>6;
Rows matched: 1 Changed: 0 Warnings: 0
--SESSSION 2
mysql> begin;
mysql> insert into t5 values(8,8,8); --被阻塞
--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: 324676121 --等待锁的事务ID（SESSION 2的事务ID）
waiting_thread: 2 --等待锁的MYSQL线程ID（SESSION 2 的 MYSQL 线程ID）
wait_time: 13
waiting_query: insert into t5 values(8,8,8)
waiting_table_lock: `test`.`t5`
waiting_index_lock: b
blocking_trx_id: 324676120 --持有锁的事务ID （SESSION 1的事务ID）
blocking_thread: 1 --持有锁的MYSQL线程ID（SESSION 1的MSYQL 线程ID）
blocking_host:
blocking_port: localhost
idle_in_trx: 51
blocking_query: NULL
1 row in set (0.00 sec)
mysql> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 324676121:255:4:1
lock_trx_id: 324676121
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 1
lock_data: supremum pseudo-record --SESSION 2 被阻塞在表示数据页最后一行的伪记录上
*************************** 2. row ***************************
lock_id: 324676120:255:4:1
lock_trx_id: 324676120
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t5`
lock_index: b
lock_space: 255
lock_page: 4
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
当WHERE 字句中范围查询条件大于表中最后一行时，会在数据页最后一行的伪记录上添加排他行锁，导致无法向表中插入比原来最后一行主键键值大的新记录。
总结：
在辅助索引为唯一索引时，SQL语句执行计划为索引访问扫描或常量检索时事务只会在符合WHERE 字句过滤条件的辅助索引项和符合条件的主键索引项上添加排他行锁，不符合过滤条件的索引项不会添加锁。
按照索引顺序进行全表扫描会在主键所有的主键索引项上添加排他行锁，因为INNODB 的主键索引页子叶其实就是表的数据页，所以也就是在全表所有的记录上添加了排他行锁。
INNODB 在表上没有索引（明确定义的主键也没有，只有INNODB 提供的隐藏主键）的情况下会进行全表扫描，在表中所有的记录上添加排他行锁。在表上有主键索引的情况下，执行计划使用按照索引顺序进行全表扫描会在主键所有的主键索引项上添加排他行锁。
在 WHERE 字句中使用范围条件过滤时，在辅助索引为唯一索引的情况下不会产生间隙锁，但会锁住范围条件中最大值的索引项（SQL语句实际上是不需要这条记录的）。
当WHERE 字句中范围查询条件大于表中最后一行时，会在数据页最后一行的伪记录上添加排他行锁，导致无法向表中插入比原来最后一行主键键值大的新记录。
看完上述内容，你们对如何进行innodb 事务锁的研究有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注创新互联行业资讯频道，感谢大家的支持。
新闻标题：如何进行innodb事务锁的研究
URL地址：http://cqcxhl.cn/article/pcpeec.html