mysql死锁-查询锁表进程-分析锁表原因
来源:互联网 发布:中国工业发展现状 知乎 编辑:程序博客网 时间:2024/06/06 02:27
查询锁表进程:
1、查询是否锁表
show OPEN TABLES where In_use > 0;2、查询进程
show processlist
查询到相对应的进程===然后 kill id
补充:
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
分析锁表原因:
参考:http://blog.itpub.net/12679300/viewspace-1420031/
在InnoDB Plugin之前,一般通过show full processlist和show engine innodb status命令查看当前的数据库请求,然后再判断当前事务中锁的情况。随着MySQL的发展,已经提供更加便捷的方法来监控数据库中的锁等待现象了。
在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS,通过这三张表,可以更简单地监控当前的事务并分析可能存在的问题。
- INNODB_TRX表及结构
比较常用的列:
trx_id:InnoDB存储引擎内部唯一的事物ID
trx_status:当前事务的状态
trx_status:事务的开始时间
trx_requested_lock_id:等待事务的锁ID
trx_wait_started:事务等待的开始时间
trx_weight:事务的权重,反应一个事务修改和锁定的行数,当发现死锁需要回滚时,权重越小的值被回滚
trx_mysql_thread_id:MySQL中的进程ID,与show processlist中的ID值相对应
trx_query:事务运行的SQL语句
- INNODB_LOCKS
- INNODB_LOCK_WAITS
以上这些表,其实只要知道其中比较常用的字段,就差不多能够满足日常的工作需求了,下面通过测试进行演示;
一、准备工作
1、在test下面随便创建一张表john,并取消自动commit操作,脚本如下:
mysql> use information_schemaDatabase changed
mysql> select count(*) from tables;
+----------+
| count(*) |
+----------+
| 81 |
+----------+
1 row in set (0.06 sec)
mysql> create table test.john as select * from tables;
Query OK, 82 rows affected (0.29 sec)
Records: 82 Duplicates: 0 Warnings: 0
mysql> insert into john select * from john;
Query OK, 671744 rows affected (2 min 19.03 sec)
Records: 671744 Duplicates: 0 Warnings: 0
(经过几次插入后john表的数据671744行)
mysql> set @@autocommit=0;
Query OK, 0 rows affected (0.00 sec)
(取消数据库的自动commit)
二、进行表john加锁操作,脚本如下:
mysql> select count(*) from john for update;
+----------+
| count(*) |
+----------+
| 2686976 |
+----------+
1 row in set (8.19 sec)
在另外一个窗口中监控innodb锁的状态;
mysql> SELECT * FROM INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: B14 /请记住该trx_id/
trx_state: RUNNING /当前状态/
trx_started: 2014-11-29 14:07:51
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 15905
trx_mysql_thread_id: 10 /在process 里面的id值/
trx_query: select count(*) from john for update; /当前执行的语句/
trx_operation_state: fetching rows
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 15905
trx_lock_memory_bytes: 1554872
trx_rows_locked: 1360743
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
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
1 row in set (0.02 sec)
trx_id: B14 只是持有锁,但并没有产生锁等待;
三、模拟锁等待
3.1 在另外一个窗口中,执行语句:
3.2 查看当前锁等待的情况
INNODB_TRX的锁情况:
mysql> SELECT * FROM INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: B15
trx_state: LOCK WAIT //状态为锁等待//
trx_started: 2014-11-29 14:12:28
trx_requested_lock_id: B15:0:32777:2
trx_wait_started: 2014-11-29 14:12:28
trx_weight: 2
trx_mysql_thread_id: 10 //在process里面可以看到相应的状态//
trx_query: select count(*) from john where table_name='CHARACTER_SETS' for update //锁等待的语句//
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: REPEATABLE READ
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
*************************** 2. row ***************************
trx_id: B14
trx_state: RUNNING
trx_started: 2014-11-29 14:07:51
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 31777
trx_mysql_thread_id: 8
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 31777
trx_lock_memory_bytes: 3094968
trx_rows_locked: 2718752
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
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
2 rows in set (0.02 sec)
请注意:因为我们只有模拟两个session,所以这边只有两个会话。(因此一个处于锁等待,另外一个必然就是持有锁的对象。实际的生产环境中可能这边会出现很多列,所以需要用下面的语句才能判断:锁等待和持有锁对象的匹配关系)
3.3 锁等待和持有锁的相互关系
mysql> SELECT * FROM INNODB_LOCK_WAITS\G;
*************************** 1. row ***************************
requesting_trx_id: B15
requested_lock_id: B15:0:32777:2
blocking_trx_id: B14
blocking_lock_id: B14:0:32777:2
1 row in set (0.03 sec)
ERROR:
No query specified
通过视图INNODB_LOCK_WAITS可以清晰的看到B14持有锁,而B15处于锁等待;
3.4 锁等待的原因
mysql> SELECT * FROM INNODB_LOCKS\G;
*************************** 1. row ***************************
lock_id: B15:0:32777:2
lock_trx_id: B15
lock_mode: X
lock_type: RECORD
lock_table: `test`.`john`
lock_index: `GEN_CLUST_INDEX`
lock_space: 0
lock_page: 32777
lock_rec: 2
lock_data: 0x000000640000
*************************** 2. row ***************************
lock_id: B14:0:32777:2
lock_trx_id: B14
lock_mode: X
lock_type: RECORD
lock_table: `test`.`john`
lock_index: `GEN_CLUST_INDEX`
lock_space: 0
lock_page: 32777
lock_rec: 2
lock_data: 0x000000640000
2 rows in set (0.01 sec)
可以看到持有锁的模式、对象
3.5 在进程里面查看状态
Id值为8的进程,Info显示为NULL值,可以推断当前的session由于未进行commit导致锁未释放的;
总结:通过以上几个视图,就可以很快速的判断出锁等待的对象及原因了,从这上面也可以看出mysql管理更加便捷和容易了;
innodb_lock_wait_timeout的参数说明
innodb_lock_wait_timeout指的是事务等待获取资源等待的最长时间,超过这个时间还未分配到资源则会返回应用失败;
参数的时间单位是秒,最小可设置为1s(一般不会设置得这么小),最大可设置1073741824秒(34年,一条语句锁等待超过30分钟估计业务该有反馈了)
默认安装时这个值是50s(公司的默认参数设置)
二、参数的修改
参数支持范围为Session和Global,且支持动态修改,所以可以通过两种方法修改;
2.1 通过语句修改
set innodb_lock_wait_timeout=100;
set global innodb_lock_wait_timeout=100;
注意global的修改对当前线程是不生效的,只有建立新的连接才生效
2.2 修改参数文件/etc/my.cnf
innodb_lock_wait_timeout = 50
三、当等待超过阀值时的情况
当锁等待超过设置时间的时候,就会报如下的错误;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- mysql死锁-查询锁表进程-分析锁表原因
- mysql死锁-查询锁表进程-分析锁表原因
- mysql 查询表死锁
- mysql查询表死锁和结束死锁
- 锁表原因查询
- mysql 查询表死锁 和结束死锁的表步骤
- mysql 查询表死锁 和结束死锁的表步骤
- mysql 查询表死锁 和结束死锁的表步骤
- mysql 查询表死锁 和结束死锁的表步骤
- mysql 查询表死锁 和结束死锁的表步骤
- mysql 查询表死锁 和结束死锁的表步骤
- mysql 查询表死锁 和结束死锁的表步骤
- mysql 查询表死锁 和结束死锁的表步骤
- 查询mysql那些表出于死锁状态
- 进程死锁的原因
- 造成数据库表死锁的原因分析及解决方案
- biztalk 2009 原因: 事务(进程 ID 128)与另一个进程被死锁在 锁 资源上,并且已被选作死锁牺牲品
- oracle死锁原因分析
- xcode 与 码云git
- oracle11g更改SID-DBname
- Java是如何读取和写入浏览器Cookies的
- Java发展史
- Android-多列表的项目(Rxjava+Rtrofit+Recyclerview+Glide+Adapter封装)之(二)网络层的封装
- mysql死锁-查询锁表进程-分析锁表原因
- 天线的基本参数介绍
- AS2.2.3新建工程出现的问题(直接贴了代码,耐心比对不同之处)
- 交换机开发(一)—— 交换机的工作原理
- 线性表的顺序存储
- 455. Assign Cookies
- POJ 2411 Mondriaan's Dream [状压DP做法]
- HDOJ 1005 Java 答案
- 深度学习caffe实战(一)验证码识别