如何进行innodb 事务锁的研究
发布时间：2021-12-19 18:21:42 所属栏目：MySql教程 来源：互联网
导读：今天就跟大家聊聊有关如何进行innodb 事务锁的研究，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。 1. select * for update 语句添加的是排他行锁。 2. select ... from table_name where ... fo
|
今天就跟大家聊聊有关如何进行innodb 事务锁的研究，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。 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_locksG *************************** 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 updateG *************************** 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 DESCG *************************** 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_locksG *************************** 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 DESCG *************************** 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_locksG *************************** 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 updateG *************************** 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 updateG *************************** 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 DESCG *************************** 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_locksG *************************** 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 DESCG *************************** 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_locksG *************************** 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 DESCG *************************** 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_locksG *************************** 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 DESCG *************************** 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_locksG *************************** 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=bG *************************** 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 t5G *************************** 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=cG *************************** 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=5G *************************** 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 updateG *************************** 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=5G *************************** 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 DESCG *************************** 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_locksG *************************** 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 DESCG *************************** 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_locksG *************************** 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 DESCG *************************** 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_locksG *************************** 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<4G *************************** 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 DESCG *************************** 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_locksG *************************** 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 DESCG *************************** 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_locksG *************************** 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 事务锁的研究有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。 （编辑：站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
