练习：Next-key lock案例

# 准备数据
create table t1(

id int,

key idx_id(id)

)engine=innodb;

 

insert t1

values

(1),

(5),

(7),

(11);

 

mysql> explain select * from t1 where id=7 for update;  -- key字段为idx_id，命中索引，即会采用行锁而不是表锁

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra       |

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

|  1 | SIMPLE      | t1    | ref  | idx_id        | idx_id | 5       | const |    1 | Using index |

+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

1 row in set (0.00 sec)

实验：


插入超时失败后，会怎么样？

超时时间的参数：innodb_lock_wait_timeout ，默认是50秒。
超时是否回滚参数：innodb_rollback_on_timeout 默认是OFF。

section A:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> select * from t1 where id=7 for update;
section B:
mysql> begin;

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert t1 values(2);

Query OK, 1 row affected (0.00 sec)

 

mysql> 

mysql> insert t1 values(7);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction  -- 抛出超时异常

mysql> select * from t1;  -- 超时异常并不会被回滚

+------+

| id   |

+------+

|    1 |

|    2 |

|    5 |

|    7 |

|   11 |

+------+

5 rows in set (0.00 sec)

经过测试，不会回滚超时引发的异常，当参数innodb_rollback_on_timeout 设置成ON时，则可以回滚，会把插进去的12回滚掉。

默认情况下，InnoDB存储引擎不会回滚超时引发的异常，死锁问题带来的超时异常除外。

既然InnoDB有三种算法，那Record Lock什么时候用？还是用上面的列子，把辅助索引改成唯一属性的索引。

测试二：

>create table t(a int primary key)engine =innodb;
Query OK, 0 rows affected (0.19 sec)

>insert into t values(1),(3),(5),(8),(11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

>select * from t;
+----+
| a  |
+----+
|  1 |
|  3 |
|  5 |
|  8 |
| 11 |
+----+
5 rows in set (0.00 sec)

section A:
>start transaction;
Query OK, 0 rows affected (0.00 sec)

>select * from t where a = 8 for update;
+---+
| a |
+---+
| 8 |
+---+
1 row in set (0.00 sec)

section B:

>start transaction;
Query OK, 0 rows affected (0.00 sec)

>insert into t values(6);
Query OK, 1 row affected (0.00 sec)

>insert into t values(7);
Query OK, 1 row affected (0.00 sec)

>insert into t values(9);
Query OK, 1 row affected (0.00 sec)

>insert into t values(10);
Query OK, 1 row affected (0.00 sec)

问题：

为什么section B上面的插入语句可以正常，和测试一不一样？

分析：

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法，锁定的不是单个值，而是一个范围，按照这个方法是会和第一次测试结果一样。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

注意：通过主键或则唯一索引来锁定不存在的值，也会产生GAP锁定。即：

会话1：
>show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

>start transaction;

>select * from t where id = 15 for update;
Empty set (0.00 sec)

会话2：
>insert into t(id,name) values(10,'k');
Query OK, 1 row affected (0.01 sec)

>insert into t(id,name) values(12,'k');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
>insert into t(id,name) values(16,'kxx');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
>insert into t(id,name) values(160,'kxx');
^CCtrl-C -- sending "KILL QUERY 9851" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted

如何让测试一不阻塞？可以显式的关闭Gap Lock：

1：把事务隔离级别改成：Read Committed，提交读、不可重复读。SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2：修改参数：innodb_locks_unsafe_for_binlog 设置为1。