mysql多线程update发生死锁

 

问题描述

mysql使用InnoDB引擎，在多线程并发的情况下，发现对数据库表中的数据进行更新操作时发生了死锁

Error updating database. 

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

基础知识

mysql 引擎

1、MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。

2、InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。

mysql锁

表级锁：锁住整张表
行级锁：锁住一行或者多行
ps: 如果数据库表没有加索引，那么更新操作的时候会锁住整张表，也就是所谓的表级锁；如果有索引，并且查询条件中也带有这些字段，那么就会使用行级锁。行级锁并不是锁记录，而是锁索引。

误区

根据上面锁的原理，对引擎为InnoDB的数据库表进行更新操作，想当然的以为只要对更新语句中where条件所用到的每个字段加上索引，那么where后面跟着多个条件字段并且理论上能够唯一确定一条记录的情况下，对这一条记录进行更新操作时，只会对这一条记录（记录的索引）加锁，问题就出在这里。

因为行锁是对索引加锁，那么当where语句中包含多个条件时候，mysql在生成执行计划的时候实际上也只用到一个字段的索引（区分度最大的字段），所以即使where语句中包含多个字段，实际上也只使用了一个字段的索引，那么根据这个字段进行过滤出来的记录数可能就不止一条，这个可以通过explain查看到。

如下面更新操作：

explain UPDATE `binlog`.t_binlog_process_log SET status = 0  WHERE database_name = 'loandb'  AND file_partitions = 'year=2018/month=11/day=11'  AND file_name = '1541909458303-bin-log.003368'  AND type = 'create'  AND table_name = 't_user_data_supply_status'  AND status = 99

发现执行计划中虽然列出了所有的索引，但是真正用到的索引是file_name这个字段上的索引，锁住的记录数也就不是一条，而是根据flie_name能够查询出来的记录条数。于是当高并发的情况下，当两个事务同时需要对同一个file_name的记录记性更新操作时，由于其中一个事务把同一个file_name的所有记录都锁住了，那么必然会导致另外一个事务无法获取到锁。

解决方法

即使where条件中所有的字段有添加了索引，真正查询的时候也只能用到其中一个字段的索引，于是想能不能建立一个联合索引，对where条件中所用到的所有的字段共同构建一个联合索引，再次执行explain，发现这个时候确实只锁住了一条记录，问题解决。