mysql-insert锁机制

注:不同的参数设置,不同的mysql版本,不同的事务隔离级别都对加锁过程有影响

1.通过show variables like 'innodb_locks_unsafe_for_binlog'可以查看gap锁是否开启


该参数默认值为0,即启用gap锁,但是这个参数的设置不会影响外键和唯一索引(包含主键)对gap进行加锁的需要

2.通过show variables like 'innodb_autoinc_lock_mode'查看自增锁的模式


该参数默认有3种配置模式:

0(传统模式):对auto_increment列插入语句时加表级auto-inc锁,只有插入执行结束之后才会释放锁

1(连续模式):可以事先确定插入行数的语句(包括多行和单行),会分配连续确定的auto-increment值;对于插入行数不确定的语句,仍然加表级auto-inc锁。(注:在这种模式下,如果回滚,auto-increment并不会回滚,则导致自增列不连续)

2(交错模式):同一时刻多条SQL语句产生交错的auto-increment值


行锁:

record lock:,仅仅锁住索引记录的一行,单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身

gap lock:仅仅锁住一个索引区间(开区间),在索引记录之间的间隙中加锁,或在某一条索引记录之前或之后加锁,并不包括索引记录本身

next-key锁:record lock+gap lock 左开右闭区间


插入意向锁(insert intention lock,属于gap lock):在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候,不需要等待其他事务完成。

加锁过程:insert会在插入成功的行加一个X锁,这个锁是record lock而不是一个next-key lock(更不会是gap lock),不会阻止其他并发事务往这条记录之前的间隙插入记录。在插入之前,会先在插入记录所在的间隙加上一个insert intention gap lock,并发事务可以对同一个加insert意向锁。如果insert的事务出现了主键重复错误,事务会对这个记录加共享锁。这个共享锁在并发的时候是会产生死锁。


行锁之间的兼容情况(横向为已加的锁)

说明:

insert操作之间不会有冲突

gap、next-key会阻止insert

gap和record、next-key不会冲突

record和record、next-key之间相互冲突

已有的insert锁不阻止任何准备加的锁


insert死锁场景分析

1.主键错误引发的死锁

表结构:

CREATE TABLE `aa` (

 `id` int(10) unsigned NOT NULL COMMENT '主键',

 `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',

 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',

 `stage` int(11) NOT NULL DEFAULT '0' COMMENT '关卡数',

 PRIMARY KEY (`id`),

 UNIQUE KEY `udx_name` (`name`),

 KEY `idx_stage` (`stage`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据:

mysql> select * from aa;

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

| id | name | age | stage |

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

| 1 | yst | 11 |   8 |

| 2 | dxj |  7 |   4 |

| 3 | lb  | 13 |   7 |

| 4 | zsq |  5 |   7 |

| 5 | lxr | 13 |   4 |

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


注:如果T1未rollback,而是commit的话,T2和T3会报唯一键冲突:ERROR 1062 (23000): Duplicate entry ‘6’ for key ‘PRIMARY’

死锁原因:首先session1插入一条记录,获得该记录的排它锁,这时session2和session3都检测到了主键冲突错误,但是由于session1并没有提交,所以session1并不算插入成功,于是它并不能直接报错吧,于是session2和session3都申请了该记录的共享锁,这时还没获取到共享锁,处于等待队列中。这时session1 rollback了,也就释放了该行记录的排它锁,那么session2和session3都获取了该行上的共享锁。而session2和session3想要插入记录,必须获取排它锁,但由于他们自己都拥有了共享锁,于是永远无法获取到排它锁,于是死锁就发生了。如果这时session1是commit而不是rollback的话,那么session2和session3都直接报错主键冲突错误。

2.gap和insert intention冲突引发的死锁

表结构:

CREATE TABLE `t` (

 `a` int(11) NOT NULL,

 `b` int(11) DEFAULT NULL,

 PRIMARY KEY (`a`),

 KEY `idx_b` (`b`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表数据:

mysql> select * from t;

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

| a | b  |

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

| 1 |  2 |

| 2 |  3 |

| 3 |  4 |

| 11 |  22 |

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


死锁原因:

事务T1执行查询语句,在索引b=6上加排他Next-key锁(LOCK_X | LOCK_ORDINARY),会锁住idx_b索引范围(4, 22)。

事务T2执行查询语句,在索引b=8上加排他Next-key锁(LOCK_X | LOCK_ORDINARY),会锁住idx_b索引范围(4, 22)。由于请求的GAP与已持有的GAP是兼容的,因此,事务T2在idx_b索引范围(4, 22)也能加锁成功。

事务T1执行插入语句,会先加排他Insert Intention锁。由于请求的Insert Intention锁与已有的GAP锁不兼容,则事务T1等待T2释放GAP锁。

事务T2执行插入语句,也会等待T1释放GAP锁。

于是,死锁便产生了。


译者介绍:家华,从事mysqlDBA的工作,记录自己对mysql的一些总结

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值