`

mysql死锁-非主键索引更新引起的死锁

阅读更多
背景:最近线上经常抛出mysql的一个Deadlock,细细查来,长了知识!


分析:错误日志如下:
21:02:02.563 ERROR dao.CommonDao        [pool-15-thread-19] [jbc.trade.qunar.com] [703c9ddbe4b143609035365ca46bff35] - db error , tableId=jbc.trade.qunar.com, sql=update i_pay_record set checktime = now() where order_id in (62818) , par
ams=null
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
      
其中sql更新语句:
update i_pay_record set checktime = now() where order_id in (62818)

很奇妙吧,执行一条sql会有死锁吗?
答案是:会有。

其中奥妙就在 i_pay_record 中的 order_id 字段有索引。

特此转载下面这篇文章很好的讲解了这个问题 !!!
http://blog.csdn.net/aesop_wubo/article/details/8286215
转载:
表结构如下:
CREATE TABLE `user_item` (
  `id` BIGINT(20) NOT NULL,
  `user_id` BIGINT(20) NOT NULL,
  `item_id` BIGINT(20) NOT NULL,
  `status` TINYINT(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1` (`user_id`,`item_id`,`status`)
) ENGINE=INNODB DEFAULT CHARSET=utf-8


SQL语句如下:
update user_item set status=1 where user_id=? and item_id=?  


原因分析
mysql的事务支持与存储引擎有关,MyISAM不支持事务,INNODB支持事务,更新时采用的是行级锁。这里采用的是INNODB做存储引擎,意味着会将update语句做为一个事务来处理。前面提到行级锁必须建立在索引的基础,这条更新语句用到了索引idx_1,所以这里肯定会加上行级锁。
行级锁并不是直接锁记录,而是锁索引,如果一条SQL语句用到了主键索引,mysql会锁住主键索引;如果一条语句操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引。
这个update语句会执行以下步骤:
1、由于用到了非主键索引,首先需要获取idx_1上的行级锁
2、紧接着根据主键进行更新,所以需要获取主键上的行级锁;
3、更新完毕后,提交,并释放所有锁。
如果在步骤1和2之间突然插入一条语句:update user_item .....where id=? and user_id=?,这条语句会先锁住主键索引,然后锁住idx_1。
蛋疼的情况出现了,一条语句获取了idx_1上的锁,等待主键索引上的锁;另一条语句获取了主键上的锁,等待idx_1上的锁,这样就出现了死锁。

解决方案
1、先获取需要更新的记录的主键
select id from user_item where user_id=? and item_id=?  

2、逐条更新
select id from user_item where user_id=? and item_id=?  
for (Long id : idList) {  
    userItemDAO.updateStatus(id, userId, 1);  
}  
update user_item set status=? where id=? and user_id=?  

3、这样貌似解决了,都是对单条进行操作,都是先获取主键上的锁,再获取idx_1上的锁。
不过这个解决方案与先前的更新语句不一样,先前的更新语句对所有记录的更新在一个事务中,采用循环更新后并不在同一个事务中,所以在for循环外面还得开一个事务。

Exception e = (Exception)getDbfeelTransactionTemplate().execute(new TransactionCallback() {
   public Object doInTransaction(TransactionStatus status) {
      try {
        	for(Long id:idList) {
		<span style="white-space:pre">	</span>userItemDAO.updateStatus(id,userId,1)
		}
      		return null;
      }catch(DAOException e) {
         status.setRollbackOnly();
         return e;
      }
      catch (Exception e) {
         status.setRollbackOnly();
         return e;
      }
   }
});


小结:在采用INNODB的MySQL中,更新操作默认会加行级锁,行级锁是基于索引的,在分析死锁之前需要查询一下mysql的执行计划,看看是否用到了索引,用到了哪个索引,对于没有用索引的操作会采用表级锁。如果操作用到了主键索引会先在主键索引上加锁,然后在其他索引上加锁,否则加锁顺序相反。在并发度高的应用中,批量更新一定要带上记录的主键,优先获取主键上的锁,这样可以减少死锁的发生。
0
0
分享到:
评论

