MySQL 事务没有提交导致 锁等待

北极之北 发布于 2015/12/22 15:45
阅读 3K+
收藏 4
执行简单的update语句失效:报错

Lock wait timeout exceeded; try restarting transaction


                        jdbc PreparedStatement executeBatch

解决办法:

1、 ps -ef | grep mysql  找到mysql安装路径

2、cd mysql路径-->进入bin,执行mysql -uroot -p进入命令行

3、查看数据库的隔离级别:

mysql> select @@tx_isolation;

+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

4,去查看先当前库的线程情况:

mysql> show full processlist;

+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+

| 1 | event_scheduler | localhost | NULL | Daemon | 9635385 | Waiting on empty queue | NULL |

9930577 | business_web | 192.168.1.21:45503 | business_db | Sleep | 153 | | NULL |

| 9945825 | business_web | 192.168.1.25:49518 | business_db | Sleep | 43 | | NULL |

| 9946322 | business_web | 192.168.1.23:44721 | business_db | Sleep | 153 | | NULL |

| 9960167 | business_web | 192.168.3.28:2409 | business_db | Sleep | 93 | | NULL |

| 9964484 | business_web | 192.168.1.21:24280 | business_db | Sleep | 7 | | NULL |

| 9972499 | business_web | 192.168.3.28:35752 | business_db | Sleep | 13 | | NULL |

| 10000117 | business_web | 192.168.3.28:9149 | business_db | Sleep | 6 | | NULL |

| 10002523 | business_web | 192.168.3.29:42872 | business_db | Sleep | 6 | | NULL |

| 10007545 | business_web | 192.168.1.21:51379 | business_db | Sleep | 155 | | NULL |

没有看到正在执行的慢SQL记录线程,再去查看innodb的事务表INNODB_TRX,看下里面是否有正在锁定的事务线程,看看ID是否在show full processlist里面的sleep线程中,如果是,就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了,我们需要手动kill掉。

5、mysql> SELECT * FROM information_schema.INNODB_TRX\G;

*************************** 1. row ***************************

trx_id: 20866

trx_state: LOCK WAIT

trx_started: 2014-07-31 10:42:35

trx_requested_lock_id: 20866:617:3:3

trx_wait_started: 2014-07-30 10:42:35

trx_weight: 2

trx_mysql_thread_id: 9930577

trx_query: delete from dltask where id=1

trx_operation_state: starting index read

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 376

trx_rows_locked: 1

trx_rows_modified: 0

trx_concurrency_tickets: 0

trx_isolation_level: READ COMMITTED

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

6、看到有这条9930577sqlkill掉,执行kill 9930577;

mysql> kill 9930577;

Query OK, 0 rows affected (0.00 sec)

7、然后再去查询INNODB_TRX表,就没有阻塞的事务sleep线程存在了,如下所示:

mysql> SELECT * FROM INNODB_TRX\G;

Empty set (0.00 sec)

 

ERROR:

No query specified

8,总结分析
表数据量也不大,按照普通的情况来说,简单的update应该不会造成阻塞的,mysql都是autocommit,不会出现update卡住的情况,去查看下autocommit的值。
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|
+--------------+
1 row in set (0.00 sec)

mysql>

看到亮闪闪的0,这个设置导致原来的update语句如果没有commit的话,你再重新执行update语句,就会等待锁定,当等待时间过长的时候,就会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。
所以赶紧commit刚才执行的update语句,之后 set global autocommit=1;

文章抄袭地址:http://www.linuxidc.com/Linux/2014-08/105078.htm



加载中
0
宏哥
宏哥

用pgsql 


聽雨人
聽雨人
回复 @宏哥 : 高手你有测试数据吗?我这边没出现过你说的问题。
宏哥
宏哥
回复 @聽雨人 : 不要猜测, 要实测, mysql 要是有这个能力, 我就不会说它不是数据库了。
聽雨人
聽雨人
回复 @宏哥 : 这种情况下,看sql语句,但一般来说mysql明显是行锁。
宏哥
宏哥
回复 @聽雨人 : 内部实现具体不清楚。 mysql 会锁表。 主要区别就在这里。
聽雨人
聽雨人
回复 @宏哥 : record如何避免一个事务锁住一条数据,而不提交,另外的update能拿到锁?
下一页
0
颖辉小居
颖辉小居

一样的问题,程序是ssh的所有事务交给了spring管理,可是报了这个错误,不知道原因,只能先kill掉。

返回顶部
顶部