首页 / MYSQL / mysql 行锁排查
mysql 行锁排查
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql 行锁排查，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10679字，纯文字阅读大概需要16分钟。
内容图文
<pre name="code" class="html">mysql 锁表:
隔离级别使用RR:
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ
SESSION A:
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 1 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.02 sec)
mysql> insert into test values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.00 sec)
mysql> show full processlist;
+----+------+-----------+--------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+-------+-----------------------+
| 1 | root | localhost | DEVOPS | Query | 0 | init | show full processlist |
| 2 | root | localhost | DEVOPS | Sleep | 8 | | NULL |
+----+------+-----------+--------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
mysql> update test set name='b' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show full processlist;
+----+------+-----------+--------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+-------+-----------------------+
| 1 | root | localhost | DEVOPS | Query | 0 | init | show full processlist |
| 2 | root | localhost | DEVOPS | Sleep | 34 | | NULL |
+----+------+-----------+--------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
SESSION B:
此时HANG：
mysql>mysql> update test set name='c100' where id=1;
SESSION B的信息:
mysql> show full processlist;
+----+------+-----------+--------+---------+------+----------+----------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+----------+----------------------------------------+
| 1 | root | localhost | DEVOPS | Query | 0 | init | show full processlist |
| 2 | root | localhost | DEVOPS | Query | 2 | updating | update test set name='c100' where id=1 |
+----+------+-----------+--------+---------+------+----------+----------------------------------------+
2 rows in set (0.00 sec)
此时SESION B也就是Id=2 被堵塞,一直在更新
模拟 Id=1获取锁:
mysql> show full processlist;
+----+------+----------------------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+------+---------+------+-------+-----------------------+
| 7 | root | 115.236.160.82:53073 | NULL | Sleep | 184 | | NULL |
| 8 | root | 115.236.160.82:53074 | NULL | Sleep | 184 | | NULL |
| 9 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+----------------------+------+---------+------+-------+-----------------------+
3 rows in set (0.00 sec)
mysql> select * from test;
+------+------+
| id | name |
+------+------+
| 1 | b |
| 2 | b |
| 2 | b |
+------+------+
3 rows in set (0.00 sec)
SELECT
r.trx_state wating_trx_state,
r.trx_id waiting_trx_id,
r.trx_mysql_thread_Id waiting_thread,
r.trx_query waiting_query,
b.trx_state blocking_trx_state,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
information_schema.innodb_lock_waits w
INNER JOIN
information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
# wating_trx_state, waiting_trx_id, waiting_thread, waiting_query, blocking_trx_state, blocking_trx_id, blocking_thread, blocking_query
'LOCK WAIT', '2332', '10', 'update test set name=\'aabbcc100\' where id=1', 'RUNNING', '2331', '9', NULL
mysql> desc information_schema.innodb_lock_waits
-> ;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |
| requested_lock_id | varchar(81) | NO | | | |
| blocking_trx_id | varchar(18) | NO | | | |
| blocking_lock_id | varchar(81) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
REQUESTING_TRX_ID ID of the requesting transaction. 请求事务的ID
REQUESTED_LOCK_ID ID of the lock for which a transaction is waiting. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.
事务在等待的lockid ,详细的信息可以关联 INNODB_LOCKS on LOCK_ID
BLOCKING_TRX_ID ID of the blocking transaction.
blocking 事务的 ID 持有者
BLOCKING_LOCK_ID ID of a lock held by a transaction blocking another transaction from proceeding. Details about the lock can be found by joining with INNODB_LOCKS on LOCK_ID.
一个事务持有的lock ID 阻塞其他事务处理
mysql> desc information_schema.innodb_trx
-> ;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |
| trx_state | varchar(13) | NO | | | |
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
| trx_requested_lock_id | varchar(81) | YES | | NULL | |
| trx_wait_started | datetime | YES | | NULL | |
| trx_weight | bigint(21) unsigned | NO | | 0 | |
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
| trx_query | varchar(1024) | YES | | NULL | |
| 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 | |
| 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 | |
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |
| trx_is_read_only | int(1) | NO | | 0 | |
| trx_autocommit_non_locking | int(1) | NO | | 0 | |
+----------------------------+---------------------+------+-----+---------------------+-------+
24 rows in set (0.00 sec)
TRX_ID 唯一的事务ID号, InnoDB内部使用.( 在MySQL 5.6开始,
那些IDs不是被创建用于事务的,是只读的和非堵塞的
TRX_WEIGHT 一个事务的权重, 反映(但是不是必须的准确的计数) 被改变的行和被事务锁定的行。
为了解决死锁, InnoDB 选择最小权重的事务作为 victim来回滚。
事务 改变非事务表是被认为比其他权重高, 不管更改的数目和锁住的行数
TRX_STATE 事务执行状态, RUNNING,LOCK WAIT, 回滚或者提交
TRX_STARTED Transaction start time. 事务开始时间
TRX_REQUESTED_LOCK_ID LOCK 事务ID 是当前等待(如果 TRX_STATE 是lock,否则为空)
TRX_WAIT_STARTED 当交易开始等待lock(如果TRX_STATE 是LOCK WAIT,否则NULL)
TRX_MYSQL_THREAD_ID
MySQL thread ID,可以用于关联PROCESSLIST 的ID
mysql> show full processlist;
+----+------+-----------+--------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------+---------+------+-------+-----------------------+
| 1 | root | localhost | DEVOPS | Sleep | 6 | | NULL |
| 2 | root | localhost | DEVOPS | Query | 0 | init | show full processlist |
+----+------+-----------+--------+---------+------+-------+-----------------------+
见章节 14.13.2.3.1 ,潜在的不一致的PROCESSLIST数据
TRX_QUERY 通过事务执行的SQL
TRX_OPERATION_STATE 事务的当前操作,或者为NULL
TRX_TABLES_IN_USE Innodb表当前被使用来处理当前事务的SQL的表的数量
TRX_TABLES_LOCKED InnoDB表当前SQL语句有行锁的表的数量。
因为这些是row locks, 不是表锁,表仍旧可以被读取和被多个事务写,尽管有些行被锁定。
TRX_LOCK_STRUCT 事务保留的锁的数目
TRX_LOCK_MEMORY_BYTES 这个事务的lock 结构在内存里占用的大小
TRX_ROWS_LOCKED 这个事务的锁定的大概记录的行数,
该值可能包括标记为删除的记录 物理上是存在的 但是对于事务不可见
TRX_ROWS_MODIFIED 在该事务中修改和插入的数目
TRX_CONCURRENCY_TICKETS 这个值表明 当前事务可以做多少事情在被交换出去之前,
通过 innodb_concurrency_tickets 选择指定
TRX_ISOLATION_LEVEL 当前事务的隔离级别
TRX_UNIQUE_CHECKS
是否 unique checks 被启用或者关闭 对于当前的事务, 它们可以在一个批量数据加载时关闭
mysql 行锁排查
标签：
本文系统来源：http://blog.csdn.net/zhaoyangjian724/article/details/51372595
内容总结
以上是互联网集市为您收集整理的mysql 行锁排查全部内容，希望文章能够帮你解决mysql 行锁排查所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】