mysql 事务长时间占有锁

原创

厵

发布于 2020-12-20 17:20:13

1.4K00

代码可运行

文章被收录于专栏：peiyuanpeiyuan

运行总次数：0

代码可运行

问题：

业务代码日志出现lock timeout的报错，并且配置核查也会报读不到数据的错，具体的表现是：

1. 业务代码进行where的查询操作会导致lock timeout，从show engine innodb status查看有一个transaction已经存在了很长时，并且获取了很多锁

2. 从业务的log里看插入数据成功，但是后面在另一个线程里读不到数据，从Mysql命令行也读不到

MySQL [performance_schema]> show engine innodb status;

---TRANSACTION 2359367371, ACTIVE 2435 sec
26 lock struct(s), heap size 2936, 261 row lock(s), undo log entries 356
MySQL thread id 36811994, OS thread handle 0x7f99ea1c8700, query id 11140778723 16.2.7.21 root
Trx read view will not see trx with id >= 2359367372, sees < 2359367372

MySQL [performance_schema]> SHOW FULL PROCESSLIST ;
+----------+------+-------------------+------------------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+------+-------------------+------------------------+---------+------+-------+-----------------------+
| 36811994 | root | 16.2.7.21:38938 | xxxxxxxxxxx | Sleep | 587 | | NULL |
+----------+------+-------------------+------------------------+---------+------+-------+-----------------------+

MySQL [information_schema]> select * from information_schema.INNODB_TRX where trx_id = '2359367371';
+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started | trx_requested_lock_id | trx_wait_started | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 2359367371 | RUNNING | 2020-08-31 14:00:49 | NULL | NULL | 406 | 36811994 | NULL | NULL | 0 | 0 | 26 | 2936 | 265 | 380 | 0 | REPEATABLE READ | 1 | 1 | NULL | 0 | 9972 | 0 | 0 |
+------------+-----------+---------------------+-----------------------+------------------+------------+---------------------+-----------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+

问题的定位：

定位发现是有业务获取了事务锁，同时关闭autocommit，此时业务出现异常退出，没有commit和rollback，导致transaction没有关闭.

问题的原因：

1. 因为mysql默认的isolation level是REPEATABLE-READ，由于事务一直没有提交，所以这个事务里的所有修改，其他线程都看不到

2. 这个事务会一直持有锁不释放，会导致其他线程在操作对应数据时出现lock wait timeout

解决方法：