linux mysql 死锁进程_Linux数据库：MYSQL死锁相关查找1

如果遇到死锁了，怎么解决呢？找到原始的锁ID,然后KILL掉一直持有的那个线程就可以了， 但是众多线程，可怎么找到引起死锁的线程ID呢？ MySQL 发展到现在，已经非常强大了，这个问题很好解决。 直接从数据字典连查找。

我们来演示下。

线程A,我们用来锁定某些记录，假设这个线程一直没提交，或者忘掉提交了。 那么就一直存在，但是数据里面显示的只是SLEEP状态。

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, 我们用来进行普通的更新，但是遇到问题了，此时不知道是哪个线程把这行记录给锁定了？

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

------------

来源:考试大-Linux认证考试

责编:lhn  评论 纠错