1. select * for update 语句添加的是排他行锁。

2. select ... from table_name where ... forupdate 语句在行计划使用索引常量查找或索引范围扫描时（索引覆盖查询的情况下）会在主键上添加排他行锁。

3. select .. for update 语句使用全索引扫描时，在使用覆盖索引的情况下也会对主键的所有记录添加排他行锁。

4. update 语句执行计划使用索引常量查找或索引范围扫描时，除了在辅助索引添加排他行锁也会在主键对应的记录上添加排他行锁（即便使用了覆盖索引也是如此）。

5. update 语句执行计划使用辅助索引全扫描时，除了在辅助索引的所有记录添加排他行锁也会在主键的所有记录上添加排他行锁（即便使用了覆盖索引也是如此）。

6. 测试辅助索引是唯一索引的情况下是否会有间隙锁

准备测试数据：

CREATE TABLE t5 (

aint(11) NOT NULL,

b intnot null,

cint 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 sessioninnodb_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 sessioninnodb_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 forupdate;

+---+

| 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 forupdate; --被阻塞

--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=5for update 添加到是排他行锁。

2. select ... from table_name where ... forupdate 语句在行计划使用索引常量查找或索引范围扫描时（索引覆盖查询的情况下）会在主键上添加排他行锁。

mysql> explain select b from t5 whereb=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 forupdate;

+---+

| b |

+---+

| 5 |

+---+

1 row in set (0.00 sec)

--SESSION 2

mysql> select c from t5 where c=5 forupdate; --被阻塞

--SESSION 3查看锁信息

SELECT r.trx_id ASwaiting_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 forupdate;

+---+

| 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 forupdate; --被阻塞

--SESSION 3 查看锁信息

SELECT r.trx_id ASwaiting_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 JOINinformation_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 forupdate\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 forupdate\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 ASwaiting_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 JOINinformation_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 ASwaiting_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 JOINinformation_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 ASwaiting_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 JOINinformation_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 forupdate; --被阻塞

--SESSION 3 查看锁信息

SELECT r.trx_id ASwaiting_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 JOINinformation_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 whereb=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 whereb=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 wherec=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 bin (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 bin (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 ASwaiting_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 JOINinformation_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 ASwaiting_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 JOINinformation_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 bin (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 cin (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 ASwaiting_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 JOINinformation_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 whereb>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>1and 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 ASwaiting_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 ASwaiting_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 JOINinformation_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 字句中范围查询条件大于表中最后一行时，会在数据页最后一行的伪记录上添加排他行锁，导致无法向表中插入比原来最后一行主键键值大的新记录。