相关推荐

    几种不常见的MySQL InnoDB 死锁情况--1

    假设表中的主键是自增ID,两个事务并发插入记录,由于每个事务都需要获取下一个可用的自增ID,如果事务A先获取ID并锁定,但插入操作失败(如违反唯一约束),而事务B在此期间已经获取了更高的ID,那么当事务A回滚并...

    mysql高级部分--包含索引建立优化_函数_存储过程_触发器_及游标

    - **主键索引:** 特殊的唯一索引,不允许存在空值,通常在创建表时定义。 - **组合索引:** 基于多列创建的索引,可以包含连续或非连续的列。 **创建示例:** - 创建普通索引: ```sql CREATE INDEX index_name ...

    这六个 MySQL 死锁案例,能让你理解死锁的原因!.doc

    MySQL 死锁案例详解 在 MySQL 中,死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象。死锁的产生原因是由于两个或两个以上的 Session 加锁的顺序不一致。解决死锁问题的关键就是让...

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

    在描述的死锁场景中,有一个简单的表`dltask`,拥有一个由a、b、c三列组成的唯一索引,以及主键id。事务隔离级别设定为RR(Repeatable Read)。每个事务执行的SQL语句是基于这个唯一索引删除一条记录。理论上,由于...

    mysql死锁分析

    ### MySQL死锁分析 #### 死锁问题背景 在MySQL的使用过程中,死锁是一个较为常见的现象,尤其是在并发量较大的应用场景下。死锁的发生往往会给系统带来不可预知的影响,严重时甚至会导致整个数据库服务不可用。...

    mysql索引和锁机制ppt介绍

    - InnoDB的非主键索引通常是指辅助索引,它们不直接存储数据行,而是存储指向数据行的指针。 - 辅助索引的叶子节点中不仅包含键值,还包括一个指向主键的指针。 #### 五、锁机制 **锁机制概述:** MySQL中的锁机制...

    MySQL死锁、锁、索引相关资料整理

    这是因为二级索引可能导致行锁定顺序与主键索引不一致,从而产生死锁。例如,事务A先锁定索引B的行,然后尝试锁定索引A的行,而事务B则相反,这样就形成了死锁。 解决这类问题,可以检查更新语句是否按照特定的顺序...

    一次MYSQL死锁分析案例1

    - **索引**: 死锁涉及到的是`order_pay_status`表的主键索引。 **行锁类型**: - **记录锁(LOCK_REC_NOT_GAP)**: Session1等待的锁类型,锁定具体记录。 - **间隙锁(LOCK_GAP)**: 未出现在日志中,但可能存在于...

    mysql-常见问题,索引优化

    松散索引(也称为非聚簇索引)不包含主键数据,只存储索引列,需要回表查找完整记录。而紧凑索引(聚簇索引)将数据和索引存储在一起,查询效率更高,但插入和删除操作相对较慢。选择哪种类型取决于具体应用。 以上...

    mysql面试题-mysql经典面试题目-数据库的基本概念-SQL语法-事务处理-索引优化-性能调优-mysql-面试题目

    常见的SQL命令有SELECT用于查询数据,INSERT用于插入数据,UPDATE用于更新数据,DELETE用于删除数据,以及CREATE、ALTER和DROP用于创建、修改和删除数据库对象如表、索引等。 索引是一种特殊的数据结构,类似于书籍...

    MySQL DELETE 删除语句加锁分析1

    在MySQL中,DELETE语句的加锁行为是数据库事务处理的重要组成部分,尤其是在并发环境中,正确理解其加锁机制对于避免死锁和提高系统性能至关重要。本篇文章主要探讨了在不同隔离级别下,针对不同索引类型的DELETE...

    MySQL官方指南 MySQL-5-0-Certification-Study-Guide

    《MySQL官方指南 MySQL-5-0-Certification-Study-Guide》是一本专注于MySQL 5.0版本的权威学习资料,旨在帮助读者掌握这个广泛使用的开源数据库系统的核心知识和技能,为MySQL 5.0认证考试做准备。本书涵盖了MySQL的...

    MySQL 5.7-OCP-题库最终版

    这些知识点主要围绕MySQL 5.7、OCP认证(Oracle Certified Professional)以及与数据库管理和优化相关的具体技术细节。 ### 1. 创建一致性的备份 **问题**: 在一个完全使用InnoDB表的MySQL数据库中,您计划通过`...

    Mysql Innodb死锁情况分析与归纳.docx

    - 由于语句中使用了非聚簇索引`k_id_titleWeight_score`,因此不仅会对主键索引上的行加上X锁,还会对非聚簇索引上的相关行加上X锁。 ##### 锁冲突产生的原因 - **共享锁与排他锁之间的冲突**:当一个事务持有了...

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

    在MySQL数据库中,死锁是两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。本文将深入探讨如何通过调整索引来解决一个具体的线上死锁案例。 首先,让我们看下...

    MySQL19-20讲

    还会涉及不同类型的索引,如主键索引、唯一索引、全文索引以及组合索引的使用策略。 2. **查询优化**:这部分内容可能涵盖SQL查询的优化技巧,包括避免全表扫描、使用EXPLAIN分析查询执行计划、理解JOIN操作的优化...

    mysql军规-sql规范

    - **解释**:事务时间越长,占用资源越多,可能引起死锁等问题。 - **建议**:缩短事务处理时间,尽量减少锁定时间。 **3. 避免使用触发器,用户自定义函数,请由程序取而代之** - **解释**:触发器和用户自定义...

    MySQL16-18讲

    第三范式是数据库设计中的一个重要原则,它要求每个表中的非主键列都完全依赖于主键,而不能有传递依赖。此外,讲解可能包括如何通过分解表来消除数据冗余,以提高数据的一致性和完整性。 第17讲:这一讲可能涉及...

Global site tag (gtag.js) - Google Analytics