MySQL 死锁套路:唯一索引 S 锁与 X 锁的爱恨情仇

在初学者从源码理解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死锁问题

通过好多个深夜艰难的单步调试,终于找到了一个理想的断点,可以看到大部分获取锁的过程 代码在lock0lock.c的static enum db_err lock_rec_lock() 函数中,这个函数会显示,获取锁的过程,以及获取锁成功与否. 场景1:通过主键进行删除 表结构 CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (

一个mysql死锁场景实例分析

前言 最近遇到一个mysql在RR级别下的死锁问题,感觉有点意思,研究了一下,做个记录. 涉及知识点:共享锁.排他锁.意向锁.间隙锁.插入意向锁.锁等待队列 场景 隔离级别:Repeatable-Read 表结构如下 create table t ( id int not null primary key AUTO_INCREMENT, a int not null default 0, b varchar(10) not null default '', c varchar(10) not n

详解MySQL(InnoDB)是如何处理死锁的

一.什么是死锁 官方定义如下:两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁. 这个就好比你有一个人质,对方有一个人质,你们俩去谈判说换人.你让对面放人,对面让你放人. 二.为什么会形成死锁 看到这里,也许你会有这样的疑问,事务和谈判不一样,为什么事务不能使用完锁之后立马释放呢?居然还要操作完了之后一直持有锁?这就涉及到 MySQL 的并发控制了. MySQL的并发控制有两种方式,一个是 MVCC,一个是两阶段锁协议.那么为什么要并发控制呢?是因为多个用户同时操作 M

一次神奇的MySQL死锁排查记录

背景 说起Mysql死锁,之前写过一次有关Mysql加锁的基本介绍,对于一些基本的Mysql锁或者死锁都有一个简单的认识,可以看下这篇文章为什么开发人员需要了解数据库锁.有了上面的经验之后,本以为对于死锁都能手到擒来,没想到再一个阳光明媚的下午报出了一个死锁,但是这一次却没想象的那么简单. 问题初现 在某天下午,突然系统报警,抛出个异常: 仔细一看好像是事务回滚异常,写着的是因为死锁回滚,原来是个死锁问题,由于我对Mysql锁还是有一定了解的,于是开始主动排查这个问题. 首先在数据库中查找Inn

一次Mysql死锁排查过程的全纪录

前言 之前接触到的数据库死锁,都是批量更新时加锁顺序不一致而导致的死锁,但是上周却遇到了一个很难理解的死锁.借着这个机会又重新学习了一下mysql的死锁知识以及常见的死锁场景.在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多.虽然是后端程序员,我们不需要像DBA一样深入地去分析与锁相关的源码,但是如果我们能够掌握基本的死锁排查方法,对我们的日常开发还是大有裨益的. PS:本文不会介绍死锁的基本知识,mysql的加锁原理可以参考本文的参考资料提供的链接. 死锁起因 先介绍一下数

MySQL性能瓶颈排查定位实例详解

本文实例讲述了MySQL性能瓶颈排查定位的方法.分享给大家供大家参考,具体如下: 导读 从一个现场说起,全程解析如何定位性能瓶颈. 排查过程 收到线上某业务后端的MySQL实例负载比较高的告警信息,于是登入服务器检查确认. 1. 首先我们进行OS层面的检查确认 登入服务器后,我们的目的是首先要确认当前到底是哪些进程引起的负载高,以及这些进程卡在什么地方,瓶颈是什么. 通常来说,服务器上最容易成为瓶颈的是磁盘I/O子系统,因为它的读写速度通常是最慢的.即便是现在的PCIe SSD,其随机I/O读写

MySQL死锁问题分析及解决方法实例详解

MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;

记一次tomcat进程cpu占用过高的问题排查记录

本文主要记录一次tomcat进程,因TCP连接过多导致CPU占用过高的问题排查记录. 问题描述 linux系统下,一个tomcat web服务的cpu占用率非常高,top显示结果超过200%.请求无法响应.反复重启依然同一个现象. 问题排查 1.获取进程信息 通过jdk提供的jps命令可以快速查出jvm进程, jps pid 2.查看jstack信息 jstack pid 发现存在大量log4j线程block,处于waiting lock状态 org.apache.log4j.Category.

通过唯一索引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` (`na

MySQL死锁的产生原因以及解决方案

数据库和操作系统一样,是一个多用户使用的共享资源.当多个用户并发地存取数据 时,在数据库中就会产生多个事务同时存取同一数据的情况.若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性.加锁是实现数据库并 发控制的一个非常重要的技术.在实际应用中经常会遇到的与锁相关的异常情况,当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁,严 重影响应用的正常执行. 在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁)和共享锁(Share Lock

MySQL死锁检查处理的正常方法

正常情况下,死锁发生时,权重最小的连接将被kill并回滚.但是为了找出语句来优化,启用可启用死锁将死锁信息记录下来. #step 1:窗口一 mysql> start transaction; mysql> update aa set name='aaa' where id = 1; #step 2:窗口二 mysql> start transaction; mysql> update bb set name='bbb' where id = 1; #step 3:窗口一 mysq

防止mysql重复插入记录的方法

防止mysql重复插入记录的方法有很多种,常用的是ignore,Replace,ON DUPLICATE KEY UPDATE,当然我们也可以在php中加以判断了. 方案一:使用ignore关键字 如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用: 代码如下: 复制代码 代码如下: INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) VALUES ('test9@163.com'