位置: 编程技术 - 正文

MySQL中由load data语句引起死锁的解决案例(mysql load local)

编辑:rootadmin

推荐整理分享MySQL中由load data语句引起死锁的解决案例(mysql load local),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:mysql load 命令,mysql load数据,mysql load 指定字段,mysql load语句,mysql load 指定字段,mysql load语句,mysql load语句,mysql load infile,内容如对您有帮助,希望把文章链接给更多的朋友!

一个线上项目报的死锁,简要说明一下产生原因、处理方案和相关的一些点.

1、背景

这是一个类似数据分析的项目,数据完全通过LOAD DATA语句导入一个InnoDB表中。为方便描述,表结构简化为如下:

导入数据的语句对应为

产生死锁的证据是在show engine innodb status的LATEST DETECTED DEADLOCK段中看到死锁信息,简化为如下:

说明

从上面表格中看出,事务1在等待某一行的锁。而事务2持有这行的锁,但等待表的自增锁(AUTO_INC),判断为死锁,事务回滚。这里事务1没有写出来,但是可以推断,事务1持有这个表的自增锁(否则就不是死锁了)。

2、背景知识1:AUTO_INC lock 及其选项

在InnoDB表中,若存在自增字段,则会维护一个表级别的锁,这里称为自增锁。每次插入新数据,或者update语句修改了此字段,都会需要获取这个锁

由于一个事务可能包含多个语句,而并非所有的语句都与自增字段有关,因此InnoDB作了一个特殊的处理,自增锁在一个语句结束后马上被释放。之所以说是特殊处理,是因为普通的锁,都是在事务结束后释放。

若一个表有自增字段,一个insert语句不指定该字段的值,或指定为NULL时,InnoDB会给它赋值为当前的AUTO_INCREMENT的值,然后AUTO_INCREMENT加1。

与这个自增锁相关的一个参数是innodb_autoinc_lock_mode. 默认值为1,可选为0,1,2。

我们先来看当这个值设置为0时,一个有自增字段的表,插入一行数据时的行为:

1) 申请AUTO_INC锁

2) 得到当前AUTO_INCREMNT值n,给AUTO_INCREMENT 加1

3) 执行插入操作,并将n填入新增的行对应字段中

4) 释放AUTO_INC锁

我们看到这个过程中,虽然InnoDB为了减少锁粒度,在语句执行完成就马上释放,但这锁还是太大了??它包括了插入操作的时间。这就导致了两个insert语句,实际上没办法并行。

没有这个参数之前,行为就是与设置为0相同,0这个选项就是留着兼容的。

很容易想到设置为1的时候,应该是将3) 和 4)对调。但是本文还是要讨论为0的情况,因为我们的前提是LOAD语句,而LOAD语句这类插入多行的语句中(包括insert …select …),即使设置为1也没用,会退化为0的模式。

3、背景知识2:LOAD DATA语句的主从行为

为什么插入多行的语句要即使将innodb_autoinc_lock_mode设置为1,也会用0的模式呢?

主要原因还是为了主从一致性。设想binlog_format='statement',一个LOAD DATA语句在主库的binlog直接记录为语句本身,那从库如何重放:

1) 将load data用到的文件发给slave,slave将文件保存在临时目录。

2) 在slave也执行一次LOAD DATA语句。

其间有一个问题:slave怎么保证load data语句的自增id字段与master相同?

为了解决这个问题,主库的binlog中还有一个set SET INSERT_ID命令,表明这个LOAD DATA语句插入的第一行的自增ID值。这样slave在执行load data之前,先执行了这个set SET INSERT_ID语句,用于保证执行结果与主库一模一样。

上述的机制能保证主从数据一致的前提是:主从库上LOAD DATA语句生成的自增ID值必须是连续的。

4、背景知识1+2:分析

回到前面说的模式0和1的区别,我们看到,如果AUTO_INC锁在整个语句开始之前就获取,在语句结束之后才释放,这样就能保证整个语句生成的id连续??模式0的保证。

