MySQL 查看事务和锁情况的常用语句分享

一些查看数据库中事务和锁情况的常用语句

查看事务等待状况:

SELECT
   r.trx_id waiting_trx_id,
   r.trx_mysql_thread_id waiting_thread,
   r.trx_query waiting_query,
   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;

查看更具体的事务等待状况:

SELECT
   b.trx_state,
   e.state,
   e.time,
   d.state AS block_state,
   d.time AS block_time,
   a.requesting_trx_id,
   a.requested_lock_id,
   b.trx_query,
   b.trx_mysql_thread_id,
   a.blocking_trx_id,
   a.blocking_lock_id,
   c.trx_query AS block_trx_query,
   c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM
   information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY
   a.requesting_trx_id;

查看未关闭的事务:

–MySQL 5.6

SELECT
   a.trx_id,
   a.trx_state,
   a.trx_started,
   a.trx_query,
   b.ID,
   b.USER,
   b.DB,
   b.COMMAND,
   b.TIME,
   b.STATE,
   b.INFO,
   c.PROCESSLIST_USER,
   c.PROCESSLIST_HOST,
   c.PROCESSLIST_DB,
   d.SQL_TEXT
FROM
   information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;

–MySQL 5.5

SELECT
   a.trx_id,
   a.trx_state,
   a.trx_started,
   a.trx_query,
   b.ID,
   b. USER,
   b. HOST,
   b.DB,
   b.COMMAND,
   b.TIME,
   b.STATE,
   b.INFO
FROM
   information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
WHERE
   b.COMMAND = 'Sleep';

查看某段时间以来未关闭事务:

SELECT
    trx_id,
    trx_started,
    trx_mysql_thread_id
FROM
    INFORMATION_SCHEMA.INNODB_TRX
WHERE
    trx_started < date_sub(now(), INTERVAL 1 MINUTE)
AND trx_operation_state IS NULL
AND trx_query IS NULL;

补充:MySQL锁定状态查看命令

1 show processlist;

SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程(也就是,与您正在使用的MySQL账户相关的线程)。如果有线程在update或者insert 某个表,此时进程的status为updating 或者 sending data。

如果您得到“too many connections”错误信息,并且想要了解正在发生的情况,本语句是非常有用的。MySQL保留一个额外的连接,让拥有SUPER权限的账户使用,以确保管理员能够随时连接和检查系统(假设您没有把此权限给予所有的用户)。


Status


含义


Checking table


正在检查数据表(这是自动的)。


Closing tables


正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。


Connect Out


复制从服务器正在连接主服务器。


Copying to tmp table on disk


由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。


Creating tmp table


正在创建临时表以存放部分查询结果。


deleting from main table


服务器正在执行多表删除中的第一部分,刚删除第一个表。


deleting from reference tables


服务器正在执行多表删除中的第二部分,正在删除其他表的记录。


Flushing tables


正在执行FLUSH TABLES,等待其他线程关闭数据表。


Killed


发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。


Locked


被其他查询锁住了。


Sending data


正在处理SELECT查询的记录,同时正在把结果发送给客户端。


Sorting for group


正在为GROUP BY做排序。


Sorting for order


正在为ORDER BY做排序。


Opening tables


这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。


Removing duplicates


正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。


Reopen table


获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。


Repair by sorting


修复指令正在排序以创建索引。


Repair with keycache


修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。


Searching rows for update


正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。


Sleeping


正在等待客户端发送新请求。


System lock


正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加--skip-external-locking参数来禁止外部系统锁。


Upgrading lock


INSERT DELAYED正在尝试取得一个锁表以插入新记录。


Updating


正在搜索匹配的记录,并且修改它们。


User Lock


正在等待GET_LOCK()。


Waiting for tables


该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。


waiting for handler insert


INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。

大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。还有其他的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。

2 show full processlist;

show processlist;只列出前100条,如果想全列出请使用show full processlist;

3 show open tables;

