MYSQL 表锁情况查看

原创 2017年10月13日 16:15:30

查看锁表情况

mysql> show status like ‘Table%’;
+—————————-+——–+
| Variable_name | Value |
+—————————-+——–+
| Table_locks_immediate | 795505 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 0 |
| Table_open_cache_misses | 0 |
| Table_open_cache_overflows | 0 |
+—————————-+——–+
5 rows in set (0.00 sec)

Table_locks_immediate 指的是能够立即获得表级锁的次数
Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数,如果数量大,说明锁等待多,有锁争用情况

查看正在被锁定的的表
show OPEN TABLES where In_use > 0;

mysql> show OPEN TABLES where In_use > 0;
+————–+—————+——–+————-+
| Database | Table | In_use | Name_locked |
+————–+—————+——–+————-+
| music | class_record | 1 | 0 |
| vipswoole | chat_message | 3 | 0 |
| music | user_account | 1 | 0 |
| music | sales_channel | 1 | 0 |
| music | class_room | 5 | 0 |
| music | user | 1 | 0 |
| music_school | user | 1 | 0 |
+————–+—————+——–+————-+
7 rows in set (0.00 sec)

mysql>

如果查看到锁争用情况严重,可以再查看当前执行的SQL :
mysql>show processlist

(mysqladmin -uroot -p -P 3306 processlist)

mysqladmin命令有一个debug参数,可以分析当前MySQL服务的状态信息,同时也可以用来帮助我们定位当前锁的详细情况,这里我们通过该命令分析一下当前MySQL服务的详细状态,执行mysqladmin命令如下:

[root@phpmysql02 data]# mysqladmin -ujss -p -S /data/3306/mysql.sock debug

Enter password:
debug会将状态信息生成到mysql的错误文件,一般锁的信息都会保存在最后几行,这里我们在操作系统层error log最后几行:

[root@phpmysql02 data]# tail -10 phpmysql02.err

Thread database.table_name Locked/Waiting Lock_type
2 hdpic.t_wiki_zutu Waiting - write Highest priority write lock
123890 hdpic.t_wiki_zutu_category Locked - read Low priority read lock
123890 hdpic.t_wiki_zutu_photo Locked - read Low priority read lock
123890 hdpic.t_wiki_zutu Locked - read Low priority read lock
124906 hdpic.t_wiki_zutu Waiting - read Low priority read lock
从上述信息可以看出,123890持有的读锁阻塞了2的写入和124906的读操作,这个状态符合我们的推论,接下来处理就比较单纯了,如果现状不可接受,不能继续等待,将123890杀掉,释放资源即可:

mysql> kill 123890;

Query OK, 0 rows affected (0.00 sec)
再次执行show processlist查看:

使用系统表进行锁查询:

select r.trx_isolation_level, r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_trx_thread,
r.trx_state waiting_trx_state,lr.lock_mode waiting_trx_lock_mode,lr.lock_type waiting_trx_lock_type,
lr.lock_table waiting_trx_lock_table,lr.lock_index waiting_trx_lock_index,r.trx_query waiting_trx_query,
b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_trx_thread,b.trx_state blocking_trx_state,
lb.lock_mode blocking_trx_lock_mode,lb.lock_type blocking_trx_lock_type,lb.lock_table blocking_trx_lock_table,
lb.lock_index blocking_trx_lock_index,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
inner join information_schema.innodb_locks lb on lb.lock_trx_id=w.blocking_trx_id
inner join information_schema.innodb_locks lr on lr.lock_trx_id=w.requesting_trx_id \G


涉及的3张表说明:

  1. information_shcema下的三张表(通过这三张表可以更新监控当前事物并且分析存在的锁问题)
    —— innodb_trx ( 打印innodb内核中的当前活跃(ACTIVE)事务)
    —— innodb_locks ( 打印当前状态产生的innodb锁 仅在有锁等待时打印)
    —— innodb_lock_waits (打印当前状态产生的innodb锁等待 仅在有锁等待时打印)

    1) innodb_trx表结构说明 (摘取最能说明问题的8个字段)
    字段名 说明
    trx_id innodb存储引擎内部唯一的事物ID
    trx_state
    当前事物状态(running和lock wait两种状态)
    trx_started
    事物的开始时间
    trx_requested_lock_id 等待事物的锁ID,如trx_state的状态为Lock wait,那么该值带表当前事物等待之前事物占用资源的ID,若trx_state不是Lock wait 则该值为NULL
    trx_wait_started 事物等待的开始时间
    trx_weight 事物的权重,在innodb存储引擎中,当发生死锁需要回滚的时,innodb存储引擎会选择该值最小的进行回滚
    trx_mysql_thread_id mysql中的线程id, 即show processlist显示的结果
    trx_query 事物运行的SQL语句
    2)innodb_locks表结构说明