对于1,每次拿到下一个值就释放,插入数据后,若需要再申请,则不连续。

MySQL中由load data语句引起死锁的解决案例(mysql load local)

这就是为什么,即使设置为1,对于多行操作,会退化成0。

至此我们知道这个死锁出现的原因,是这两个LOAD DATA语句不仅会访问相同的记录,还会访问同一个AUTO_INC锁,造成互相等待。

到此没完,因为我们知道虽然两个线程访问两个锁可能造成死锁,但是死锁还有另外一个条件,与申请顺序有关。既然AUTO_INC是一个表锁,不论谁先拿到,会阻塞其他同表的LOAD DATA的执行,又为什么会在某个记录上出现锁等待?

5、背景知识3:AUTO_INC的加锁时机

前面我们说到每次涉及到插入新数据,就会要求对AUTO_INC加锁,并列出了流程。但这个流程是对于需要从InnoDB中得到自增值来设置列值的情况。另一种情况是在语句中已经指定了该列的值。

比如对于这个表,执行 insert into tb values(9,). 此时id的值已经明确是9,虽然不需要取值来填,但是插入这行后有可能需要改变AUTO_INCREMENT的值(若原来是<,则应该改为),所以这个锁还是省不了。流程变成:

1) 插入数据

2) 若失败则流程结束

3) 若成功,申请AUTO_INC锁

4) 调用set_max….函数,如有必要则修改AUTO_INCREMENT

5) 语句结束时释放AUTO_INC锁。

6、为什么修改AUTO_INC顺序

这么调整的好处是什么? 主要是为了减少不必要的锁访问。若在插入数据期间发生错误,比如其他字段造成DUPLICATE KEY error,这样就不用访问AUTO_INC锁。

7、死锁过程复现

必须强调是“语句结束时”。这样我们来看一个每行都已经指定了自增列值的LOAD DATA语句的流程(也就是本文例子的情况):

1) 插入第一条数据

2) 申请AUTO_INC锁

3) 插入第二条

4) 申请AUTO_INC 锁(因为已经是自己的,直接成功)

5) 。。。。。。插入剩余所有行

6) 释放AUTO_INC锁。

所以这个流程就简单描述为:插入第一行,申请AUTO_INC锁,然后插入剩下的所有行后再释放。

我们前面提到过,插入第一条数据时可能需要访问的记录锁,是要等到整个事务结束后才释放的.

有了上面的这些背景知识,我们来复现一下死锁出现的过程

可以看到触发条件还是比较苛刻的,尤其是session2要刚好要用到session1锁住的那个记录锁。需要说明,由于InnoDB内部对记录的表示,同一个记录锁并不表示主键值一定相同。

8、解决方案1:去掉不必要的AUTO_INCREMENT字段

在这个业务中,由于所有的数据都是通过LOAD DATA进去,而且都已经指定了自增字段的值,因此这个AUTO)INCREMENT属性是不需要的。

少了一个,就死锁不了了。

9、解决方案2:强制模式1

前面我们说到innodb_autoinc_lock_mode这个参数的可选值有0、1、2。当设置为1的时候,在LOAD DATA语句会退化为模式0。但若设置为2,则无论如何都会使用模式1。

我们前面说到使用模式1会导致LOAD DATA生成的自增id值不连续,这样会导致在binlog_format是1时主从不一致,因此设置为2的前提,是binlog_format 是row.

在binlog_format='row'时,设置innodb_autoinc_lock_mode为2是安全的。

若允许,方案2比方案1更轻量些,不需要修改数据和表结构。

详解MySQL中的死锁情况以及对死锁的处理方法 当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁。死锁发生在事务试图以不同的顺序锁定资源。以StockPrice表上的两个事

Mysql5升级到Mysql5.5的方法 安装5.5依赖安装包代码如下yuminstall-yautoconf*automake*zlib*libxml*ncurses-devel*libgcrypt*libtool*openssl*安装cmake代码如下yuminstall-ycmake在升级前,建议先将之前5.1的mys

