如何進行innodb 事務鎖的研究

今天就跟大家聊聊有關如何進行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_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 事務鎖的研究有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。