这条命令能够查看当前有那些表是打开的。In_use列表示有多少线程正在使用某张表,Name_locked表示表名是否被锁,这一般发生在Drop或Rename命令操作这张表时。所以这条命令不能帮助解答我们常见的问题:当前某张表是否有死锁,谁拥有表上的这个锁等。

show open tables from database;

4 show status like ‘%lock%'

查看服务器状态。

5 show engine innodb status\G;

MySQL 5.1之前的命令是:show innodbstatus\G;,MySQL 5.5使用上面命令即可查看innodb引擎的运行时信息。

6 show variables like ‘%timeout%';

查看服务器配置参数。

以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。如有错误或未考虑完全的地方,望不吝赐教。

(0)

相关推荐

  • MySql 索引、锁、事务知识点小结

    本文总结了MySql 索引.锁.事务知识点.分享给大家供大家参考,具体如下: 1. 索引 索引,类似书籍的目录,可以根据目录的某个页码立即找到对应的记录. 索引的优点: 天生排序. 快速查找. 索引的缺点: 占用空间. 降低更新表的速度. 注意点:小表使用全表扫描更快,中大表才使用索引.超级大表索引基本无效. 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引. 详细说说 6 种索引

  • MySql 知识点之事务、索引、锁原理与用法解析

    本文实例讲述了MySql 知识点之事务.索引.锁原理与用法.分享给大家供大家参考,具体如下: 事务 事务概念 事务就是一组原子性的SQL查询,或者说一个独立的工作单元.如果数据库引擎执行一组操作语句,那么久执行所有的操作,如果其中有任何一条崩溃或其他原因无法执行,所有语句将不会执行.也就是说事务内的语句,要么全部执行成功,要么全部执行失败. 事务特性ACID 原子性(atomicity) 一个事务被视为最小工作单元,不可拆分,整个事务所有的操作要么全部提交成功,要么全部失败回滚,不可只执行部分.

  • 浅析MySQL 锁和事务

    MySQL本身也是在文件系统的基础上发展而来,因为锁的存在使之有所不同. MySQL作为一种数据库软件,难免会存在对其共享资源的并发访问,为了协调和管理不同资源的并发访问,也就产生了锁机制,因为锁机制的存在为数据库提供了数据的完整性和一致性. 从锁的级别来分锁可分为:行级锁.表级锁.页级锁. 从锁的类型来分锁可分为:共享锁.排它锁(独占锁). 为了协调行锁.表锁产生了:意向锁(表级锁). 共享锁,允许事务去读取数据. 排它锁,允许事务去修改或删除数据. 意向锁,获取行级锁的时候,自动添加的表级锁

  • MySQL数据库事务与锁深入分析

    一.基本概念 事务是指满足ACID特性的的一组操作,可以通过Commit提交事务,也可以也可以通过Rollback进行回滚.会存在中间态和一致性状态(也是真正在数据库表中存在的状态) 二.ACID Atomicity[原子性]:事务被视为不可分割的最小单元,事务的所有操作要么全部提交成功,要么全部失败回滚.回滚可以用回滚日志(undo Log)来实现,回滚日志记录着事务所执行的修改操作,在回滚时反向执行这些修改操作即可 undoLog:为了满足事务的原子性,在操作任何数据之前,首先将数据备份到U

  • Mysql事务中Update是否会锁表?

    两种情况: 1.带索引 2.不带索引 前提介绍: 方式:采用命令行的方式来模拟 1.mysq由于默认是开启自动提交事务,所以首先得查看自己当前的数据库是否开启了自动提交事务. 命令:select @@autocommit; 结果如下: +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 如果是1,那么运行命令:set autocommit = 0;设置为不开启自动提交 2.当前的数据库表格式如下 tb

  • mysql的事务,隔离级别和锁用法实例分析

    本文实例讲述了mysql的事务,隔离级别和锁用法.分享给大家供大家参考,具体如下: 事务就是一组一起成功或一起失败的sql语句.事务还应该具备,原子性,一致性,隔离性和持久性. 一.事务的基本要素 (ACID) 1.原子性:事务开始后,所有的操作,要么全部成功,要么全部失败,不可能处于中间状态,事务是一个不可分割的整体,就像原子一样. 2.一致性:事务开始前和结束后,数据库的完整性约束没有破坏,A向B转账,A扣了钱,但B却没到账. 3.隔离性:同时发生的事务(并发事务)不应该导致数据库处于不一致

  • MySQL 查看事务和锁情况的常用语句分享

    一些查看数据库中事务和锁情况的常用语句 查看事务等待状况: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_

  • MySQL查看表和清空表的常用命令总结

    查看MySQL数据库表 进入MySQL Command line client下 查看当前使用的数据库: mysql>select database(); mysql>status; mysql>show tables; mysql>show databases;//可以查看有哪些数据库,返回数据库名(databaseName) mysql>use databaseName; //更换当前使用的数据库 mysql>show tables; //返回当前数据库下的所有表的

  • mysql锁表和解锁语句分享

    页级的典型代表引擎为BDB. 表级的典型代表引擎为MyISAM,MEMORY以及很久以前的ISAM. 行级的典型代表引擎为INNODB. -我们实际应用中用的最多的就是行锁. 行级锁的优点如下: 1).当很多连接分别进行不同的查询时减小LOCK状态. 2).如果出现异常,可以减少数据的丢失.因为一次可以只回滚一行或者几行少量的数据. 行级锁的缺点如下: 1).比页级锁和表级锁要占用更多的内存. 2).进行查询时比页级锁和表级锁需要的I/O要多,所以我们经常把行级锁用在写操作而不是读操作. 3).

  • python中使用psutil查看内存占用的情况

    有的时候需要对python程序内存占用进行监控,这个时候可以用到psutil库,Anaconda中是自带的,如果import出错,可以用pip install psutil(安装在python中)或conda install psutil(安装在Anaconda中) #常用的: import psutil import os info = psutil.virtual_memory() print u'内存使用:',psutil.Process(os.getpid()).memory_info(

  • MySQL查看数据库表容量大小的方法示例

    本文介绍MySQL查看数据库表容量大小的命令语句,提供完整查询语句及实例,方便大家学习使用. 1.查看所有数据库容量大小 select table_schema as '数据库', sum(table_rows) as '记录数', sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)', sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema

  • MySQL数据库表被锁、解锁以及删除事务详解

    目录 背景 故障追踪 解决方案 第一步:查看表使用 第二步:查看进程 第三步:查看当前运行的所有事务 第四步:查看当前出现的锁 第五步:查询锁等待的对应关系 第六步:kill掉事务 MySQL的锁 MySQL锁表场景 Waiting for table metadata lock 场景一:长事务运行,阻塞DDL,继而阻塞所有同表的后续操作. 场景二:为提交事务,阻塞DDL,继而阻塞所有同表的后续操作. 场景三:显式事务失败操作获得锁,未释放 小结 总结 背景 在程序员的职业生涯中,总会遇到数据库

  • MySQL事务与锁实例教程详解

    目录 MySQL事务和锁 事务 事务的控制语句 事务隔离级别设置 脏读 不可重复读 幻读 锁机制 InnoDB的行级锁 锁实战 死锁 总结 MySQL事务和锁 事务 说到关系型的数据库的事务,相信大家对四大特性都不陌生,分别是原子性.一致性.隔离性.持久性,简称为ACID特性. MySQL中支持3种不同的存储引擎: MyISAM存储引擎.Memory存储引擎.和InnoDB存储引擎 注:只有InnoDB才支持事务. 事务的控制语句 控制语句 作用 begin或者start transaction

  • MySQL查看锁的实现代码

    目录 MySQL查看锁的sql 开启锁监控 其他监控 MySQL查看锁的sql 查看数据库状态 # 会显示加锁的信息等等 show engine innodb status; 查看正在执行的线程信息 show full processlist; 查看正在锁的表 show open tables where in_use > 0; show open tables; 查看锁的类型.状态 show status like '%lock%'; 5.0后,增加了3个关于锁的表 MySQL5.7版本 IN

随机推荐