mysql 事务长时间占有锁
原创
厵
发布于 2020-12-20 09:20:13
发布于 2020-12-20 09:20:13
代码可运行
运行总次数：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
解决方法：
- 尽可能使用autocommit，不要单独管理
- 如果确实有transction需求，那么代码需要补货异常，并执行commit或者rollback
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
暂无评论
推荐阅读
推荐阅读
相关推荐
深入了解epoll模型 -- 开卷有益
更多 >
腾讯云开发者
