MYSQL 表锁情况查看

查看锁表情况

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张表说明：

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（可根据自己的分析习惯适进行调整）

原文出处：waitig -> https://www.waitig.com/mysql-%E8%A1%A8%E9%94%81%E6%83%85%E5%86%B5%E6%9F%A5%E7%9C%8B.html