Mysql Innodb锁分析
来源:互联网 发布:dijkstra算法求解过程 编辑:程序博客网 时间:2024/05/17 04:08
说明:前面已经了解了InnoDB关于在出现锁等待的时候,会根据参数innodb_lock_wait_timeout的配置,判断是否需要进行timeout的操作,本文档介绍在出现锁等待时候的查看及分析处理;
在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锁分析
- Mysql innodb 锁分析
- Mysql 锁问题分析 InnoDB 锁
- mysql innodb的锁机制分析
- mysql myisan和innodb分析
- mysql innodb master thread分析
- mysql innodb死锁情况分析
- mysql innodb死锁情况分析
- mysql innodb 数据安全性分析
- mysql-innodb与myisam分析
- mysql InnoDB锁等待的查看及分析
- mysql InnoDB锁等待的查看及分析
- Mysql框架分析和Innodb分析
- [MySQL] InnoDB锁问题
- MYSQL之Innodb锁
- Mysql InnoDB锁
- MySQL InnoDB 锁机制
- Mysql innodb 间隙锁
- A bite of Python
- MBProgressHUD
- Python数字图像处理
- 后缀表达式计算——栈的应用
- 第一行代码系列第二章——向下一个活动传递数据
- Mysql Innodb锁分析
- 游戏菜单级联
- 线性代数复习 第六章 二次型
- 1117. Eddington Number(25)-PAT甲级真题
- *[Lintcode] Decode Ways
- VPS主机IP被墙怎么办 导致IP被墙的原因
- 244.1
- 基于tmpfs使用mongoDB
- maven的pom文件报错: must be "pom" but is "jar"