Simple Life

和这个世界交手这许多年   你是否光彩依旧,兴致盎然...

您现在的位置是:首页 爱折腾 爱折腾详情

恼人的MySQL死锁

发布时间:2016-1-10 作者:Felix 浏览(1955)

    早上到公司,发现服务器上日志记录中Mysql出现了死锁,感叹下,该来的总是会来啊。因为之前这个系统主要是个内部web应用,而且考虑到读的情况应该比较多,并且没有什么需要事务支持的地方,果断选择了MyISAM引擎。随着功能的增加和数据量的增大,系统的架构被我做了一次升级

    1. 为首页几块数据图表展示做了缓存表和统计表的拆分,避免因首页数据量过大,出现数据聚合运算超时的情况

    2. 为几处计算量大的banner展示增加了redis缓存

    3. 为场所表做了一次垂直拆分,拆分出了场所状态表和场所基础信息表,原先在场所表中的上下线时间及状态都移到状态表,并在状态表中做了些字段冗余。

    基础的升级差不多是这些,但是和外部系统的对接增多,需要不定时上报数据更新状态,MyISAM的表所就不再合适,crontab定时计算更新首页数据,以及拆分下来表的增删改操作需要事务支持。基于这些原因,最后还是决定改成InnoDB引擎。但是,大家都知道MyISAM使用的是表锁,表锁是deadlock free的,这是因为MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。而InnoDB支持基于索引的行锁,在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了在InnoDB中发生死锁是可能的,在我把MyISAM引擎改为InnoDB的第一天就出现了有死锁的情况。

    首先死锁的报错信息是:

(1213, 'Deadlock found when trying to get lock; try restarting transaction')

    我们通过执行命令 :

show engine innodb status;

    来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的SQL语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。下面是输出的信息,做了部分省略,保留了死锁部分:

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

    LATEST DETECTED DEADLOCK

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

    160311  8:56:51

    *** (1) TRANSACTION:  # 事物1

    TRANSACTION 7854, ACTIVE 0 sec starting index read

    mysql tables in use 1, locked 1

    LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 1

    MySQL thread id 1261620, OS thread handle 0x7f1c3e5a6700, query id 809805122 10.20.0.6 work Updating

    update datasys_placemanagement set total_num=181, error_num=0  where code = '44050727000211'

    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:      # 等待锁

    RECORD LOCKS space id 21 page no 26 n bits 232 index `PRIMARY` of table `nscenter`.`datasys_placemanagement` trx id 7854 lock_mode X locks rec but not gap waiting    #  冲突的sql语句

    Record lock, heap no 70 PHYSICAL RECORD: n_fields 19; compact format; info bits 0

     0: len 4; hex 80000781; asc     ;;

     1: len 6; hex 000000007851; asc     xQ;;

     2: len 7; hex 5e0000992b0e41; asc ^   + A;;

     3: len 8; hex 80001253cbf0b097; asc    S    ;;

    

    *** (2) TRANSACTION:    # 事物2

    TRANSACTION 7851, ACTIVE 5 sec starting index read

    mysql tables in use 1, locked 1

    3697 lock struct(s), heap size 342456, 54301 row lock(s), undo log entries 3795

    MySQL thread id 1261618, OS thread handle 0x7f1c3e4a4700, query id 809805752 10.20.0.6 work Updating

    update datasys_placemanagement set device_status='E', data_status='S' where place_id=3440

    *** (2) HOLDS THE LOCK(S):    # 持有锁

    RECORD LOCKS space id 21 page no 26 n bits 232 index `PRIMARY` of table `nscenter`.`datasys_placemanagement` trx id 7851 lock_mode X locks rec but not gap      # 冲突的sql语句

    Record lock, heap no 70 PHYSICAL RECORD: n_fields 19; compact format; info bits 0

     0: len 4; hex 80000781; asc     ;;

     1: len 6; hex 000000007851; asc     xQ;;

     2: len 7; hex 5e0000992b0e41; asc ^   + A;;

     3: len 8; hex 80001253cbf0b097; asc    S    ;;

    

    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:    # 等待锁

    RECORD LOCKS space id 21 page no 40 n bits 232 index `PRIMARY` of table `nscenter`.`datasys_placemanagement` trx id 7851 lock_mode X locks rec but not gap waiting     #  冲突的sql语句

    Record lock, heap no 126 PHYSICAL RECORD: n_fields 19; compact format; info bits 0

     0: len 4; hex 80000d70; asc    p;;

     1: len 6; hex 000000007854; asc     xT;;

     2: len 7; hex 600000992e0110; asc `   .  ;;

     3: len 8; hex 80001253d14d0cd7; asc    S M  ;;

    *** WE ROLL BACK TRANSACTION (1)   # 回滚事物1


    问题sql出在哪里已经很明显了,但是我找了半天,愣是没发现出问题,两条不相干的记录怎么锁的。这张表是拆分出来的场所状态表,数据上报会更新上下线时间,crontab脚本会定时更新状态,还有增删改会操作,execl导入场所我也加了表锁。后来一一排查,发现了问题,我脚本跑都是使用的MySQLdb, 批量操作我使用了executemany,看了下源码。

def executemany(self, query, args):
    
"""
Execute a multi-row query.
    
  query -- string, query to execute on server
  args
    Sequence of sequences or mappings, parameters to use with query.
        
  Returns long integer rows affected, if any.
    
  This method improves performance on multiple-row INSERT and
  REPLACE. Otherwise it is equivalent to looping over args with
  execute().
"""

executemany主要改善了多行插入和replace,对于其他的例如更新操作,跟循环执行一样。问题就来了,循环执行很可能会跟其他脚本的批量操作形成资源竞争,两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。

    我的处理方式就是事先对数据进行排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。


总结:

    下面归纳几种避免死锁的常用方法:

     a. 在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。在下面的例子中,由于两个session访问两个表的顺序不同,发生死锁的机会就非常高!但如果以相同的顺序来访问,死锁就可以避免。

     b. 在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。

     c. 在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁

     d. 在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题   

     e. 当隔离级别为READ COMMITTED时,如果两个线程都先执行SELECT...FOR UPDATE,判断是否存在符合条件的记录,如果没有,就插入记录。此时,只有一个线程能插入成功,另一个线程会出现锁等待,当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁。对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁

    尽管通过上面介绍的设计和SQL优化等措施,可以大大减少死锁,但死锁很难完全避免。因此,在程序设计中总是捕获并处理死锁异常是一个很好的编程习惯。



基于 Django 搭建

服务器采用的 阿里云

域名来自 万网

苏ICP备16015443号

©2015-2016 felixglow.com.

GitHub

Design by Felix