bitscn.com
mysql死锁相关查找
如果遇到死锁了，怎么解决呢？找到原始的锁id，然后kill掉一直持有的那个线程就可以了， 但是众多线程，可怎么找到引起死锁的线程id呢？ mysql 发展到现在，已经非常强大了，这个问题很好解决。 直接从数据字典连查找。
我们来演示下。
线程a，我们用来锁定某些记录，假设这个线程一直没提交，或者忘掉提交了。 那么就一直存在，但是数据里面显示的只是sleep状态。
[sql] mysql> set @@autocommit=0; query ok, 0 rows affected (0.00 sec) mysql> use test; reading table information for completion of table and column names you can turn off this feature to get a quicker startup with -a database changed mysql> show tables; +----------------+ | tables_in_test | +----------------+ | demo_test | | t3 | +----------------+ 2 rows in set (0.00 sec) mysql> select * from t3; +----+--------+--------+------------+----+----+----+ | id | fname | lname | birthday | c1 | c2 | c3 | +----+--------+--------+------------+----+----+----+ | 19 | lily19 | lucy19 | 2013-04-18 | 19 | 0 | 0 | | 20 | lily20 | lucy20 | 2013-03-13 | 20 | 0 | 0 | +----+--------+--------+------------+----+----+----+ 2 rows in set (0.00 sec) mysql> update t3 set birthday = '2022-02-23' where id = 19; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 16 | +-----------------+ 1 row in set (0.00 sec) mysql>
线程b， 我们用来进行普通的更新，但是遇到问题了，此时不知道是哪个线程把这行记录给锁定了？
[sql] mysql> use test; reading table information for completion of table and column names you can turn off this feature to get a quicker startup with -a database changed mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec) mysql> update t3 set birthday='2018-01-03' where id = 19; error 1205 (hy000): lock wait timeout exceeded; try restarting transaction mysql> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 17 | +-----------------+ 1 row in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | id | user | host | db | command | time | state | info | +----+------+-----------+------+---------+------+-------+------------------+ | 10 | root | localhost | null | sleep | 1540 | | null | | 11 | root | localhost | null | sleep | 722 | | null | | 16 | root | localhost | test | sleep | 424 | | null | | 17 | root | localhost | test | query | 0 | init | show processlist | | 18 | root | localhost | null | sleep | 5 | | null | +----+------+-----------+------+---------+------+-------+------------------+ 5 rows in set (0.00 sec) mysql> show engine innodb status/g ------------ transactions ------------ trx id counter 189327 purge done for trx's n:o
bitscn.com