精华内容
下载资源
问答
  • 2021-01-19 02:20:17

    MySQL复制slave服务器死锁案例

    原文:MySQL复制slave服务器死锁案例

    MySQL复制刚刚触发了一个bug,该bug的触发条件是slave上Xtrabackup备份的时候执行flushs tables with read lock和show slave status有可能和SQL Thread形成死锁。

    该bug在MySQ...

    文章

    杰克.陈

    2018-10-21

    825浏览量

    MySQL死锁系列-线上死锁问题排查思路

    前言

    MySQL 死锁异常是我们经常会遇到的线上异常类别,一旦线上业务日间复杂,各种业务操作之间往往会产生锁冲突,有些会导致死锁异常。这种死锁异常一般要在特定时间特定数据和特定业务操作才会复现,并且分析解决时还需要了解 MySQL 锁冲突相关知识,所以一般遇到这些偶尔出现的死锁异常,往往一时没有头...

    文章

    程序员历小冰

    2020-10-19

    975浏览量

    MySQL · 引擎新特性 · 可开关的InnoDB死锁检测

    在数据库系统中,死锁问题几乎是不可避免的,一般要么是资源互相占用导致,或者是系统内部的锁升级(在innodb内尤其普遍),尤其是糟糕的未经审查的SQL设计通常是导致死锁的元凶。在MySQL InnoDB引擎中,死锁的检测是通过深度遍历进行的,每一个需要等待的行锁请求都需要去检测是否可能产生死锁。

    ...

    文章

    zhaiwx_yinfeng

    2016-09-25

    9184浏览量

    超赞,InnoDB调试死锁的方法!

    近期写了不少InnoDB锁相关的文章,不少小伙伴问,如何在MySQL终端模拟并发事务,如何复现之前文章中的案例。今天,咱们一起动起手来,模拟并发事务的互斥与死锁。

    【事前准备】

    安装MySQL服务端

    安装MySQL客户端

    安装能够模拟多个并发事务的终端

    画外音:楼主使用的是MySQL5.6,官...

    文章

    初商

    2019-08-09

    175浏览量

    一个最不可思议的MySQL死锁分析

    1 死锁问题背景 1

    1.1 一个不可思议的死锁 1

    1.1.1 初步分析 3

    1.2 如何阅读死锁日志 3

    2 死锁原因深入剖析 4

    2.1 Delete操作的加锁逻辑 4

    2.2 死锁预防策略 5

    2.3 剖析死锁的...

    文章

    developerguy

    2016-06-19

    2916浏览量

    MySQL死锁的两个小案例

    最近花了些时间分析MySQL锁的内容,觉得越看越有意思。

    我有个学习的习惯,有时候也不知道好还是不好,那就是喜欢直接上手练习,然后反过来练习理论。结果在学习锁的时候,感觉多多少少走了一些弯路,那就是对锁的基础的概念有一些混淆,虽然能够模拟出一些场景来,但是总是有一种隔靴搔痒的感觉,于...

    文章

    jeanron100

    2017-09-08

    880浏览量

    【MySQL】如何阅读死锁日志

    一 前言

    工欲善其事必先利其器,前面分析了很多死锁案例,并没有详细的介绍如何通过死锁日志来诊断死锁的成因。本文将介绍如何读懂死锁日志,尽可能的获取信息来辅助我们解决死锁问题。二 日志分析

    2.1 场景

    为了更好的学习死锁日志,我们需要提前了解死锁场景MySQL 5.6 事务隔离级别为RR

    ...

    文章

    yangyi_dba

    2017-09-18

    2994浏览量

    巧用MySQL InnoDB引擎锁机制解决死锁问题(转)

    该文会通过一个实际例子中的死锁问题的解决过程,进一步解释innodb的行锁机制

    最近,在项目开发过程中,碰到了数据库死锁问题,在解决问题的过程中,笔者对MySQL InnoDB引擎锁机制的理解逐步加深。

    案例如下:

    在使用Show innodb status检查引擎状态时,发现了死锁问题:

    *...

    文章

    developerguy

    2016-06-19

    881浏览量

    pt(Percona Toolkit)工具详解:(二)工具介绍

    之所以先写介绍,那是因为太多工具,要查起来非常麻烦,要先做个汇总介绍,方便查找.介绍

    1.找出重复的索引和外键

    pt-duplicate-key-checker

    这个工具会将重复的索引和外键都列出来,并生成了删除重复索引的语句,非常方便

    2.执行alter的时候不会阻塞写和读取操作

    ...

    文章

    技术小胖子

    2017-11-08

    1353浏览量

    pt(Percona Toolkit)工具详解:(二)工具介绍

    之所以先写介绍,那是因为太多工具,要查起来非常麻烦,要先做个汇总介绍,方便查找.介绍1.找出重复的索引和外键    pt-duplicate-key-checker这个工具会将重复的索引和外键都列出来,并生成了删除重复索引的语句,非常方便2.执行alter的时候不会阻塞写和读取操作    pt-o...

    文章

    技术小胖子

    2017-11-15

    1548浏览量

    一次诡异的数据库死锁问题排查过程

    GitHub 1.8k Star 的Java工程师成神之路 ,不来了解一下吗?

    GitHub 1.8k Star 的Java工程师成神之路 ,真的不来了解一下吗?

    GitHub 1.8k Star 的Java工程师成神之路 ,真的确定不来了解一下吗?

    现象

    某天晚上,同事正在发布,突然线上大量报警...

    文章

    Hollis

    2019-04-10

    1394浏览量

    Innodb 死锁案例解析

    先贴上案例信息吧:

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    21

    22

    *** (1) TRANSACTION:

    TRANSACTION 52EDC5761, ACTIVE 0 sec inserting

    mysql ...

    文章

    像教授

    2017-11-26

    910浏览量

    mysql小技巧积累(持续更新)

    一、关于NULL值

    1.update null值时,格式为set col=null,不能是is null,不能是'null'。2.mysql中,Null值不等于'',oracle中,空字符串会被当成null保存。3.select时,mysql基于PADSPACE的校对规则,对CHAR和VA...

    文章

    门牙没了

    2013-11-19

    699浏览量

    【MySQL】死锁案例之一

    一 前言   死锁,其实是一个很有意思,也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。二 案例分析2.1 环境说明MySQL 5.6 事务隔离级别为RR

    CREATE TABLE `...

    文章

    yangyi_dba

    2017-09-17

    1403浏览量

    pt(Percona Toolkit)工具介绍

    pt(Percona Toolkit)工具介绍

    一.介绍1.找出重复的索引和外键    pt-duplicate-key-checker

    例子:[root@node1 ~]# pt-duplicate-key-checker --user=root --password='abc123'

    # ##...

    文章

    技术小甜

    2017-11-16

    1168浏览量

    当并发insert on duplicate key update遇见死锁:更新丢失

    数据库死锁问题,是一个老生常谈且很常见的问题,网上也有非常多对于各类死锁场景的解析和复现,但凡和死锁有关,无外乎不涉及数据库隔离等级、索引、以及innodb锁等相关原因。但是我这个案例我搜遍了全网也没能找到比较相似情况。于是我想尽可能的复现出这种情况,找出死锁的原因,找出可能出现的隐患。

    问题的背...

    文章

    贺小白同学

    2019-11-15

    3687浏览量

    MySQL更新死锁问题

    作为一个社交类的 App ,我们有很多操作都会同时发生,为了确保数据的一致性,会采用数据库的事物。

    比如现在我们有一个点赞操作,点赞成功后,需要更改文章的热度。以下是 SQL 语句:

    INSERT INTO user_praise(uid,plan_id,stage_id) VALUES(1234...

    文章

    developerguy

    2016-08-29

    731浏览量

    mysql死锁,等待资源,事务锁,Lock wait timeout exceeded; try restarting transaction解决

    mysql死锁,等待资源,事务锁,Lock wait timeout exceeded; try restarting transaction解决

    前面已经了解了InnoDB关于在出现锁等待的时候,会根据参数innodb_lock_wait_timeout的配置,判断是否需要进行timeout的操...

    文章

    科技小能手

    2017-11-12

    1752浏览量

    Waiting Auto-INC LOCK导致死锁

    今天下午在看死锁相关的文档,到线上查看一生产数据库的时候,正好发现了show engine innodb status有一个死锁的信息:

    LATEST DETECTED DEADLOCK

    ------------------------

    120626 20:00:30

    *** (1) TRANS...

    文章

    玄惭

    2016-03-25

    3922浏览量

    MySQL 死锁检测

    对于死锁,MySQL并没有提供提供直接的变量来表示。对于5.5版本之后的performance_shcema可以提供锁的详细信息(但我们还是5.0呢),对于InnoDB自带的监控器 Innodb_lock_monitor 其输出总是输出到错误日志中,不方便进行对比。

    我监控采用的是za...

    文章

    像教授

    2017-11-26

    1037浏览量

    一次诡异的数据库“死锁”,问题究竟在哪里?

    程序死锁的问题,很难调试,看进程堆栈,看各个线程与锁的情况,对照代码进行排查。

    数据库死锁的问题,更难,看不了数据库堆栈,也看不了数据库线程与锁,更难以对照代码排查。

    前段时间,和一个朋友讨论了一个“疑似”数据库死锁的问题,最后进行试验与排查,找到了问题所在。

    场景如下:

    同一个表,高并发事务,...

    文章

    初商

    2019-08-07

    309浏览量

    mysql死锁问题分析

    线上某服务时不时报出如下异常(大约一天二十多次):“Deadlock found when trying to get lock;”。

    Oh, My God! 是死锁问题。尽管报错不多,对性能目前看来也无太大影响,但还是需要解决,保不齐哪天成为性能瓶颈。

    为了更系统的分析...

    文章

    力君

    2016-02-07

    14086浏览量

    一个JDBC驱动注册死锁问题总结

    群里有个大神(你假笨)再讲解工作中碰到的一个死锁问题.

    这个是大神后来总结的文章:http://lovestblog.cn/blog/2014/07/08/jdk-sql-deadlock/

    情况是这样的:

    项目碰到多线程初始化JDBC驱动时,产生死锁,如下实例所示: (我的环境: JDK...

    文章

    青衫无名

    2017-06-02

    1441浏览量

    MySQL · 答疑解惑 · 物理备份死锁分析

    背景

    本文对 5.6 主备场景下,在备库做物理备份遇到死锁的case进行分析,希望对大家有所帮助。

    这里用的的物理备份工具是 Percona-XtraBackup(PXB),有的同学可能不清楚其备份流程,所以这里先简单说下,PXB的备份步骤是这样的:

    拷贝 InnoDB redo log,这是一...

    文章

    db匠

    2016-05-23

    1495浏览量

    [MySQL 学习] Innodb锁系统(1)之如何阅读死锁日志

    前言:

    最近经常碰到死锁问题,由于对这块代码不是很熟悉,而常持有对文档怀疑的观点。决定从几个死锁问题着手,好好把Innodb锁系统的代码过一遍。

    以下的内容不敢保证完全正确。只是我系统学习的过程。

    /...

    文章

    zhaiwx_yinfeng

    2016-05-10

    3519浏览量

    阿里数据库内核月报:2016年01月

    # 01 MySQL · 引擎特性 · InnoDB 事务锁系统简介

    # 02 GPDB · 特性分析· GreenPlum Primary/Mirror 同步机制

    # 03 MySQL · 专家投稿 · MySQL5.7 的 JSON 实现

    # 04 MySQL · 特性分析 · 优化器 ...

    文章

    场景研读

    2017-06-07

    908浏览量

    【MySQL】死锁案例之四

    一 前言

    死锁,其实是一个很有意思,也很有挑战的技术问题,大概每个DBA和部分开发同学都会在工作过程中遇见过 。关于死锁我会持续写一个系列的案例分析,希望能够对想了解死锁的朋友有所帮助。本文介绍一例三个并发insert 导致的死锁,根本原因还是在于insert 唯一键申请插入意向锁这个特殊的G...

    文章

    yangyi_dba

    2017-09-18

    1762浏览量

    util 100% 怪异问题分析

    环境

    1. DB: Server version: 5.7.18-log MySQL Community Server (GPL)

    2. OS: CentOS release 6.6 (Final)

    问题描述

    问题要害

    1. 不定时的磁盘util 100%

    2. 每次持续时间就几秒钟

    问题分析

    第...

    文章

    兰春

    2017-06-09

    3622浏览量

    TDH_Socket开源介绍

    TDH_Socket开源啦源码地址: https://github.com/taobao/TDH_Socket同时还开源了Java客户端: https://github.com/taobao/tdhs-java-client现在介绍一下TDH_Socket:TDH_Socket是一个MySQL d...

    文章

    沉默术士

    2017-05-02

    1331浏览量

    阿里数据库内核月报:2016年03月

    # 01 MySQL · TokuDB · 事务子系统和 MVCC 实现

    # 02 MongoDB · 特性分析 · MMAPv1 存储引擎原理

    # 03 PgSQL · 源码分析 · 优化器逻辑推理

    # 04 SQLServer · BUG分析 · Agent 链接泄露分析

    # 05 Re...

    文章

    场景研读

    2017-06-07

    1030浏览量

    更多相关内容
  • MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1、MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级...
  • 主要给大家实例分析了一个mysql死锁场景的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • 在实际应用中经常会遇到的与锁相关的异常情况,当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁,严 重影响应用的正常执行。 在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁...
  • 死锁的本质是资源竞争,批量插入如果顺序不一致很容易导致死锁,我们来分析一下这个情况。为了方便演示,把批量插入改写为了多条 insert。 先来做几个小实验,简化的表结构如下 CREATE TABLE `t1` ( `id` int(11...
  • 大牛出手MySQL死锁深入分析,关于死锁是工作与面试经常遇到的。
  • MySql 死锁

    2021-07-26 14:32:22
    MySql 死锁一、什么是死锁InnoDB存储引擎定义了四种类型的行锁隔离等级对加锁的影响当前数据对加锁的影响二、为什么会形成死锁两阶段锁协议产生死锁的四个必要条件三、MySQL 如何处理死锁?杀死进程MySQL表间隙锁...

    MySql 死锁

    一、什么是死锁

    官方定义如下:两个事务都持有对方需要的锁,并且在等待对方释放,并且双方都不会释放自己的锁。

    这个就好比你有一个人质,对方有一个人质,你们俩去谈判说换人。你让对面放人,对面让你放人。

    在这里插入图片描述
    共享锁(S Lock):允许事务读取一行数据,多个事务可以拿到一把S锁(即读读并行);

    排他锁(X Lock):允许事务删除或更新一行数据,多个事务有且只有一个事务可以拿到X锁(即写写/写读互斥);

    记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap

    间隙锁(LOCK_GAP): lock_mode X locks gap before rec

    Next-key 锁(LOCK_ORNIDARY): lock_mode X
    Next-key锁是记录锁和间隙锁的组合,它指的是加在某条记录以及这条记录前面间隙上的锁。

    插入意向锁(LOCK_INSERT_INTENTION): lock_mode X locks gap before rec insert intention

    InnoDB存储引擎定义了四种类型的行锁

    • LOCK_ORDINARY:Next-Key 锁,锁一条记录及其之前的间隙,这是 RR 隔离级别用的最多的锁,从名字也能看出来;lock_mode X

    • LOCK_GAP:间隙锁,锁两个记录之间的 GAP,防止记录插入;lock_mode X locks gap before rec

    • LOCK_REC_NOT_GAP:记录锁 只锁记录;lock_mode X locks rec but not gap

    • LOCK_INSERT_INTENSION:插入意向锁,插入意向 GAP 锁,插入记录时使用,是 LOCK_GAP 的一种特例。lock_mode X locks gap before rec insert intention

    隔离等级对加锁的影响

    隔离级别 读数据一致性 脏读 不可重复读 幻读
    未提交读(Read uncommitted) 最低级别,只能保证不读取物理上损坏的数据,事务可以看到其他事务没有被提交的数据(脏数据)
    已提交度(Read committed) 语句级,事务可以看到其他事务已经提交的数据
    可重复读(Repeatable read) 事务级,事务中两次查询的结果相同
    可序列化(Serializable) 串行

    这里说明一下,RC 总是读取记录的最新版本,而 RR 是读取该记录事务开始时的那个版本,虽然这两种读取的版本不同,但是都是快照数据,并不会被写操作阻塞,所以这种读操作称为 快照读(Snapshot Read)

    MySQL 还提供了另一种读取方式叫当前读(Current Read),它读的不再是数据的快照版本,而是数据的最新版本,并会对数据加锁,根据语句和加锁的不同,又分成三种情况:

    • SELECT … LOCK IN SHARE MODE:加共享(S)锁
    • SELECT … FOR UPDATE:加排他(X)锁
    • INSERT / UPDATE / DELETE:加排他(X)锁

    当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的:RC 只加记录锁,RR 除了加记录锁,还会加间隙锁,用于解决幻读问题。

    当前数据对加锁的影响

    比如一条最简单的根据主键进行更新的 SQL 语句,如果主键存在,则只需要对其加记录锁,如果不存在,则需要在加间隙锁。

    二、为什么会形成死锁

    看到这里,也许你会有这样的疑问,事务和谈判不一样,为什么事务不能使用完锁之后立马释放呢?

    居然还要操作完了之后一直持有锁?

    这就涉及到 MySQL 的并发控制了。

    MySQL的并发控制有两种方式,一个是 MVCC,一个是两阶段锁协议。

    那么为什么要并发控制呢?

    是因为多个用户同时操作 MySQL 的时候,为了提高并发性能并且要求如同多个用户的请求过来之后如同串行执行的一样(可串行化调度)。

    两阶段锁协议

    官方定义:

    两阶段锁协议是指所有事务必须分两个阶段对数据加锁和解锁,在对任何数据进行读、写操作之前,事务首先要获得对该数据的封锁;

    在释放一个封锁之后,事务不再申请和获得任何其他封锁。

    对应到 MySQL 上分为两个阶段:

    • 扩展阶段(事务开始后,commit 之前):获取锁
    • 收缩阶段(commit 之后):释放锁

    就是说呢,只有遵循两段锁协议,才能实现 可串行化调度。

    但是两阶段锁协议不要求事务必须一次将所有需要使用的数据加锁,并且在加锁阶段没有顺序要求,所以这种并发控制方式会形成死锁。

    产生死锁的四个必要条件

    (1) 互斥条件:一个资源每次只能被一个进程使用。
    (2) 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
    (3) 不剥夺条件:进程已获得的资源,在末使用完之前,不能强行剥夺。
    (4) 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

    三、MySQL 如何处理死锁?

    MySQL有两种死锁处理方式:

    • 等待,直到超时(innodb_lock_wait_timeout=50s)。
    • 发起死锁检测,主动回滚一条事务,让其他事务继续执行(innodb_deadlock_detect=on)。

    由于性能原因,一般都是使用死锁检测来进行处理死锁。

    死锁检测

    死锁检测的原理是构建一个以事务为顶点、锁为边的有向图,判断有向图是否存在环,存在即有死锁。

    回滚

    检测到死锁之后,选择插入更新或者删除的行数最少的事务回滚,INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

    杀死进程

    通过以上方法一可以查询对应死锁的数据库进程,可以直接杀掉。

    kill 进程ID

    下列方法有助于最大限度地降低死锁:

    (1)按同一顺序访问对象。
    (2)避免事务中的用户交互。
    (3)保持事务简短并在一个批处理中。
    (4)使用低隔离级别。
    (5)使用绑定连接。

    MySQL表

    CREATE TABLE `book` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `isbn` varchar(255) DEFAULT '',
      `author` varchar(255) DEFAULT '',
      `score` decimal(11,2) DEFAULT '0.00',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('10', 'N001', 'Bob', '9.20');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('18', 'N002', 'ABob', '7.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('25', 'N003', 'CBob', '7.80');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('30', 'N004', 'DBob', '9.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('41', 'N005', 'EBob', '3.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('45', 'N006', 'F5Bob', '6.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('49', 'N006', 'willem', '6.70');
    INSERT INTO `mysqldemo`.`book` (`id`, `isbn`, `author`, `score`) VALUES ('60', 'N007', 'willem', '8.70');
    
    

    间隙锁

    当我们用范围条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;

    对于键值在条件范围内但并不存在的记录,叫做”间隙(GAP)”。

    InnoDB也会对这个”间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。
    在这里插入图片描述
    危害(坑):若执行的条件是范围过大,则InnoDB会将整个范围内所有的索引键值全部锁定,很容易对性能造成影响。

    排他锁

    排他锁,也称写锁,独占锁,当前写操作没有完成前,它会阻断其他写锁和读锁。
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

    共享锁

    共享锁,也称读锁,多用于判断数据是否存在,多个读操作可以同时进行而不会互相影响。当如果事务对读锁进行修改操作,很可能会造成死锁。如下图所示。
    在这里插入图片描述

    分析行锁定

    mysql> show status like 'innodb_row_lock%';
    +-------------------------------+--------+
    | Variable_name                 | Value  |
    +-------------------------------+--------+
    | Innodb_row_lock_current_waits | 0      |
    | Innodb_row_lock_time          | 536668 |
    | Innodb_row_lock_time_avg      | 44722  |
    | Innodb_row_lock_time_max      | 51085  |
    | Innodb_row_lock_waits         | 12     |
    +-------------------------------+--------+
    5 rows in set (0.00 sec)
    
    • innodb_row_lock_current_waits: 当前正在等待锁定的数量

    • innodb_row_lock_time: 从系统启动到现在锁定总时间长度;非常重要的参数,

    • innodb_row_lock_time_avg: 每次等待所花平均时间;非常重要的参数,

    • innodb_row_lock_time_max: 从系统启动到现在等待最常的一次所花的时间;

    • innodb_row_lock_waits: 系统启动后到现在总共等待的次数;非常重要的参数。直接决定优化的方向和策略。

    行锁优化

    1 尽可能让所有数据检索都通过索引来完成,避免无索引行或索引失效导致行锁升级为表锁。

    2 尽可能避免间隙锁带来的性能下降,减少或使用合理的检索范围。

    3 尽可能减少事务的粒度,比如控制事务大小,而从减少锁定资源量和时间长度,从而减少锁的竞争等,提供性能。

    4 尽可能低级别事务隔离,隔离级别越高,并发的处理能力越低。

    四、如何避免发生死锁

    对于 MySQL 的 InnoDb 存储引擎来说,死锁问题是避免不了的,没有哪种解决方案可以说完全解决死锁问题,但是我们可以通过一些可控的手段,降低出现死锁的概率。

    收集死锁信息:

    利用命令 SHOW ENGINE INNODB STATUS查看死锁原因。
    调试阶段开启innodb_print_all_deadlocks,收集所有死锁日志。

    1.对索引加锁顺序的不一致很可能会导致死锁;

    所以如果可以,尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能;

    2.Gap 锁往往是程序中导致死锁的真凶;

    由于默认情况下 MySQL 的隔离级别是 RR,所以如果能确定幻读和不可重复读对应用的影响不大,可以考虑将隔离级别改成 RC,可以避免 Gap 锁导致的死锁;

    3.为表添加合理的索引,如果不走索引将会为表的每一行记录加锁,死锁的概率就会大大增大;

    4.我们知道 MyISAM 只支持表锁,它采用一次封锁技术来保证事务之间不会发生死锁,所以,我们也可以使用同样的思想,在事务中一次锁定所需要的所有资源,减少死锁概率;

    5.避免大事务;

    尽量将大事务拆成多个小事务来处理;因为大事务占用资源多,耗时长,与其他事务冲突的概率也会变高;

    6.避免在同一时间点运行多个对同一表进行读写的脚本;

    特别注意加锁且操作数据量比较大的语句;我们经常会有一些定时脚本,应该避免它们在同一时间点运行;

    7.设置锁等待超时参数:innodb_lock_wait_timeout;

    这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

    五、死锁的影响

    当产生某表死锁的一开始,所有涉及这张表的操作都将受到阻塞。

    假设这张表在业务逻辑上是读写频繁的,那就会使很多操作在那里排队等待,而排队等待会占用数据库连接,当该达到该数据库连接数的最大承载数之后,就会使所有数据库操作均无法再继续下去。

    致使数据库各项指标异常,导致整个环境崩溃。在生产环境中出现这种问题,那是相当致命的,当发现数据库指标异常时因快速处理!

    六、如何发现死锁

    1.查询数据库进程

    主要看State字段,如果出现大量 waiting for …lock 即可判定死锁:

    SHOW FULL PROCESSLIST;
    

    注意:需要拥有root组权限(supper),否则只能看到当前用户的进程,无法查询所有。

    2.查看当前的事务

    SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
    

    3.查看当前锁定的事务

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    

    4.查看当前等锁的事务

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    

    MySQL事务 autocommit 自动提交

    MySQL默认操作模式就是autocommit自动提交模式。

    我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

    查看当前autocommit模式

    show variables like 'autocommit';
    

    在这里插入图片描述
    从查询结果中,我们发现Value的值是ON,表示autocommit开启。

    autocommit 为开启状态时,即使没有手动 start transaction 开启事务,mysql默认也会将用户的操作当做事务即时提交。

    例如,你执行了insert into test values(2)语句,mysql默认会帮你开启事务,并且在这条插入语句执行完成之后,默认帮你提交事务。

    这时候可能有人会问了,那如果我手动开启了事务呢?

    例如如下操作,开启事务并插入两条数据:

    在这里插入图片描述
    由于A客户端没有提交,因此如果我们用B客户端去查询数据,会发现新插入的数据并没有被查询到:
    在这里插入图片描述
    从上述的操作中我们可以明白,当autocommit为ON的情况下,并且又手动开启了事务,那么mysql会把start transaction 与 commit之间的语句当做一次事务来处理,默认并不会帮用户提交需要手动提交,如果用户不提交便退出了,那么事务将回滚。

    禁止使用当前会话的自动提交

    在这里插入图片描述
    在这里插入图片描述

    Mysql 查询正在执行的事务以及等待锁的操作方式

    使用navicat测试学习:
    首先使用 set autocommit = 0;(取消自动提交,则当执行语句commit或者rollback执行提交事务或者回滚)

    mysql> show variables like 'autocommit';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | OFF   |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> UPDATE `mysqldemo`.`user` SET `id`='14', `sex`='0', `age`='28', `province`='北京' WHERE (`id`='14');
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0
    
    

    查看正在执行的事务

    SELECT * FROM information_schema.INNODB_TRX;
    在这里插入图片描述
    在打开一个执行update
    在这里插入图片描述
    在这里插入图片描述
    根据这个事务的线程ID(trx_mysql_thread_id):

    可以使用mysql命令:kill 线程id 杀掉线程

    期间如果并未杀掉持有锁的线程:则第二个update语句提示等待锁超时

    在这里插入图片描述

    查看正在锁的事务

    在这里插入图片描述

    查看等待锁的事务

    在这里插入图片描述

    查询mysql数据库中存在的进程

    在这里插入图片描述

    MySQL命令

    show engines;查看数据库存储引擎

    SHOW ENGINES显示有关服务器的存储引擎的状态信息。这对于检查是否支持存储引擎或参见默认引擎特别有用。

    默认InnoDB

    mysql> show engines;
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)
    
    

    MYSQL 事务处理主要有两种方法:

    在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。

    1、用 BEGIN, ROLLBACK, COMMIT来实现

    • BEGIN 开始一个事务
    • ROLLBACK 事务回滚
    • COMMIT 事务确认

    2、直接用 SET 来改变 MySQL 的自动提交模式:

    • SET AUTOCOMMIT=0 禁止自动提交
    • SET AUTOCOMMIT=1 开启自动提交
    mysql> use RUNOOB;
    Database changed
    mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
    Query OK, 0 rows affected (0.04 sec)
     
    mysql> select * from runoob_transaction_test;
    Empty set (0.01 sec)
     
    mysql> begin;  # 开始事务
    Query OK, 0 rows affected (0.00 sec)
     
    mysql> insert into runoob_transaction_test value(5);
    Query OK, 1 rows affected (0.01 sec)
     
    mysql> insert into runoob_transaction_test value(6);
    Query OK, 1 rows affected (0.00 sec)
     
    mysql> commit; # 提交事务
    Query OK, 0 rows affected (0.01 sec)
    ---------------------------------------------------------------------------------
    mysql>  select * from runoob_transaction_test;
    +------+
    | id   |
    +------+
    | 5    |
    | 6    |
    +------+
    2 rows in set (0.01 sec)
     
    mysql> begin;    # 开始事务
    Query OK, 0 rows affected (0.00 sec)
     
    mysql>  insert into runoob_transaction_test values(7);
    Query OK, 1 rows affected (0.00 sec)
     
    mysql> rollback;   # 回滚
    Query OK, 0 rows affected (0.00 sec)
     
    mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
    +------+
    | id   |
    +------+
    | 5    |
    | 6    |
    +------+
    2 rows in set (0.01 sec)
     
    mysql>
    

    查看当前线程处理情况

    SHOW FULL PROCESSLIST;

    在这里插入图片描述
    show full processlist 返回的结果是实时变化的,是对mysql链接执行的现场快照,所以用来处理突发事件非常有用。

    一般用到 show processlist 或 show full processlist 都是为了查看当前 mysql 是否有压力,都在跑什么语句,当前语句耗时多久了,有没有什么慢 SQL 正在执行之类的。

    通过navicat中的【工具】=> 【服务器监控】进行查看结果如下

    下面针对每列做下介绍:

    Id:链接mysql 服务器线程的唯一标识,可以通过kill来终止此线程的链接。

    User:当前线程链接数据库的用户

    Host:显示这个语句是从哪个ip 的哪个端口上发出的。可用来追踪出问题语句的用户

    db: 线程链接的数据库,如果没有则为null

    Command: 显示当前连接的执行的命令,一般就是休眠或空闲(sleep),查询(query),连接(connect)

    Time: 线程处在当前状态的时间,单位是秒

    State:显示使用当前连接的sql语句的状态,很重要的列,后续会有所有的状态的描述,请注意,state只是语句执行中的某一个状态,一个 sql语句,已查询为例,可能需要经过copying to tmp table,Sorting result,Sending data等状态才可以完成

    Info: 线程执行的sql语句,如果没有语句执行则为null。这个语句可以使客户端发来的执行语句也可以是内部执行的语句。
    在这里插入图片描述

    记录死锁日志

    MySQL 提供了一个系统参数 innodb_print_all_deadlocks 专门用于记录死锁日志,当发生死锁时,死锁日志会记录到 MySQL 的错误日志文件中。

    set GLOBAL innodb_print_all_deadlocks=ON;
    

    开启mysql慢查询和死锁日志

    显示慢查询状态及日志目录

    show variables like '%slow_query_log%';
    

    在这里插入图片描述

    开启慢查询(说明: 1开启;0关闭;)

    set global slow_query_log = 1;
    

    显示慢查询阈值(单位秒),默认执行时间超过10s才会被记录到日志

    show variables like '%long_query%';
    

    在这里插入图片描述

    设置慢查询阈值 (注意:设置后需要重新打开mysql客户端才能到最新的值)

    set global long_query_time = 0.8;
    

    查看死锁的日志是否开启

    show variables like "%innodb_print_all_deadlocks%";
    

    在这里插入图片描述

    开启记录死锁

    set global innodb_print_all_deadlocks=1
    

    InnoDB 行锁等待超时时间(默认为50秒)

    show variables like 'innodb_lock_wait_timeout'; -- 查看当前会话
    show global variables like 'innodb_lock_wait_timeout'; -- 查看全局设置
    

    获取死锁日志信息

    show engine innodb status
    

    该命令可以用来获取死锁信息,但是它有个限制,只能拿到最近一次的死锁日志。如果想获得周期性的日志可以使用MySQL 提供的一套 InnoDb 监控机制。

    死锁日志中列出了死锁发生的时间,以及导致死锁的事务信息(只显示两个事务,如果由多个事务导致的死锁也只显示两个),并显示出每个事务正在执行的 SQL 语句、等待的锁以及持有的锁信息等。

    日志中常见关键字样

    LOCK WAIT 表示事务正在等待锁
    n lock struct(s) 表示该事务的锁链表的长度为 n
    n row lock(s) 表示当前事务持有的行锁个数
    RECORD LOCKS 表示记录锁
    lock_mode X 表示该记录锁为排他锁
    insert intention waiting 表示要加的锁为插入意向锁,并处于锁等待状态
    

    设置mysql 事务锁超时时间 innodb_lock_wait_timeout

    Mysql数据库采用InnoDB模式,默认参数:innodb_lock_wait_timeout设置锁等待的时间是50s,一旦数据库锁超过这个时间就会报错。

    mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_lock_wait_timeout | 50    |
    +--------------------------+-------+
    1 row in set (0.00 sec)
    

    设置事务锁超时时间

    mysql> SET GLOBAL innodb_lock_wait_timeout=7820;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | innodb_lock_wait_timeout | 7820  |
    +--------------------------+-------+
    1 row in set (0.00 sec)
    
    

    共享锁、排他锁与意向锁

    共享锁与独占锁均 用于事务当中,随事务的结束而解除。

    共享锁(share lock)

    又称读锁,读取操作创建的锁。

    一旦上锁,任何事务(包括当前事务)无法对其修改,其他事务可以并发读取数据,也可在对此数据再加共享锁。

    语法:SELECT … LOCK IN SHARE MODE;

    排他锁(exclusive lock)

    又称写锁,如果事务对数据A加上排他锁后,则其他事务不可并发读取数据,也不能再对A加任何类型的锁。获准排他锁的事务既能读数据,又能修改数据。

    语法:SELECT … FOR UPDATE

    意向锁

    InnoDB所用的表级锁,其设计目的主要是为了在一个事务中揭示下一步将要被请求的锁的类型。

    InnoDB中的两个表锁:

    意向共享锁(IS):表示事务准备给数据行加入共享锁,也就是说一个数据行加共享锁前必须先取得该表的IS锁

    意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

    意向锁是InnoDB自动加的,不需要用户干预。

    InnoDB和Myisam不一样,可以同时支持行锁和表锁,

    select * from user where name = "libis" for update; 其中name字段不是user表的索引
    这些情况下InnoDB都会上表锁。

    MySQL事务查看

    1,查看数据库的隔离级别:

    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql>
    

    2,去查看先当前库的线程情况:

    mysql> show processlist;
    

    查看innodb的事务表INNODB_TRX,看下里面是否有正在锁定的事务线程,
    看看ID是否在show full processlist里面的sleep线程中,
    如果是,就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了,我们需要手动kill掉。

    mysql> SELECT * FROM information_schema.INNODB_TRX;
    

    3,kill掉,线程ID;

    mysql> kill 9930577;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
    

    表数据量也不大,按照普通的情况来说,简单的update应该不会造成阻塞的,mysql都是autocommit,不会出现update卡住的情况,去查看下autocommit的值。

    mysql> select @@autocommit;
    +--------------+
    | @@autocommit |
    +--------------+
    | 0 |
    +--------------+
    1 row in set (0.00 sec)
    
    mysql>
    

    看到亮闪闪的0,这个设置导致原来的update语句如果没有commit的话,你再重新执行update语句,就会等待锁定,当等待时间过长的时候,就会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。

    所以赶紧commit刚才执行的update语句,之后 set global autocommit=1;

    4,MySQL锁简写名称

    SELECT ... LOCK IN SHARE MODE:加共享(S)SELECT ... FOR UPDATE:加排他(X)INSERT / UPDATE / DELETE:加排他(X)锁
    
    共享锁(S Lock)
    排他锁(X Lock)
    
    意向共享锁(IS)
    意向排他锁(IX)
    
    展开全文
  • 主要和大家一起聊一聊MySQL死锁与日志二三事,实际业务当中如何快速的定位线上MySQL问题,修复异常?本文根据两个实际case,分享下相关的经验与方法,感兴趣的小伙伴们可以参考一下
  • 主要给大家介绍了一次神奇的MySQL死锁排查的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Mysql具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
  • 通过好多个深夜艰难的单步调试,终于找到了一个理想的断点,可以看到大部分获取锁的过程 代码在lock0lock.c的static enum db_err lock_rec_lock() 函数中,这个函数会显示,获取锁的过程,以及获取锁成功与否。...
  • Mysql死锁

    2021-01-28 05:00:53
    Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction 这样的日志 ,网上看了很多文章 发现这篇文章 ...

    笔者最近在生产环境错误日志上看到updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction 这样的日志 ,网上看了很多文章  发现这篇文章 跟自己的场景非常接近。也没像作者翻binlog日志

    但综合分析我的情况应该是相同表记录行锁冲突

    在我一次执行三个update table1  ,table2  set table.xx=table2.xx where ..

    update table1,table2 set table1.yy=table2.yy where ...

    update table1,table2 set table1.xx=table2.xx where ...

    update table1,table2 set table1.zz=table2.zz where ...

    而且我的where 条件列是没有建立索引 所以锁住的是整表。

    事务一直执行过程中 锁住记录后没有释放  事务2执行时 遇到相同记录 等待事务1  事务三 又等待事务二 因为有相同行数涉及  ,这个时候 事务1执行完毕 等待事务3  就造成死锁现象

    当事务1执行语句1时  锁住了某几行  这时事务2执行的时候 也需要更新相同的几行 但发现那几行被事务1锁住

    format,png

    上图是我接收到的错误报警,SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction,错误信息显示我们业务中有一条数据库操作遇到了死锁情况。接下来就开始我们的追查之旅。

    1.执行“show engine innodb status”获取INNODB引擎当前信息(show engine innodb status 详细介绍)

    LATEST DETECTED DEADLOCK记录了最近一次的死锁情况。

    2017-01-04 09:25:17时间跟我们接收到的报错日志时间吻合。

    上面还可以看出两个事务之间发生锁竞争时,给我们留下的部分数据

    事务1

    UPDATEskuSETquantity=quantity-'1',lock_stock=lock_stock+'1',sys_version=sys_version+1 WHEREid= '15608' ANDquantity>= '1' limit 1

    事务2

    UPDATEskuSETquantity=quantity-'1',lock_stock=lock_stock+'1',sys_version=sys_version+1 WHEREid= '15504' ANDquantity>= '1' limit 1

    死锁的两个资源均被lock_mode X locks了

    最后,mysql给了很重要的一个数据“WE ROLL BACK TRANSACTION (2)” MYSQL回滚了事务2。既然mysql回滚了事务2,那么肯定是事务2的语句触发了死锁,被mysql回滚了,也就是应该为报错日志所记录的那部分。同时,MYSQL执行了事务1,那么事务1的SQL语句肯定被记录在BINLOG中了。

    2.查看binlog日志,找出事务1所执行的语句

    查找依据:

    SQL语句,根据LATEST DETECTED DEADLOCK提供的死锁时记录的sql语句。

    线程ID(mysql的唯一标识): MySQL thread id 11572504

    执行时间(时间线):2017-01-04 09:25:17 7f553477d700

    根据以上三个标识,以及BINLOG的起始标志“BEGIN、COMMIT”,几乎可以100%确定事务1所包含的SQL语句。

    binlog信息大致如下

    3.还原事务2所包含的执行语句

    事务1的语句找齐了,接下来找事务2的语句,还记得我们开头提供的报错日志吗,那个日志里也详细记录了发起请求时的参数情况(截图未展示),根据参数和我们处理业务的代码,可以复现事务2所要执行的语句

    根据两个事务所执行的sql语句,目前可以还原死锁产生的原因了

    4.查看两个事务执行语句的顺序:

    顺序事务1事务2说明

    1

    begin

    2

    begin

    3

    UPDATE sku

    SET quantity=quantity-'1',

    lock_stock=lock_stock+'1',

    sys_version=sys_version+1

    WHERE id = '15504' AND

    quantity >= '1' limit 1

    事务1 给 sku表 id 15504记录上 X 锁

    4

    UPDATE sku

    SET quantity=quantity-'1',

    lock_stock=lock_stock+'1',

    sys_version=sys_version+1

    WHERE id = '15608' AND

    quantity >= '1' limit 1

    事务2 给 sku表 id 15608记录上 X 锁

    5

    UPDATE sku

    SET quantity=quantity-'1',

    lock_stock=lock_stock+'1',

    sys_version=sys_version+1 WHERE id = '15608' AND

    quantity >= '1' limit 1

    这里是关键,事务1想给sku表 id 15608上X锁,发现被别人(事务2)上锁了,等待锁释放

    6

    UPDATE sku

    SET quantity=quantity-'1',

    lock_stock=lock_stock+'1',

    sys_version=sys_version+1

    WHERE id = '15504' AND

    quantity >= '1' limit 1

    事物2打算给sku表id为15504记录上 X 排它锁,发现被其他事务上了,而且此事务居然还在等他提交,这时MYSQL立刻回滚事务2…(php发现MYSQL返回死锁信息,记录该信息到错误日志…发送回滚指令…mysql已经“帮”他回滚了…)

    7

    执行成功

    事务1发现别人的锁释放了,获得X锁,执行成功

    8

    commit

    事务1执行成功,记录binlog日志

    解决方案

    减小事务中的语句数量

    在业务中调整语句的执行顺序,例如可以按照where条件中字段的大小进行一下排序,按照排序后顺序执行,让死锁变为锁等待。

    相关补充

    innodb的行锁,锁的是查询条件中的索引字段,以及索引字段对应的primary key字段

    展开全文
  • 前段时间遇到了一个Mysql 死锁相关的问题,整理一下。 问题描述:Mysql 的修改语句似乎都没有生效,同时使用Mysql GUI 工具编辑字段的值时会弹出异常。 什么是死锁 在解决Mysql 死锁的问题之前,还是先来了解一下...
  • mysql死锁的一些案例

    2019-04-17 01:15:16
    NULL 博文链接:https://fs20041242.iteye.com/blog/1732749
  • 在生产环境中如果出现MySQL死锁问题该如何排查和解决呢,本文将模拟真实死锁场景进行排查,最后总结下实际开发中如何尽量避免死锁发生。 一、准备好相关数据和环境 当前自己的数据版本是8.0.22 mysql> select @@...

    在生产环境中如果出现MySQL死锁问题该如何排查和解决呢,本文将模拟真实死锁场景进行排查,最后总结下实际开发中如何尽量避免死锁发生。

    一、准备好相关数据和环境

    当前自己的数据版本是8.0.22

    mysql> select @@version;
    +-----------+
    | @@version |
    +-----------+
    | 8.0.22    |
    +-----------+
    1 row in set (0.00 sec)
    

    数据库隔离级别(默认隔离级别)

    mysql> select @@transaction_isolation;
    +-------------------------+
    | @@transaction_isolation |
    +-------------------------+
    | REPEATABLE-READ         |
    +-------------------------+
    1 row in set (0.00 sec)
    

    自动提交关闭

    mysql> select @@autocommit;
    +--------------+
    | @@autocommit |
    +--------------+
    |            1 |
    +--------------+
    1 row in set (0.00 sec)
    
    mysql> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@autocommit;
    +--------------+
    | @@autocommit |
    +--------------+
    |            0 |
    +--------------+
    1 row in set (0.00 sec)
    

    表结构

    这个age为 非唯一索引,这点对下面整个案例非常重要。

    -- id是自增主键,age是非唯一索引,name普通字段
    CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
      `age` int DEFAULT NULL COMMENT '年龄',
      `name` varchar(255)  DEFAULT NULL COMMENT '姓名',
      PRIMARY KEY (`id`),
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
    

    表中暂时先插入两条数据


    二、模拟出真实死锁案例

    开启两个终端模拟事务并发情况,执行顺序以及实验现象如下:

    1)事务A执行更新操作,更新成功

    mysql> update  user  set name = 'wangwu' where age= 20;
    Query OK, 1 row affected (0.00 sec)
    
    1. 事务B执行更新操作,更新成功
    mysql> update  user  set name = 'zhaoliu' where age= 10;
    Query OK, 1 row affected (0.00 sec)
    

    3)事务A执行插入操作,陷入阻塞~

    mysql> insert into user values (null, 15, "tianqi");
    

    4)事务B执行插入操作,插入成功,同时事务A的插入由阻塞变为死锁error。

    insert into user values (null, 30, "wangba");
    Query OK, 1 row affected (0.00 sec)
    

    事务A的插入操作变成报错。


    上面四步操作后,我们分别对事务A和事务B进行commit操作。

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    

    我们再来看数据库中表的数据。


    我们发现,事务B的所有操作最终都成功了,而事务A的操作因为报错都回滚了。所以事务A的操作都失败。

    那既然是死锁,为什么回滚事务A,而不是事务B,是随机的还是有机制在里面?

    我们可以理解死锁是数据库对事务的保护机制,一旦发生死锁,MySQL会选择相对小的事务(undo较少的)进行回滚


    三、查看分析死锁日志

    可以用 show engine innodb status,查看最近一次死锁日志哈,执行后,死锁日志如下(只展示部分日志):

    LATEST DETECTED DEADLOCK
    ------------------------
    2021-12-24 06:02:52 0x7ff7074f8700
    *** (1) TRANSACTION:
    TRANSACTION 2554368, ACTIVE 22 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
    
    INSERT INTO user VALUES (NULL, 15, "tianqi")
    
    *** (1) HOLDS THE LOCK(S):
    RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 lock_mode X
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554368 lock_mode X locks gap before rec insert intention waiting
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 80000014; asc     ;;
     1: len 4; hex 80000002; asc     ;;
    
    *** (2) TRANSACTION:
    TRANSACTION 2554369, ACTIVE 14 sec inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
    INSERT INTO user VALUES (NULL, 30, "wangba")
    
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 lock_mode X locks gap before rec
    Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 80000014; asc     ;;
     1: len 4; hex 80000002; asc     ;;
    
    
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 309 page no 5 n bits 72 index idx_age of table `mall_goods`.`user` trx id 2554369 lock_mode X insert intention waiting
    Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
     0: len 8; hex 73757072656d756d; asc supremum;;
    
    *** WE ROLL BACK TRANSACTION (1)
    
    

    1、事务A相关日志

    1)找到关键词TRANSACTION,事务2554368


    2)查看事务1正在执行的sql

    insert into user values (null, 15, "tianqi")
    
    1. 查看当前事务已占有的锁和等待其它事务释放的锁

    2、事务B相关日志

    1)找到关键词TRANSACTION,事务2554369


    2)查看事务2正在执行的sql

    insert into user values (null, 30, "wangba")
    
    1. 查看当前事务已占有的锁和等待其它事务释放的锁

    3、总结

    这里把一些关键的日志截图了下


    我们把这张图换一种方式画下


    1)从图中可以很明显的看出,事务1和事务2都在等对方的锁释放,所以导致了死锁问题。而且最终是事务1进行了回滚。

    2)这个日志提供比较重要的信息就是我们可以看出的是哪两条sql在互相一直等待其它事务的锁释放而产生了死锁,也知道是哪个索引导致产生的死锁,同时也知道最终哪个事务被回滚了。

    3)如果上面的信息还不能帮你定位解决问题,那可以问数据库DB要详细的binlog日志来分析这段时间这两个事务具体执行的所有sql。


    四、总结分析案例中产生死锁的原因

    这个分析就需要对MySQL中的各种锁机制有所了解,还不清楚的话可以看我之前写的两篇文章,看完你就清楚我下面所写的了。

    1、事务A的SQL产生了哪些锁

    1) 事务A的update语句产生哪些锁

    我们先来看

    update  user  set name = 'wangwu' where age= 20;
    

    记录锁

    因为是等值查询,所以这里会在满足age=20的所有数据请求一个记录锁。

    间隙锁

    因为这里是非唯一索引的等值查询,所以一样会产生间隙锁(如果是唯一索引的等值查询那就不会产生间隙锁,只会有记录锁),因为这里只有2条记录
    所以左边为(10,20),右边因为没有记录了,所以请求间隙锁的范围就是(20,+∞),加一起就是(10,20) +(20,+∞)。

    Next-Key锁

    Next-Key锁=记录锁+间隙锁,所以该Update语句就有了(10,+∞)的 Next-Key锁

    事务A的install语句产生哪些锁

    INSERT INTO user VALUES (NULL, 15, "tianqi");
    

    间隙锁

    • 因为age 15(在10和20之间),所以需要请求加(10,20)的间隙锁

    插入意向锁(Insert Intention)

    • 插入意向锁是在插入一行记录操作之前设置的一种间隙锁,这个锁释放了一种插入方式的信号,即事务A需要插入意向锁(10,20),这个插入锁的作用就是提高插入效率的,在分析死锁的时候我们可以不用关心它,只关心上面间隙锁就好了。

    2、事务B的SQL产生了哪些锁

    事务B的update语句产生哪些锁

    我们先来看

    update  user  set name = 'zhaoliu' where age= 10
    

    记录锁

    因为是等值查询,所以这里会在满足age=10的所有数据请求一个记录锁。

    间隙锁

    因为左边没有记录,右边有一个age=20的记录,所以间隙锁的范围是(-∞,10),右边为(10,20),一起就是(-∞,10)+(10,20)。

    Next-Key锁

    Next-Key锁=记录锁+间隙锁,所以该Update语句就有了(-∞,20)的 Next-Key锁

    事务A的install语句产生哪些锁

    INSERT INTO user VALUES (NULL, 30, "wangba")
    

    间隙锁

    • 因为age 30(左边是20,右边没有值),所以需要请求加(20,+∞)的间隙锁

    插入意向锁(Insert Intention)

    • (20,+∞)

    锁都分析清楚了,接下来再来看下是什么地方导致死锁的呢?


    这样以来产生整个死锁的原因也就清楚了,不过这里再补充两点

    1)MySQL的间隙锁虽然有左开右闭的原则,但是其实这个并不完全正确,因为它有可能是左闭右开,也可能是左开右开,它会跟你插入主键值位置有关,具体的可以看我之前写的一篇文章
    里面有完整示例MySQL记录锁、间隙锁、临键锁小案例演示。所以这里间隙锁写的都是左开右开的范围,可能临界点有点模糊,但不影响分析这个案例的死锁问题。

    2)通过事务A和事务B的update语句,我们可以发现其实它们都持有间隙锁(10,20)的这段范围,说明间隙锁范围是可以相互兼容的,意思就是只要你的10不在我(10,+∞)的间隙锁范围内,那就可以产生部分重合的间隙锁,也就是这里的(10,20)。


    五、实际开发中如何尽量避免死锁发生

    一般来讲在实际开发中,很少会发生死锁的情况,尤其是在业务并发量不是很大的情况下。在并发很大的情况下可能会存在偶尔产生死锁。

    不过呢,在自己实际开发中,有遇到过请求一个接口出现100%概率死锁的情况。

    当时的场景其实很简单。一段业务代码中,有去走Dubbo调其它接口服务,这就存在了两个事务,结果各自事务提交的时候,都需要等待对方的锁释放,就导致每次都发生死锁超时。这其实是一种代码不规范而导致死锁的发生。

    这里也总结下如何尽量避免死锁发生。

    1)不同的应用访问同一组表时,应尽量约定以相同的顺序访问各表。对一个表而言,应尽量以固定的顺序存取表中的行。这点真的很重要,它可以明显的减少死锁的发生。

    举例:好比有a,b两张表,如果事务1先a后b,事务2先b后a,那就可能存在相互等待产生死锁。那如果事务1和事务2都先a后b,那事务1先拿到a的锁,事务2再去拿a的锁,如果锁冲突那就会等待事务1释放锁,那自然事务2就不会拿到b的锁,那就不会堵塞事务1拿到b的锁,这样就避免死锁了。

    2)在主键等值更新的时候,尽量先查询看数据库中有没有满足条件的数据,如果不存在就不用更新,存在才更新。为什么要这么做呢,因为如果去更新一条数据库不存在的数据,一样会产生间隙锁。

    举例:如果表中只有id=1和id=5的数据,那么如果你更新id=3的sql,因为这条记录表中不存在,那就会产生一个(1,5)的间隙锁,但其实这个锁就是多余的,因为你去更新一个数据都不存在的数据没有任何意义。

    3)尽量使用主键更新数据,因为主键是唯一索引,在等值查询能查到数据的情况下只会产生行锁,不会产生间隙锁,这样产生死锁的概率就减少了。当然如果是范围查询,一样会产生间隙锁。

    4)避免长事务,小事务发送锁冲突的几率也小。这点应该很好理解。

    5)在允许幻读和不可重复度的情况下,尽量使用RC的隔离级别,避免gap lock造成的死锁,因为产生死锁经常都跟间隙锁有关,间隙锁的存在本身也是在RR隔离级别来解决幻读的一种措施。


    感谢

    这篇文章给自己提供了很好的思路,这篇文章也基本上按照这个思路往下写的

    手把手教你分析MySQL死锁问题

    声明: 公众号如需转载该篇文章,发表文章的头部一定要 告知是转至公众号: 后端元宇宙。同时也可以问本人要markdown原稿和原图片。其它情况一律禁止转载!

    展开全文
  • mysql 死锁死锁检测

    千次阅读 2022-03-24 17:17:59
    1、当mysql请求发生并发时,不同线程执行的事务操作需要获取相同资源的锁,涉及的线程都在等待别的线程释放锁,几个线程都进入无限等待的状态时,就出现死锁了。 2、innodb引擎,可以通过show engine innodb status...
  • 借着这个机会又重新学习了一下mysql死锁知识以及常见的死锁场景。在多方调研以及和同事们的讨论下终于发现了这个死锁问题的成因,收获颇多。虽然是后端程序员,我们不需要像DBA一样深入地去分析与锁相关的源码,...
  • mysql死锁分析

    2017-03-01 09:23:14
    mysql死锁分析
  • mysql 死锁:如何解决mysql死锁

    千次阅读 2021-01-21 11:36:15
    1.session1 执行 delete 会在唯一索引 c2 的 c2 = 15 这一记录上加 X lock(也就是在MySQL 内部观测到的:X Lock but not gap);2.session2 和 session3 在执行 insert 的时候,由于唯一约束检测发生唯一冲突,会加 S...
  • MySQL死锁

    2021-01-19 04:31:56
    一、查看MySQL死锁MySQL数据库会自己解决死锁,kill掉一个session的事务,让另外一个session的事务可以执行SQL>SHOW ENGINE INNODB STATUS\GLATEST DETECTED DEADLOCK2018-02-12 15:42:06 0x7f6bd43df700(1) ...
  • MySQL死锁与优化

    2021-01-19 05:03:19
    这段时间处理了两个比较有意思的MySQL问题,一个死锁的,一个优化的,陡然发现其实自己对MySQL的理解还不深入,很多运行机制也是知其然但不知其所以然,后续还需要好好恶补一下底层知识。一次不可思议的死锁假设有...
  • MySQL死锁的产生和解决方法?

    千次阅读 2022-02-14 21:43:45
    MySQL死锁的产生和解决方法? 本文总结整理转载自:https://blog.csdn.net/qq_34107571/article/details/78001309、https://blog.csdn.net/qq_16681169/article/details/74784193、...
  • 说个很早之前自己遇到过数据库死锁问题。 有个业务主要逻辑就是新增订单、修改订单、查询订单等操作。然后因为订单是不能重复的,所以当时在新增订单的时候做了幂等性校验,做法就是在新增订单记录之前,先通过 ...
  • MySql死锁问题

    2022-01-17 20:34:21
    MySql死锁问题 插入死锁 产生背景 比如在商品秒杀的场景中,我们需要进入商品的流水情况,此时并发压力较大,不进行处理容易出现死锁 核心条件:n(n>2)个线程并发插入 流程示意图 Client A Client B Client...
  • MySQL死锁分析

    2022-03-06 22:13:18
    结合InnoDB行锁类型和实现,触发死锁,解析死锁日志以及分析死锁产生过程
  • Mysql 死锁问题

    2021-11-04 11:25:24
    一、什么是死锁(Deadlock)?? 所谓死锁,是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远...
  • 1、故事起因于2016年11月15日的一个生产bug。业务场景是:归档一个表里边的数据到历史表里边,同是删除主表记录。  2、背景场景简化如下(数据库引擎InnoDb,数据隔离级别RR[REPEATABLE]) — 创建表test1 ...
  • 死锁的概念死锁死锁一般是事务相互等待对方资源,***形成环路造成的。对于死锁,数据库处理方法:牺牲一个连接,保证另外一个连接成功执行。发生死锁会返回ERROR:1213 错误提示,大部分的死锁InnoDB存储引擎本身...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 66,844
精华内容 26,737
关键字:

mysql死锁

mysql 订阅
友情链接: visa_c_demo.zip