通过唯一索引S锁与X锁来了解MySQL死锁套路

在初学者从源码理解MySQL死锁问题中介绍了使用调试 MySQL  源码的方式来查看死锁的过程,这篇文章来讲讲一个常见的案例。
这次我们讲一段唯一索引 S 锁与 X 锁的爱恨情仇

我们来看一个简化过的例子

# 构造数据
CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(10),
 `level` int(11),
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_name` (`name`)
);
INSERT INTO `t1` (`name`, `level`) VALUES ('A',0);

# 出现问题的sql语句如下,并发情况下就会出现死锁
INSERT ignore INTO `t1` (`name`, `level`) VALUES ('A',0);
update t1 set level = 1 where name = "A";

我们用之前介绍过的源码分析方式,先来看下这两条语句分别加什么锁,然后分析死锁形成的过程。

第一条语句

INSERT ignore INTO t1 (name, level) VALUES ('A',0);

在调试中得到的结果如下

可以看到这条语句对唯一键 uk_name 加共享锁(S锁),而且成功。

第二条语句

update t1 set level = 1 where name = "A"; 

通过唯一键更新数据库字段。

这种情况在之前的文章已经介绍过,会对唯一索引加 X 锁,然后对主键索引加 X 锁

这样就可以非常轻松的复现死锁的问题了,步骤如下

1.开启两个 session,分别 begin
2.session1 执行INSERT ignore INTO t1 (name, level) VALUES ('A',0);
3.session2 执行INSERT ignore INTO t1 (name, level) VALUES ('A',0);
4.session1 执行update t1 set level = 1 where name = "A"; 进入等待状态
5.session2 执行update t1 set level = 1 where name = "A";,死锁产生,被回滚,同时事务 1 执行成功

详细的锁状态变化如下

t1 t2 备注
INSERT IGNORE INTO - t1成功获得uk的S锁 DB_SUCCESS
- INSERT IGNORE INTO t2成功获得uk的S锁 DB_SUCCESS
UPDATE - t1尝试获得uk的X锁,但没有成功,处于等待状态 DB_LOCK_WAIT
- UPDATE t2尝试获得uk的X锁,发现死锁产生 DB_DEADLOCK
- Deadlock t2释放S锁
成功 - -

死锁日志如下:

LATEST DETECTED DEADLOCK
------------------------
181208 23:00:52
*** (1) TRANSACTION:
TRANSACTION 53A7, ACTIVE 162 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 12, OS thread handle 0x700010522000, query id 1424 localhost root Updating
update t1 set level = 1 where name = "A"
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A7 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc ;;

*** (2) TRANSACTION:
TRANSACTION 53A8, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 96, OS thread handle 0x70001062e000, query id 1425 localhost root Updating
update t1 set level = 1 where name = "A"
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index `uk_name` of table `lock_demo2`.`t1` trx id 53A8 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 41; asc A;;
 1: len 4; hex 80000001; asc ;;

*** WE ROLL BACK TRANSACTION (2)

来详细看一下这个死锁日志

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A7 lock_mode X locks rec but not gap waiting

事务 1 想获取 uk_name 唯一索引上的 X 锁 (非 gap 锁的记录锁)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock mode S

事务 2 持有uk_name 唯一索引上的 S 锁(共享锁)

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 89 page no 4 n bits 72 index uk_name of table lock_demo2.t1 trx id 53A8 lock_mode X locks rec but not gap waiting

事务 2 想获得 uk_name 唯一索引上的 X 锁(非 gap 锁的记录锁)
跟之前理论上推断的结论是一致的

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。

时间: 2019-05-21

MySQL存储过程例子(包含事务,输出参数,嵌套调用)

drop procedure if exists pro_rep_shadow_rs; delimiter | ---------------------------------- -- rep_shadow_rs -- 用来处理信息的增加,更新和删除 -- 每次只更新上次以来没有做过的数据 -- 根据不同的标志位 -- 需要一个输出的参数, -- 如果返回为0,则调用失败,事务回滚 -- 如果返回为1,调用成功,事务提交 -- -- 测试方法 -- call pro_rep_shadow_rs

由不同的索引更新解决MySQL死锁套路

前几篇文章介绍了用源码的方式来调试锁相关的信息,这里同样用这个工具来解决一个线上实际的死锁案例,也是我们介绍的第一个两条 SQL 就造成死锁的情况.因为线上的表结构比较复杂,做了一些简化以后如下 CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_a` (`a`), KEY `idx_b` (`b`)

