mysql 查看死锁和去除死锁
1、查询进程
show processlist
2、 查询到相对应的进程,然后 kill id
验证(kill后再看是否还有锁)
2、查询是否锁表
show OPEN TABLES where In_use > 0;
示例:
新建一个会话执行如下的显示锁示例
LOCK TABLES account_data.account READ;
SELECT SLEEP(160);
UNLOCK TABLES account_data.account;
另开启一个会话检查锁表情况:
mysql> show OPEN TABLES where In_use > 0;
+--------------+---------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------+---------+--------+-------------+
| account_data | account | 1 | 0 |
+--------------+---------+--------+-------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.innodb_locks\G;
Empty set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> show processlist\G;
1. row
Id: 5
User: root
Host: 192.168.0.206:64294
db: NULL
Command: Sleep
Time: 4051
State:
Info: NULL
2. row
Id: 8
User: root
Host: 192.168.0.206:64297
db: NULL
Command: Sleep
Time: 4042
State:
Info: NULL
3. row
Id: 10
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
4. row
Id: 19
User: root
Host: 192.168.0.206:54603
db: account_data
Command: Sleep
Time: 245
State:
Info: NULL
5. row
Id: 20
User: root
Host: 192.168.0.206:54604
db: information_schema
Command: Query
Time: 20
State: User sleep
Info: select sleep(160)
5 rows in set (0.00 sec)
ERROR:
No query specified
mysql>
3、在5.5中,information_schema 库中增加了三个关于锁的表(innoDB引擎):
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系
先来看一下这三张表结构:
root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id | varchar(81) | NO | | | |#锁ID
| lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
| lock_mode | varchar(32) | NO | | | |#锁模式
| lock_type | varchar(32) | NO | | | |#锁类型
| lock_table | varchar(1024) | NO | | | |#被锁的表
| lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
| lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
| lock_data | varchar(8192) | YES | | NULL | |#被锁的数据
+————-+———————+——+—–+———+——-+
10 rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
| blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
| blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
+——————-+————-+——+—–+———+——-+
4 rows in set (0.00 sec)
root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态:
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事务开始时间;
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL | |#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事务锁住的内存大小(B)
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查