(10)MySQL进阶篇SQL优化(InnoDB锁间隙锁)

database

1.概述

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

2.InnoDB存储引擎的间隙锁阻塞例子

先创建一个间隙临时表,ID为主键自增:

MySQL [(none)]> CREATE TABLE goods. tab_gap (ID INT NOT NULL auto_increment,Name VARCHAR(50),PRIMARY KEY(ID));

Query OK, 0 rows affected (0.02 sec)

先插入五行数据:

MySQL [(none)]> INSERT INTO goods.tab_gap (ID,`Name`) VALUES (1,"a"),(2,"b"),(3,"c"),(4,"d"),(5,"e");

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0

示例:

session_1

session_2

1)查询事务隔离级别是否是可重复读

1查询事务隔离级别是否是可重复读

MySQL [(none)]> SHOW VARIABLES LIKE "transaction_isolation%";

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

| Variable_name | Value |

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

| transaction_isolation | REPEATABLE-READ |

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

1 row inset (0.01 sec)

MySQL [(none)]> SHOW VARIABLES LIKE "transaction_isolation%";

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

| Variable_name | Value |

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

| transaction_isolation | REPEATABLE-READ |

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

1 row inset (0.01 sec)

2先设置事务T1提交类型为事务非自动提交。

2先设置事务T2提交类型为事务非自动提交。

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)]> SET AUTOCOMMIT=0;

Query OK, 0 rows affected (0.00 sec)

3为当前session_1事务中Name=f’不存在的记录行加排他锁。

 

 

MySQL [(none)]> SELECT * FROM goods.tab_gap WHERE `Name`="f" FOR UPDATE;

Empty set (0.00 sec)

 

 

3如果这时session_2插入ID=6的记录行(注意:这条记录并不存在),也会出现锁等待。

MySQL [(none)]> INSERT INTO goods.tab_gap (ID,`Name`) VALUES (6,"f");

阻塞...

4回滚事务。

 

 

MySQL [(none)]> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)

 

 

4由于session_1回滚后释放了间隙锁, 所以当前session_2可以获得锁并成功插入记录。

MySQL [(none)]> INSERT INTO goods.tab_gap (ID,`Name`) VALUES (6,"f");

Query OK, 1 row affected (38.41 sec)

 

 

5提交事务,释放锁。

MySQL [(none)]> COMMIT;

Query OK, 0 rows affected (0.00 sec)

4为当前session_1事务中ID>4范围的记录行加排他锁。

 

 

MySQL [(none)]> SELECT * FROM goods.tab_gap WHERE ID>4 FOR UPDATE;

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

| ID | Name |

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

| 5 | e |

| 6 | f |

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

2 rows inset (0.00 sec)

 

 

6为当前session_2事务中ID=5的记录行加排他锁,发生阻塞。

MySQL [(none)]> SELECT * FROM goods.tab_gap WHERE ID>5 FOR UPDATE;

阻塞...

从示例中可以看到,在Mysql默认事务隔离级别下,如果在相等的条件中给一个不存在的记录行加锁,InnoDB也会使用间隙锁,不然session_1会出现幻读(session_1事务中能查询到session_2插入ID=6的记录行)。而当我们在ID>4范围条件内加锁,InnoDB不仅会对符合条件的ID值为5、6的记录行加锁,也会为大于6以上的记录行加间隙锁(不管数据是否存在)。

3.总结

InnoDB使用间隙锁的目的,一方面是为了防止幻读,以满足相关隔离级别的要求。另外一方面,是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响,以及不同隔离级别下InnoDB使用间隙锁的情况,在后续的章节中会做进一步介绍。很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待!因此,在实际应用开发中,尤其是并发插入比较多的应用程序,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。还要特别说明的是,InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁!

参考文献:
深入浅出MySQL大全

以上是 (10)MySQL进阶篇SQL优化(InnoDB锁间隙锁) 的全部内容, 来源链接: utcz.com/z/535609.html

回到顶部