Mysql5.7如何修改root密码 版本更新,原来user里的password字段已经变更为authentication_string版本更新缘故,好多网上的教程都不适用了,甚至连官网的文档也不是能够顺利操作的。

标签: mysql load local

本文链接地址:https://www.jiuchutong.com/biancheng/319733.html 转载请保留说明!

上一篇:MySQL存储过程中使用动态行转列(Mysql存储过程中的如何遍历一个查询结果集)

下一篇:详解MySQL中的死锁情况以及对死锁的处理方法(mysql死锁的处理方法)

  • 党建经费提取比例10%
  • 子公司是长期股权投资吗
  • 两家公司原材料能否借用
  • 个税完税凭证在哪里打印出来
  • 甲方代扣水电费怎么做账
  • 支付无法取得发票的赔偿金可否税前扣除
  • 高温补贴能以别的形式发放吗
  • 应付职工薪酬明细账模板
  • 业务协作费是什么
  • 理财产品收益交税
  • 管家婆进货单科目名称怎么录入?
  • 企业所得税国税还是地税征管
  • 房产增值税是怎么交的呢
  • 政府机关开票是普票还是专票
  • 电脑变成代码打不开怎么办
  • 2020国家生育津贴多少钱
  • 建筑材料营改增之前怎么开发票
  • 房地产企业人防设备计入什么科目
  • 基建管理费如何进行结转?
  • 无偿使用固定资产如何缴税
  • 政府无偿给企业划拨土地是否需摊销
  • 企业新增股东
  • 往来账会计怎么做
  • 如何在excel中运算
  • 累计折旧 减少
  • 个体户年报如何公示
  • php面向对象优点,缺点
  • Win10 19043.1237 9月累积更新 KB5005565推送(附更新修复+下载)
  • qqlogin.exe是什么进程 qqlogin.exe应用程序错误解决办法
  • directx/?
  • 如何使用微信公交付款
  • php课堂笔记
  • 退货时会计分录的银行存款能是负数吗
  • monaco编辑器的自定义提示
  • Laravel 5.5 的自定义验证对象/类示例代码详解
  • 无极框架下载
  • php中的强制类型转换函数有哪些?
  • 律师事务所可不可以对外投资呢
  • 接受捐赠收入要缴纳企业所得税吗
  • python3 静态方法
  • 国债 企业
  • 按月缴纳增值税的纳税人申报期限为计算期次月的( )
  • 企业购入固定资产发生的运输费,装卸费
  • 当月进项发票忘记抵扣
  • 实收资本为零该怎么办
  • 税费不足50
  • 佣金和其他费用
  • 母公司将子公司股权无偿转让给子公司
  • 困难企业社保费返还
  • 公司是否可以投资股票
  • 借款利息如何支付
  • 给个体工商户付款可以打到法人卡上吗
  • 企业预交所得税税率
  • 发票项目名称可以自定义吗
  • 配置零部件
  • 公司购买的车辆
  • 企业筹建期间发生的费用应计入什么账户
  • win7鼠标右键一闪就没了
  • 怎样一键重装
  • 怎样升级智慧中小学版本软件
  • Ubuntu 14.04系统怎么安装Nvidia 私有显卡驱动?
  • win8强制关闭程序
  • spyware.exe - spyware是什么进程
  • hosts文件位置在哪
  • win8关机一直转圈
  • mac命令行终端快捷键
  • linux svn管理工具
  • centos 添加服务
  • windows 8怎么样
  • win10取消uac
  • jsonp的使用
  • javascript的核心
  • material design设计
  • c调用java后又调用回
  • nodejs为什么性能这么好
  • nodejs 异步任务队列
  • 个人所得税明细申报记录 厦门
  • 合伙人转让出资的法律规定
  • 企业承包经营责任制
  • 皇家税务与海关署
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设