一、问题描述：

同事反馈线上一个表有其中一条数据无法删除，其他都正常，我拿到删数据的sql，尝试执行，报错如下：

mysql>deletefromfacebook_postswhereid=7048962;ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction

二、问题处理

从报错信息看，应该是关于这条数据有事物未提交，锁等待超时了，下面我们就开始验证并解决问题

1、在sql执行期间，通过information_schema.innodb_trx表找到这个sql的事物ID（5316933097 ）

mysql>selecttrx_id,trx_started,trx_requested_lock_id,trx_mysql_thread_id,trx_queryfrominformation_schema.innodb_trxwheretrx_query='deletefromfacebook_postswhereid=7048962';+------------+---------------------+------------------------+---------------------+-----------------------------------------------+|trx_id|trx_started|trx_requested_lock_id|trx_mysql_thread_id|trx_query|+------------+---------------------+------------------------+---------------------+-----------------------------------------------+|5316933097|2017-08-1507:31:57|5316933097:923:24693:6|1798850878|deletefromfacebook_postswhereid=7048962|+------------+---------------------+------------------------+---------------------+-----------------------------------------------+1rowinset(0.00sec)

关于innodb_trx表字段含义的解释：

mysql>descinformation_schema.innodb_trx;+----------------------------+---------------------+------+-----+---------------------+-------+|Field|Type|Null|Key|Default|Extra|+----------------------------+---------------------+------+-----+---------------------+-------+|trx_id|varchar(18)|NO||||#事务ID|trx_state|varchar(13)|NO||||#事物状态|trx_started|datetime|NO||0000-00-0000:00:00||#事物开始时间|trx_requested_lock_id|varchar(81)|YES||NULL||#事物请求锁ID|trx_wait_started|datetime|YES||NULL||#事物开始等待时间|trx_weight|bigint(21)unsigned|NO||0||#|trx_mysql_thread_id|bigint(21)unsigned|NO||0||#事物线程ID，即showprocesslist看到ID|trx_query|varchar(1024)|YES||NULL||#具体SQL|trx_operation_state|varchar(64)|YES||NULL||#事物当前操作状态|trx_tables_in_use|bigint(21)unsigned|NO||0||#事物中有多少个表被使用|trx_tables_locked|bigint(21)unsigned|NO||0||#使用拥有多少个锁|trx_lock_structs|bigint(21)unsigned|NO||0||#|trx_lock_memory_bytes|bigint(21)unsigned|NO||0||#事物锁住的内存大小|trx_rows_locked|bigint(21)unsigned|NO||0||#事物锁住的行数|trx_rows_modified|bigint(21)unsigned|NO||0||#使用修改的行数|trx_concurrency_tickets|bigint(21)unsigned|NO||0||#事物并发票数|trx_isolation_level|varchar(16)|NO||||#事物隔离级别|trx_unique_checks|int(1)|NO||0||#是否唯一性检查|trx_foreign_key_checks|int(1)|NO||0||#是否外键检查|trx_last_foreign_key_error|varchar(256)|YES||NULL||#最后的外键错误|trx_adaptive_hash_latched|int(1)|NO||0||#|trx_adaptive_hash_timeout|bigint(21)unsigned|NO||0||#|trx_is_read_only|int(1)|NO||0||#|trx_autocommit_non_locking|int(1)|NO||0||#+----------------------------+---------------------+------+-----+---------------------+-------+24rowsinset(0.00sec)

2、通过上面步骤1找到的事物ID，找到占有锁的事物ID（5316888834 ）

mysql>select*frominformation_schema.innodb_lock_waitswhererequesting_trx_id=5316933097;+-------------------+------------------------+-----------------+------------------------+|requesting_trx_id|requested_lock_id|blocking_trx_id|blocking_lock_id|+-------------------+------------------------+-----------------+------------------------+|5316933097|5316933097:923:24693:6|5316888834|5316888834:923:24693:6|+-------------------+------------------------+-----------------+------------------------+1rowinset(0.00sec)

关于innodb_lock_waits 表的字段含义的解释：

mysql>descinformation_schema.innodb_lock_waits;+-------------------+-------------+------+-----+---------+-------+|Field|Type|Null|Key|Default|Extra|+-------------------+-------------+------+-----+---------+-------+|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+-------------------+-------------+------+-----+---------+-------+4rowsinset(0.00sec)

3、通过步骤2找到的占有锁的事物ID,找到占有锁的事物线程ID（1790259884 ）

mysql>select*frominformation_schema.innodb_trxwheretrx_id=5316888834\G***************************1.row***************************trx_id:5316888834trx_state:RUNNINGtrx_started:2017-08-1506:00:21trx_requested_lock_id:NULLtrx_wait_started:NULLtrx_weight:6trx_mysql_thread_id:1790259884trx_query:NULLtrx_operation_state:NULLtrx_tables_in_use:0trx_tables_locked:0trx_lock_structs:6trx_lock_memory_bytes:1184trx_rows_locked:10trx_rows_modified:0trx_concurrency_tickets:0trx_isolation_level:REPEATABLEREADtrx_unique_checks:1trx_foreign_key_checks:1trx_last_foreign_key_error:NULLtrx_adaptive_hash_latched:0trx_adaptive_hash_timeout:10000trx_is_read_only:0trx_autocommit_non_locking:01rowinset(0.00sec)

4、通过步骤3找的事物ID，可以查看下这个事物发起的账号和主机信息，提供给开发人员查找异常的真正原因，并kill这个事物ID，这条数据就可以正常删除了

#查看下这个事物发起的账号和主机信息mysql>select*frominformation_schema.processlistwhereID=1790259884;+------------+----------+---------------------+--------+---------+------+-------+------+|ID|USER|HOST|DB|COMMAND|TIME|STATE|INFO|+------------+----------+---------------------+--------+---------+------+-------+------+|1790259884|spider_w|172.31.11.143:46120|db_mta|Sleep|1319||NULL|+------------+----------+---------------------+--------+---------+------+-------+------+1rowinset(0.01sec)#kill这个未提交的事物线程IDmysql>CALLmysql.rds_kill(1790259884);QueryOK,0rowsaffected(0.00sec)#删除数据mysql>deletefromfacebook_postswhereid=7041232;QueryOK,1rowaffected(0.02sec)