先了解一下什么是GAP LOCK
在INNODB中,record-level lock大致有三种:Record, Gap, and Next-KeyLocks。简单的说,RECORDLOCK就是锁住某一行记录;而GAPLOCK会锁住某一段范围中的记录;NEXT-KEYLOCK则是前两者加起来的效果。
下面是MYSQL官方文档中相关内容的链接
http://dev.mysql.com/doc/refman/5.1/en/innodb-record-level-locks.html
有资料里说MYSQL的GAP LOCK最初是为了避免Phantom (幻象读)的问题,关于幻象读这里就不多做解释了,可以参考如下链接
http://dev.mysql.com/doc/refman/5.1/en/innodb-next-key-locking.html
可是毕竟GAPLOCK导致了锁定范围的增大,在某些情况下可能会造成一些不符合预期的现象。下面是一个简单的测试例子,先对GAP LOCK有个感性的认识
mysql> desc ts_column_log_test
-> ;
+------------+-------------+------+-----+---------------------+----------------+
| Field |Type | Null | Key |Default | Extra |
+------------+-------------+------+-----+---------------------+----------------+
|id |int(11) | NO | PRI |NULL | auto_increment |
| col_id |int(11) | NO | MUL |NULL | |
| start_time | timestamp |NO | | 0000-00-00 00:00:00| |
| end_time |timestamp | NO | | 0000-00-0000:00:00| |
| data_time | timestamp |NO | | 0000-00-00 00:00:00| |
| status |varchar(30) | NO | |NULL | |
+------------+-------------+------+-----+---------------------+----------------+
6 rows in set (0.01 sec)
mysql> select * from ts_column_log_test;
+----+--------+---------------------+---------------------+---------------------+---------+
| id | col_id |start_time |end_time |data_time |status |
+----+--------+---------------------+---------------------+---------------------+---------+
| 1 | 2| 2011-12-13 11:51:11 | 2011-12-13 11:51:11 | 2011-12-09 00:00:00 | running |
| 2 | 20 |2011-12-13 11:51:16 | 2011-12-13 11:51:16 | 2011-12-09 00:00:00 | running |
| 3 | 120 |2011-12-13 11:51:20 | 2011-12-13 11:51:20 | 2011-12-09 00:00:00 | running |
+----+--------+---------------------+---------------------+---------------------+---------+
3 rows in set (0.00 sec)
开启两个不同的会话,分别执行一些语句观察一下结果:
session1
mysql> set autocommit=0;
mysql> delete from ts_column_log_testwhere col_id=10;
Query OK, 0 rows affected (0.00sec) --此时[2,20)这个区间内的记录都已经被GAP LOCK锁住了,如果在其他事务中尝试插入这些值,则会等待
session2
mysql> set autocommit=0;
mysql> INSERT INTO ts_column_log_test(col_id, start_time, end_time, data_time, status) VALUES (1, NULL, NULL,'20111209', 'running'); --成功
...
mysql> INSERT INTO ts_column_log_test(col_id, start_time, end_time, data_time, status) VALUES (2, NULL, NULL,'20111209', 'running'); --等待
...
mysql> INSERT INTO ts_column_log_test(col_id, start_time, end_time, data_time, status) VALUES (19, NULL, NULL,'20111209', 'running'); --等待
...
上面的实验很简单,大家可以自己测一下。这里解释一下会产生这种现象的原因:session1中的delete语句中指定条件where col_id=10,这时MYSQL会去扫描索引,但是这个时候delete语句获取的不是一个RECORD LOCK,而是一个NEXT-KEY LOCK。以当前值(10)为例,会向左扫描至col_id=2这条记录,向右扫描至col_id=20这条记录,锁定区间为前闭后开,即[2,20)。
下面是摘自官方手册里的一句话:
DELETE FROM ... WHERE ... sets an exclusivenext-key lock on every record the search encounters.
下面的链接里面有INNODB中各种不同的语句可能持有哪些锁的解释
http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html
明白了GAPLOCK是怎么回事,下面看下可能产生的问题吧
有时候我们会多个进程或线程并行的对同一张表进行操作,并且使用了事务,那么就可能会有问题,举个例子:
session1:
delete from ts_column_log_test wherecol_id=10;
INSERT INTO ts_column_log_test (col_id,start_time, end_time, data_time, status) VALUES (10, NULL, NULL, '20111209','running');
session2:
delete from ts_column_log_test wherecol_id=11;
INSERT INTO ts_column_log_test (col_id,start_time, end_time, data_time, status) VALUES (11, NULL, NULL, '20111209','running');
假设上面是你程序的两个进程需要做的操作,在没有并发的情况下,可能运行正常,因为每个事务在MYSQL中最终都是串行执行,中间并没有其他事务同时进行;可并发高了以后,可能在MYSQL中实际运行的语句顺序就会变成下面这个样子:
tx_num time statement
111 2011-12-12 10:00:00 delete from ts_column_log_test wherecol_id=10;
222 2011-12-1210:00:00 delete from ts_column_log_test where col_id=11;
111 2011-12-12 10:00:00 INSERT INTO ts_column_log_test (col_id,start_time, end_time, data_time, status) VALUES (10, NULL, NULL, '20111209','running');
222 2011-12-1210:00:00 INSERT INTO ts_column_log_test (col_id, start_time, end_time,data_time, status) VALUES (11, NULL, NULL, '20111209', 'running');
这个时候,你可能就会得到错误提示ERROR 1213 (40001): Deadlock found when trying toget lock; try restarting transaction。
原因是前两条语句都已经获得了[2,20)这个区间内记录的S锁,然后两个事务又分别对该区间段内的col_id=10这个位置请求X锁,这时就发生死锁,谁都请求不到X锁,因为互相都持有S锁。
解决方案有两种
1、改变程序中数据库操作的逻辑
2、取消GAP LOCK机制
Gap locking can be disabled explicitly.This occurs if you change the transaction isolation level to READ COMMITTED orenable the innodb_locks_unsafe_for_binlog system variable.
相关推荐
innodb GAP LOCK 死锁例子1
数据库也会发生死锁的现象,数据库系统实现了各种死锁检测和死锁超时机制来解除死锁,锁监视器进行死锁检测,MySQL的InnoDB处理死锁的方式是 将持有最少行级排它锁的事务进行回滚,相对比较简单的死锁回滚办法
MySQL Innodb 索引原理详解
该文档详细分析了innodb的加锁原理、死锁原因以及处理方式
2015 Oracle 技术嘉年华(OTN)分会场11何登成 - 管中窥豹——MySQL(InnoDB)死锁分析之道
主要介绍了MySQL(InnoDB)是如何处理死锁的,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
主要介绍了MySQL启动报错问题InnoDB:Unable to lock/ibdata1 error,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
Mysql Innodb死锁情况分析与归纳.docx
mysql innodb类型数据库表 根据ibd文件获取表 space id,用于恢复innodb类型数据表数据
MySQL Innodb锁解决并发问题
最近在学习MySQL技术内幕 InnoDB存储引擎 第2版,整理了一些文档分享出来,同时也方便以后查看。若有不当之处,烦请批评指正。 1. MySQL体系结构和存储引擎 2. InnoDB存储引擎 2.1 InnoDB体系结构 2.2 ...
解决MySQL中的死锁问题通常涉及多个方面,包括监控死锁、分析死锁原因、优化事务设计以及调整系统配置等。以下是一些解决MySQL中死锁问题的建议和方法: 监控死锁: 使用SHOW ENGINE INNODB STATUS命令来查看InnoDB...
Mysql 高可用 InnoDB Cluster 多节点搭建过程
这是我从网上找到的mysql/mariadb对innodb表进行数据恢复的工具,实现从innodb的数据库文件中恢复数据,用于实现下面情况:1、直接下载了innodb数据库的文件,而不是导出其数据,想恢复数据时(需要有完整的文件,...
MySQL InnoDB 查询优化实现分析,欢迎下载,1111111111111
Innodb 通过多版本并发控制(MVCC)来获得高并发...对于表中的数据innodb 采用聚集的方式,每张表的存储都是按主键的顺序存放,如果没有显式在表定义时指定主键,innodb 会为每一行生成一个6字节的rowid,并以此为主键。
MySQL体系结构及原理(innodb)图文完美解析
基于CentOS7 搭建基于InnoDB引擎的Mysql 8 集群环境
MySQL 和 InnoDB 性能