MySQL中InnoDB的间隙锁问题

在为一个客户排除死锁问题时我遇到了一个有趣的包括InnoDB间隙锁的情形。对于一个WHERE子句不匹配任何行的非插入的写操作中，我预期事务应该不会有锁，但我错了。让我们看一下这张表及示例UPDATE。

mysql> SHOW CREATE TABLE preferences \G
*************************** 1. row ***************************
    Table: preferences
Create Table: CREATE TABLE `preferences` (
 `numericId` int(10) unsigned NOT NULL,
 `receiveNotifications` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`numericId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM preferences;
+----------+
| COUNT(*) |
+----------+
|    0 |
+----------+
1 row in set (0.01 sec)
mysql> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0 Changed: 0 Warnings: 0

InnoDB状态显示这个UPDATE在主索引记录上持有了一个X锁：

---TRANSACTION 4A18101, ACTIVE 12 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x7ff2200cd700, query id 35 localhost msandbox
Trx read view will not see trx with id >= 4A18102, sees < 4A18102
TABLE LOCK table `test`.`preferences` trx id 4A18101 lock mode IX
RECORD LOCKS space id 31766 page no 3 n bits 72 index `PRIMARY` of table `test`.`preferences` trx id 4A18101 lock_mode X

这是为什么呢，Heikki在其bug报告中做了解释，这很有意义，我知道修复起来很困难，但略带厌恶地我又希望它能被差异化处理。为完成这篇文章，让我证明下上面说到的死锁情况，下面中mysql1是第一个会话，mysql2是另一个，查询的顺序如下：

mysql1> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql1> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql2> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql2> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql1> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('1', '1'); -- This one goes into LOCK WAIT
mysql2> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('2', '1');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

现在你看到导致死锁是多么的容易，因此一定要避免这种情况——如果来自于事务的INSERT部分导致非插入的写操作可能不匹配任何行的话，不要这样做，使用REPLACE INTO或使用READ-COMMITTED事务隔离。

赞 (0)

相关推荐

  • 如何开启mysql中的严格模式

    很多集成的PHP环境(PHPnow WAMP Appserv等)自带的MySQL貌似都没有开启MySQL的严格模式,何为MySQL的严格模式,简单来说就是MySQL自身对数据进行严格的校验(格式.长度.类型等),比如一个整型字段我们写入一个字符串类型的数据,在非严格模式下MySQL不会报错,同样如果定义了char或varchar类型的字段,当写入或更新的数据超过了定义的长度也不会报错. 我认为这个对于编程来说没有任何好处,虽然我们尽量在代码中做数据校验.MySQL开启了严格模式从一定程序上来讲是

  • MySQL存储引擎中的MyISAM和InnoDB区别详解

    MySQL存储引擎中的MyISAM和InnoDB区别详解

    在使用MySQL的过程中对MyISAM和InnoDB这两个概念存在了些疑问,到底两者引擎有何分别一直是存在我心中的疑问.为了解开这个谜题,搜寻了网络,找到了如下信息: MyISAM是MySQL的默认数据库引擎(5.5版之前),由早期的ISAM(Indexed Sequential Access Method:有索引的顺序访问方法)所改良.虽然性能极佳,但却有一个缺点:不支持事务处理(transaction).不过,在这几年的发展下,MySQL也导入了InnoDB(另一种数据库引擎),以强化参考完

  • MySQL数据库InnoDB引擎主从复制同步经验总结

    近期将公司的MySQL架构升级了,由原先的一主多从换成了DRBD+Heartbeat双主多从,正好手上有一个电子商务网站新项目也要上线了,用的是DRBD+Heartbeat双主一从,由于此过程还是有别于以前的MyISAM引擎的,所以这里也将其心得归纳总结了一下: 1)MySQL的replication过程是一个异步同步的过程,并非完全的主从同步,所以同步的过程中是有延迟的,如果做了读写分离的业务的话,建议也要监控此延迟时间: 2)MySQL的master与slave机器记得server-id要保

  • MySQL提示The InnoDB feature is disabled需要开启InnoDB的解决方法

    MySQL提示The InnoDB feature is disabled需要开启InnoDB的解决方法

    本文实例分析了MySQL提示The InnoDB feature is disabled需要开启InnoDB的解决方法.分享给大家供大家参考,具体如下: 一.问题: 建立数据表报错: ERROR 1289 : The 'InnoDB' feature is disabled; you need MySQL built with 'InnoDB' to have it working 开启DOD命令行,登录(关于MySQL使用DOS命令行登录方法可查看前一篇<MySQL基于DOS命令行登录操作实例

  • Mysql存储引擎InnoDB和Myisam的六大区别

      MyISAM InnoDB   构成上的区别:   每个MyISAM在磁盘上存储成三个文件.第一个文件的名字以表的名字开始,扩展名指出文件类型. .frm文件存储表定义. 数据文件的扩展名为.MYD (MYData). 索引文件的扩展名是.MYI (MYIndex).   基于磁盘的资源是InnoDB表空间数据文件和它的日志文件,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB     事务处理上方面:   MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,

  • mysql事件的开启和调用

    mysql事件的开启和调用

    检测事件是否开启 复制代码 代码如下: show variables like 'event_scheduler';     2.开启事件 复制代码 代码如下: set global event_scheduler = on; 3.创建一个存储过程 复制代码 代码如下: delimiter // create procedure test() begin update project set create_time = now(); end; 4.创建一个事件 复制代码 代码如下: create

  • MySQL的InnoDB引擎入门学习教程

    MySQL的InnoDB引擎入门学习教程

    MySQL发展到今天,InnoDB引擎已经作为绝对的主力,除了像大数据量分析等比较特殊领域需求外,它适用于众多场景.然而,仍有不少开发者还在"执迷不悟"的使用MyISAM引擎,觉得对InnoDB无法把握好,还是MyISAM简单省事,还能支持快速COUNT(*).本文是由于最近几天帮忙处理discuz论坛有感而发,希望能对广大开发者有帮助. 1. 快速认识InnoDB InnoDB是MySQL下使用最广泛的引擎,它是基于MySQL的高可扩展性和高性能存储引擎,从5.5版本开始,它已经成为

  • MySQL中InnoDB的间隙锁问题

    在为一个客户排除死锁问题时我遇到了一个有趣的包括InnoDB间隙锁的情形.对于一个WHERE子句不匹配任何行的非插入的写操作中,我预期事务应该不会有锁,但我错了.让我们看一下这张表及示例UPDATE. mysql> SHOW CREATE TABLE preferences \G *************************** 1. row *************************** Table: preferences Create Table: CREATE TABLE

  • MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

    MySQL中Innodb的事务隔离级别和锁的关系的讲解教程

    前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式.同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力.所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在.这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理中数据库到底做了什么. 一次封锁or两段锁? 因为有大量的并发访问,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会

  • 详解MySql中InnoDB存储引擎中的各种锁

    详解MySql中InnoDB存储引擎中的各种锁

    目录 什么是锁 InnoDB存储引擎中的锁 锁的算法 行锁的3种算法 幻像问题 锁的问题 脏读 不可重复读 丢失更新 死锁 什么是锁 现实生活中的锁是为了保护你的私有物品,在数据库中锁是为了解决资源争抢的问题,锁是数据库系统区别于文件系统的一个关键特性.锁机制用于管理对共享资源的并发访. 数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性 InnoDB存储引擎区别于MyISAM的两个重要特征就是:InnoDB存储引擎支持事务和行级别的锁,MyISAM只支持表级别的锁 In

  • MySQL中的行级锁、表级锁、页级锁

    在计算机科学中,锁是在执行多线程时用于强行限制资源访问的同步机制,即用于在并发控制中保证对互斥要求的满足. 在DBMS中,可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎).表级锁(MYISAM引擎)和页级锁(BDB引擎 ). 一.行级锁 行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁.行级锁能大大减少数据库操作的冲突.其加锁粒度最小,但加锁的开销也最大.行级锁分为共享锁 和 排他锁. 特点 开销大,加锁慢:会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发度也

  • MYSQL数据库Innodb 引擎mvcc锁实现原理

    MYSQL数据库Innodb 引擎mvcc锁实现原理

    目录 1 数据库设置隔离级别 2 数据库表以及案例操作 3 mvcc 实现原理 4 ACID 的实现 前言: 大家都知道在java 开发过程中,会经常用到锁,在java 代码中,我们都知道锁是加在对象头上的,在java对象布局中有锁的标志位.程序通过判断锁的标志位来获取加锁的情况.但是在mysql 中,锁的实现原理是什么呢.可能大家都听过 mvcc,但是mvcc 的实现原理是什么呢,可能就说不太清楚了,本文就以实例说明来mvcc 的实现原理. 1 数据库设置隔离级别 我们都知道数据库的隔离级别可

  • MySQL中InnoDB存储引擎的锁的基本使用教程

    MySQL中InnoDB存储引擎的锁的基本使用教程

    MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-leve locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 各种锁特点 表级锁:开销小,加锁快:不会出现死锁:锁定粒度大,发生冲突的概率最高,并发度最低 行级锁:开销大,加锁慢:会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发度也最高 页面锁:开销和加锁时间介于表锁和行锁之间:会出现死锁:锁定粒度介于表锁和行锁之

  • 大幅提升MySQL中InnoDB的全表扫描速度的方法

    大幅提升MySQL中InnoDB的全表扫描速度的方法

     在 InnoDB中更加快速的全表扫描  一般来讲,大多数应用查询的时候都会用索引,查找很少的几行数据(主键查找或百行内的查询),但有时候我们需要全表查询.典型的全表扫描就是逻辑备份  (mysqldump) 和 online schema changes( 注:在线上对大表 schema 的操作,也是 facebook 的一个开源项目) (SELECT ... INTO OUTFILE). 在 Facebook我们用 mysqldump 来备份数据库. 正如你所知MySql提供两种备份方式,提

  • 详解MySQL中InnoDB的存储文件

    详解MySQL中InnoDB的存储文件

    从物理意义上来讲,InnoDB表由共享表空间文件(ibdata1).独占表空间文件(ibd).表结构文件(.frm).以及日志文件(redo文件等)组成. 1.表结构文件 在MYSQL中建立任何一张数据表,在其数据目录对应的数据库目录下都有对应表的.frm文件,.frm文件是用来保存每个数据表的元数据(meta)信息,包括表结构的定义等,.frm文件跟数据库存储引擎无关,也就是任何存储引擎的数据表都必须有.frm文件,命名方式为数据表名.frm,如user.frm. .frm文件可以用来在数据库

  • mysql 中InnoDB和MyISAM的区别分析小结

    MyIASM是IASM表的新版本,有如下扩展: 二进制层次的可移植性. NULL列索引. 对变长行比ISAM表有更少的碎片. 支持大文件. 更好的索引压缩. 更好的键吗统计分布. 更好和更快的auto_increment处理. 以下是一些细节和具体实现的差别: 1.InnoDB不支持FULLTEXT类型的索引. 2.InnoDB 中不保存表的具体行数,也就是说,执行select count(*) from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保

  • 提高MySQL中InnoDB表BLOB列的存储效率的教程

    提高MySQL中InnoDB表BLOB列的存储效率的教程

    首先,介绍下关于InnoDB引擎存储格式的几个要点: 1.InnoDB可以选择使用共享表空间或者是独立表空间方式,建议使用独立表空间,便于管理.维护.启用 innodb_file_per_table 选项,5.5以后可以在线动态修改生效,并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间,早于5.5的版本,修改完这个选项后,需要重启才能生效: 2.InnoDB的data page默认16KB,5.6版本以后,新增选项 innodb_page_size

随机推荐