mysql 锁表锁行语句分享(MySQL事务处理)

复制代码 代码如下: mysql_query("set autocommit=0"); $list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE"); $db->query("DELETE from prizes WHERE id =".$list_one['id']); mysql_query("co

Mysql中的事务是什么如何使用

什么是事务? 事务是逻辑上的一组操作,组成这组操作的各个单元,要不全都成功要不全都失败,这个特性就是事务 注意:mysql数据支持事务,但是要求必须是innoDB存储引擎 解决这个问题: mysql的事务解决这个问题,因为mysql的事务特性,要求这组操作,要不全都成功,要不全都失败,这样就避免了某个操作成功某个操作失败.利于数据的安全 如何使用: (1)在执行sql语句之前,我们要开启事务 start transaction; (2)正常执行我们的sql语句 (3)当sql语句执行完毕,存在两

MySql的事务使用与示例详解

在MySQL中,事务就是一个逻辑工作单元的一系列步骤.事务是用来保证数据操作的安全性. 事务的特征: 1.Atomicity(原子性) 2.Consistency(稳定性,一致性) 3.Isolation(隔离性) 4.Durability(可靠性) 注:事务只针对对数据数据产生影响的语句有效. show engines //查看mysql锁支持的数据引擎 MyISAM不支持事物,InnoDB支持事物 默认情况下,MySQL将以自动提交模式运行,这意味着没一条小命令都将当做一个只有一条命令的事物

MySql 知识点之事务、索引、锁原理与用法解析

本文实例讲述了MySql 知识点之事务.索引.锁原理与用法.分享给大家供大家参考,具体如下: 事务 事务概念 事务就是一组原子性的SQL查询,或者说一个独立的工作单元.如果数据库引擎执行一组操作语句,那么久执行所有的操作,如果其中有任何一条崩溃或其他原因无法执行,所有语句将不会执行.也就是说事务内的语句,要么全部执行成功,要么全部执行失败. 事务特性ACID 原子性(atomicity) 一个事务被视为最小工作单元,不可拆分,整个事务所有的操作要么全部提交成功,要么全部失败回滚,不可只执行部分.

当Mysql行锁遇到复合主键与多列索引详解

背景 今天在配合其他项目组做系统压测,过程中出现了偶发的死锁问题.分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题. 比如表t_lock_test中有两个主键都为primary key(a,b) ,但是更新时却通过update t_lock_test .. where a = ? ,然后该事务内又有insert into t_lock_test values(...) InnoDB中的锁算法是Ne

MySQL死锁套路之唯一索引下批量插入顺序不一致

