锐客网

  1. 首页 > 生活百科 > it技术 > >

怎么查mysql线程 mysql查看线程

IT技术语句

mysql如何查询SQL中哪些语句执行最占用CPU？mysql -u root -p之后，输入show full processlist; 可以看到正在执行的语句
然后通过EXPLAIN分析SQL语句
如何 查找 mysql 中如何 kill 引起死锁的线程id如果遇到死锁了 ， 怎么解决呢?找到原始的锁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: 1Changed: 1Warnings: 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
------------
Trx id counter 189327
Purge done for trx's n:o189323 undo n:o0 state: running but idle
History list length 343
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 11, OS thread handle 0x7f70a0c98700, query id 994 localhost root init
show engine innodb status
---TRANSACTION 189326, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)

推荐阅读


上一篇：佳能相机如何导出照片ios设置，佳能数码相机如何导出照片

下一篇：ppt如何变成图片，如何将ppt变为图片