字段名 说明
lock_id 锁的ID
lock_trx_id 事物的ID
lock_mode 锁的模式(S锁与X锁两种模式)
lock_type 锁的类型 表锁还是行锁(RECORD)
lock_table 要加锁的表
lock_index 锁住的索引
lock_space 锁住对象的space id
lock_page 事物锁定页的数量,若是表锁则该值为NULL
lock_rec 事物锁定行的数量,若是表锁则该值为NULL
lock_data 事物锁定记录主键值,若是表锁则该值为NULL(此选项不可信)
3)innodb_lock_waits表结构说明
字段名 说明
requesting_trx_id申请锁资源的事物ID
requested_lock_id申请的锁的ID
blocking_trx_id阻塞其他事物的事物ID
blocking_lock_id阻塞其他锁的锁ID

可以根据这三张表进行联合查询,得到更直观更清晰的结果,可以参考如下SQL(可根据自己的分析习惯适进行调整)

版权声明:本文为博主原创文章,未经博主允许不得转载。

mysql死锁-查询锁表进程-分析锁表原因

查询锁表进程: 1、查询是否锁表 show OPEN TABLES where In_use > 0;   2、查询进程     show processlist   查询到相对应的进程===然后 k...
  • truelove12358
  • truelove12358
  • 2016年12月12日 19:14
  • 3801

MySQL锁定状态查看命令

1 show processlist; SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限,...
  • dc_726
  • dc_726
  • 2013年02月07日 20:33
  • 136750

MySQL锁定状态查看命令

1 show processlist; SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限,您...
  • tswisdom
  • tswisdom
  • 2015年04月08日 12:24
  • 4229

mysql查看事务和锁情况

MySQL 5.5 – innodb_lock_wait 锁 等待 在5.5中,information_schema 库中增加了三个关于锁的表(MEMORY引擎) innodb_t...
  • linlemo
  • linlemo
  • 2016年08月11日 17:46
  • 272

mysql 区间锁 对于没有索引 非唯一索引 唯一索引 各种情况

The locks are normally next-key locks that also block inserts into the “gap” immediately before the ...
  • zhaoyangjian724
  • zhaoyangjian724
  • 2016年11月25日 10:36
  • 460

Mysql Innodb死锁情况分析与归纳

案例描述       在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。 两个sql语句如下:       (1)i...
  • educast
  • educast
  • 2014年03月13日 09:52
  • 610

MySQL insert into ... select 的锁情况 【转】

文章来源:关于MySQL insert into ... select 的锁情况 摘要:       一直以为"insert into tb select * from tbx" 这样的导入操...
  • woshizhangliang999
  • woshizhangliang999
  • 2016年12月16日 15:37
  • 2642

mysql锁研究系列四(事务在并发情况下避免超卖)

我们在做电商的时候会考虑到一个问题,如果此时库存就剩一个,而来了两个并发同时下单,如何避免库存超卖? 先贴一段代码: beginTranse(开启事务) $sql = "select ...
  • qq_21267705
  • qq_21267705
  • 2015年08月24日 19:14
  • 1339

Mysql Innodb死锁情况分析与归纳

案例描述       在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,MySQL会检测出死锁,并打印出日志。       两个sql语句如下:     ...
  • xiaoliuliu2050
  • xiaoliuliu2050
  • 2017年03月31日 16:34
  • 148

Mysql Innodb死锁情况分析与归纳

案例描述       在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。       两个sql语句如下:       (...
  • jiangguilong2000
  • jiangguilong2000
  • 2013年09月30日 16:53
  • 3618
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MYSQL 表锁情况查看
举报原因:
原因补充:

(最多只允许输入30个字)