前言 死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况.为了方便演示,把批量插入改写为了多条 insert. 先来做几个小实验,简化的表结构如下 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` varchar(5), `b` varchar(5), PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`a`,`b`) ); 实验1: 在记录不存在的情况下,

mysql实现事务的提交和回滚实例

mysql创建存储过程的官方语法为: 复制代码 代码如下: START TRANSACTION | BEGIN [WORK]COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]SET AUTOCOMMIT = {0 | 1} 我这里要说明的mysql事务处理多个SQL语句的回滚情况.比如说在一个存储过程中启动一个事务,这个事务同时往三个表中插入数据,每插完一张表需要

mysql实现事务的提交与回滚的实例详解

最近要对数据库的数据进行一个定时迁移,为了防止在执行过程sql语句因为某些原因报错而导致数据转移混乱,因此要对我们的脚本加以事务进行控制. 首先我们建一张tran_test表 CREATE TABLE tran_test( f1 VARCHAR(10) NOT NULL, f2 INT(1) DEFAULT NULL, PRIMARY KEY (f1) )ENGINE=INNODB CHARSET=utf8 我想对tran_test插入两条数据,但是为了防止插入中报错,因此我要把插入语句控制在一

详解Java的JDBC API中事务的提交和回滚

如果JDBC连接是在自动提交模式下,它在默认情况下,那么每个SQL语句都是在其完成时提交到数据库. 这可能是对简单的应用程序,但有三个原因,你可能想关闭自动提交和管理自己的事务: 为了提高性能 为了保持业务流程的完整性 使用分布式事务 若要控制事务,以及何时更改应用到数据库.它把单个SQL语句或一组SQL语句作为一个逻辑单元,而且如果任何语句失败,整个事务失败. 若要启用,而不是JDBC驱动程序默认使用auto-commit模式手动事务支持,使用Connection对象的的setAutoComm

MySQL数据库误操作后快速回滚的方法

基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. binlog2sql快速回滚 首先,确认你的MySQL server开启了binlog,设置了以下参数: [mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin.log max_binlog_siz

spring事务异常回滚实例解析

最近遇到了事务不回滚的情况,我还考虑说JPA的事务有bug?我想多了....... 为了打印清楚日志,很多方法我都加tyrcatch,在catch中打印日志.但是这边情况来了,当这个方法异常时候日志是打印了,但是加的事务却没有回滚. 例: 类似这样的方法不会回滚(一个方法出错,另一个方法不会回滚): if(userSave){ try { userDao.save(user); userCapabilityQuotaDao.save(capabilityQuota); } catch (Exce

java事务回滚失败问题分析

Spring-Java事物回滚失效处理最近在做项目中,无意间发现有个类在抛事物回滚操作,数据也正常的插入到数据库当中了,于是仔细查看看一下具体原因. 一切还是要从Java的检查型异常和非检查型异常说起. 那么什么是检查型异常什么又是非检查型异常呢? 最简单的判断点有两个: 1.继承自RuntimeException或Error的是非检查型异常,而继承自Exception的则是检查型异常(当然,RuntimeException本身也是Exception的子类). 2.对非检查型类异常可以不用捕获,

解决Mysql收缩事务日志和日志文件过大无法收缩问题

一.MS SQL SERVER 2005 --1.清空日志       exec('DUMP TRANSACTION 数据库名 WITH NO_LOG') --2.截断事务日志:      exec('BACKUP LOG 数据库名 WITH NO_LOG') --3.收缩数据库文件(如果不压缩,数据库的文件不会减小      exec('DBCC SHRINKDATABASE(数据库名) ') --4.设置自动收缩      exec('EXEC sp_dboption 数据库名,autosh

Java Spring 事务回滚详解

spring 事务回滚 1.遇到的问题 当我们一个方法里面有多个数据库保存操作的时候,中间的数据库操作发生的错误.伪代码如下: public method() { Dao1.save(Person1); Dao1.save(Person2); Dao1.save(Person2);//假如这句发生了错误,前面的两个对象会被保存到数据库中 Dao1.save(Person2); } 期待的情况:发生错误之前的所有数据库保存操作都回滚,即不保存 正常情况:前面的数据库操作会被执行,而发生数据库操作错

完美解决Spring声明式事务不回滚的问题

疑问,确实像往常一样在service上添加了注解 @Transactional,为什么查询数据库时还是发现有数据不一致的情况,想想肯定是事务没起作用,出现异常的时候数据没有回滚.于是就对相关代码进行了一番测试,结果发现一下踩进了两个坑,确实是事务未回滚导致的数据不一致. 下面总结一下经验教训: Spring事务的管理操作方法 编程式的事务管理 实际应用中很少使用 通过使用TransactionTemplate 手动管理事务 声明式的事务管理 开发中推荐使用(代码侵入最少) Spring的声明式事

深入了解mysql长事务

前言: 本篇文章主要介绍MySQL长事务相关内容,比如说我们开启的一个事务,一直没提交或回滚会怎样呢,出现事务等待情况应该如何处理,本篇文章将给你答案. 注意:本篇文章并不聚焦于谈论事务隔离级别以及相关特性.而是介绍长事务相关危害以及监控处理方法.本文是基于MySQL5.7.23版本,不可重复读(RR)隔离级别所做实验.(语句为\G可以使查询结构显示更易读,但只可以在mysql命令行使用.) 1.什么是长事务 首先我们先要知道什么是长事务,顾名思义就是运行时间比较长,长时间未提交的事务,也可以称