mysql InnoDB锁等待的查看以及分析

本篇内容主要讲解“mysql InnoDB锁等待的查看以及分析”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql InnoDB锁等待的查看以及分析”吧!在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_LOCKSINNODB_LOCK_WAITS以上这些表，其实只要知道其中比较常用的字段，就差不多能够满足日常的工作需求了，下面通过测试进行演示；一、准备工作1、在test下面随便创建一张表john，并取消自动commit操作，脚本如下：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>in免费主机域名sert into john select * from john;
Query OK, 671744 rows affected (2 min 19.03 sec)
Records: 671744 Duplicates: 0 Warnings: 0（经过几次插入后joh免费主机域名n表的数据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_TRXG;
*************************** 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_TRXG;
*************************** 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_WAITSG;
*************************** 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_LOCKSG;
*************************** 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管理更加便捷和容易了；到此，相信大家对“mysql InnoDB锁等待的查看以及分析”有了更深的了解，不妨来实际操作一番吧！这里是云技术网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: VBOX RAC 12C安装的常见问题有哪些

这篇文章主要为大家展示了“VBOX RAC 12C安装的常见问题有哪些”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“VBOX RAC 12C安装的常见问题有哪些”这篇文章吧。 vbox安装出现问题如果工具安装不成功…