今天遇到一个sql事务执行更新操作时间过长导致死锁。排查过程记录如下：首先，sql事务的where条件命中了主键索引，表也不大，可以排除表扫描慢的原因。通过showprocesslist；发现只有SQL事务在操作表，乍一看好像不是死锁的原因：但是咨询yellbehuang后发现，不好判断SQL事务是否死锁通过showprocesslist，但是是通过查询innodb锁的相关表来确定的。与innodb锁相关的主要有三张表，innodb_trx##当前运行的所有事务innodb_locks##当前发生的锁innodb_lock_waits##上表各字段锁和等待的对应关系含义如下：innodb_locks:+——————+——————————+——+——–+————+————+|Field|Type|Null|Key|Default|Extra|+—————+—————————+——+——–+————+————+|lock_id|varchar(81)|NO||||#lockID|lock_trx_id|varchar(18)|NO||||#locktransactionID|lock_mode|varchar(32)|NO||||#lockmode|lock_type|varchar(32)|NO||||#locktype|lock_table|varchar(1024)|NO||||#Lockedtable|lock_index|varchar(1024)|YES||NULL||#Lockedindex|lock_space|bigint(21)unsigned|YES||NULL||#LockedTablespacenumber|lock_page|bigint(21)unsigned|YES||NULL||#lockedpagenumber|lock_rec|bigint(21)unsigned|YES||NULL||#lockedrecordnumber|lock_data|varchar(8192)|YES||NULL||#Lockeddatainnodb_lock_waits:+----------------+-------------+------+-----+--------+------+|字段|类型|空|键|默认|额外|+-------------------+------------+-----+-----+---------+------+|requesting_trx_id|varchar(18)|NO||||#请求锁事务ID|requested_lock_id|varchar(81)|NO||||#请求锁ID|blocking_trx_id|varchar(18)|NO||||#当前拥有锁事务ID|blocking_lock_id|varchar(81)|NO||||#当前拥有锁ID+---------------------+------------+------+-----+--------+-------+i??nnodb_trx:+——————————+————————+——+——–+————————+————+|字段|类型|Null|Key|Extra|+————————————+————————+——+——–+————————+————+|trx_id|varchar(18)|NO|||#交易ID|trx_state|varchar(13)|NO|||#交易状态：|trx_started|datetime|NO|||#交易开始时间；|trx_requested_lock_id|varchar(81)|YES|||#innodb_locks.lock_id|trx_wait_started|datetime|YES|||#事务开始等待时间|trx_weight|bigint(21)unsigned|NO|||#|trx_mysql_thread_id|bigint(21)unsigned|NO|||#transactionthreadID|trx_query|varchar(1024)|YES|||#具体SQL语句|trx_operation_state|varchar(64)|YES|||#事务当前运行状态|trx_tables_in_use|bigint(21)unsigned|NO|||#事务中使用了多少表|trx_tables_locked|bigint(21)unsigned|NO|||#事务有多少锁|trx_lock_structs|bigint(21)unsigned|NO|||#|trx_lock_memory_bytes|bigint(21)unsigned|NO|||#锁定的内存大小事务(B)|trx_rows_locked|bigint(21)unsigned|NO|||#事务锁定的行数|trx_rows_modified|bigint(21)unsigned|NO|||#事务改变的行数|trx_concurrency_tickets|bigint(21)unsigned|NO|||#交易和发票编号|trx_isolation_level|varchar(16)|NO|||#交易隔离级别|trx_unique_checks|int(1)|NO|||#是否唯一校验|trx_foreign_key_checks|int(1)|否|||#外键检查|trx_last_foreign_key_error|varchar(256)|YES|||#上次外键错误|trx_adaptive_hash_latched|int(1)|NO|||#|trx_adaptive_hash_timeout|bigint(21)unsigned|NO|||#可以通过select*fromINNODB_LOCKSainnerjoinINNODB_TRXbona.lock_trx_id=b.trx_idandtrx_mysql_thread_id=threadid获取sql的锁状态，线程id可以通过上面的showprocesslist得到，执行结果如下：此时发现sql连接确实处于LOCKWAIT锁等待状态。通过select*frominnodb_lock_waitswhererequesting_trx_id=75CB26E5（即上面查询得到的lock_trx_id），可以得到当前拥有锁的事务ID75CB26AE，然后通过select*frominnodb_trxwherelock_trx_id=75CB26AE得到sql语句andthreadid从上面的结果可以看出事务是running状态，但是sql为null，threadid是上面showprocesslist中206机器的30764端口的连接，连接是处于睡眠状态。为什么sql在为null的时候还持有锁呢？查询了相关资料，咨询了jameszhou，才知道这其实和innodb引擎的写机制有关。表上没有索引，那么innodb会在后台创建一个隐藏的聚簇主键索引），然后写入到缓存中，最后在事务提交释放锁后写入到DB中。sql之所以为null，是因为连接已经将sql更新操作写入了缓存，但是由于代码bug，没有最终commit，行锁已经被占用，后续新的连接要写入这行数据，但是因为一直在取，所以一直处于没有拿到行锁的漫长等待状态。那为什么innodb需要写两次呢？以下是我查询相关资料得出的结论：因为innodb中的log是有逻辑的，所谓逻辑就是插入一条记录的时候，可能会导致某个页面（这条记录最后要插入的位置）在多个偏移位置写入一定长度的值，如页头记录数、槽数、页尾槽数据、页中记录值等，这些是一些物理操作。innodb为了节省日志量等原因，设计成逻辑处理方式，即会以页为单位插入一条记录，那么日志记录中记录的内容就是表空间号，页码，记录的每一列的值等，都是在内部转换为以上的物理操作。但是这里有一个问题就是，如果page本身是错误的，这种错误可能是因为writebreak（1页16K，多次写入，后面可能没有写入成功，导致page不完整），那么这个逻辑操作是无法完成的，因为它的前提是页面正确完整，因为如果页面不正确，这个页面中的数据就是无效的，各种不可预见的问题。所以也正是因为这个问题，我们首先要保证页面是正确的。方法是写两遍。它的idea最终是一个backupidea，也就是镜像。innodb两次写的过程：两次写可以看作是在innodb表空间内部分配的一个短期日志文件，这个日志文件包含100个数据页。Innodb在写出buffer中的数据页时采用了一次写入多个page的方式，这样可以将多个page依次写入writebuffer两次，并调用fsync()来保证数据写出到磁盘，然后将数据页调度到它们的实际存储位置并再次调用fsync()。当从故障中恢复时，InnoDB检查双写缓冲区的内容和数据页的原始存储位置。如果数据页在两个写缓冲区中处于不一致的状态，它将被简单地丢弃。如果原存储位置不一致，则从两个写缓冲区中读取。减少。原文链接：https://www.qcloud.com/community/article/886137作者：陈文晓【本文为专栏作者《腾讯云技术社区》原创稿件，转载请联系原作者获得授权】点此查看文章作者更多好文
