mysql查询表是否被锁的方法_Mysql

来源:脚本之家  责任编辑:小易  

可直接在mysql命令行执2113行:show engine innodb status\G;查看造成死锁的sql语句,分析索5261引情况,然后优化4102sql然后show processlist;另外可1653以打开慢查询日志,linux下打开需在my.cnf的[mysqld]里面加上以下内容:本回答被提问者采纳,当你开始执行2113一个 ALTER ,而你遇到了可怕的5261“元数据锁定等待”,我敢肯4102定你一定遇见1653过。我最近遇到了一个案例,其中被更改的表要执行一个很小范围的更新(<100行)。ALTER 在负载测试期间一直等待了几个小时。在停止负载测试后,ALTER 按预期在不到一秒的时间内就完成了。那么这里发生了什么?检查外键每当有奇数次锁定时,我的第一直觉就是检查外键。当然这张表有一些外键引用了一个更繁忙的表。但是这种行为似乎仍然很奇怪。对表运行 ALTER 时,会针对子表请求一个 SHARED_UPGRADEABLE 元数据锁。还有针对父级的 SHARED_READ_ONLY 元数据锁。我们来看看如何根据文档获取元数据锁定[1]:如果给定锁定有多个服务器,则首先满足最高优先级锁定请求,并且与 max_write_lock_count系统变量有关。写锁定请求的优先级高于读取锁定请求。[1]:https://dev.mysql.com/doc/refman/en/metadata-locking.html请务必注意锁定顺序是序列化的:语句逐个获取元数据锁,而不是同时获取,并在此过程中执行死锁检测。通常在考虑队列时考虑先进先出。如果我发出以下三个语句(按此顺序),它们将按以下顺序完成:1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent但是当子 ALTER 语句请求对父进行读取锁定时,尽管排序,但两个插入将在 ALTER 之前完成。以下是可以演示此示例的示例场景:数据初始化:CREATE TABLE `parent` (`id` int(11) NOT NULL AUTO_INCREMENT,`val` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB;CREATE TABLE `child` (`id` int(11) NOT NULL AUTO_INCREMENT,`parent_id` int(11) DEFAULT NULL,`val` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_parent` (`parent_id`),CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE=InnoDB;INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");Session 1:start transaction;update parent set val = "four-new" where id = 4;Session 2:alter table child add index `idx_new` (val);Session 3:start transaction;update parent set val = "three-new" where id = 3;此时,会话 1 具有打开的事务,并且处于休眠状态,并在父级上授予写入元数据锁定。 会话 2 具有在子级上授予的可升级(写入)锁定,并且正在等待父级的读取锁定。最后会话 3 具有针对父级的授权写入锁定:mysql> select * from performance_schema.metadata_locks;+-------------+-------------+-------------------+---------------+-------------+| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE         | LOCK_DURATION | LOCK_STATUS |+-------------+-------------+-------------------+---------------+-------------+| TABLE       | child       | SHARED_UPGRADABLE | TRANSACTION   | GRANTED     | <- ALTER (S2)| TABLE       | parent      | SHARED_WRITE      | TRANSACTION   | GRANTED     | <- UPDATE (S1)| TABLE       | parent      | SHARED_WRITE      | TRANSACTION   | GRANTED     | <- UPDATE (S3)| TABLE       | parent      | SHARED_READ_ONLY  | STATEMENT     | PENDING     | <- ALTER (S2)+-------------+-------------+-------------------+---------------+-------------+请注意,具有挂起锁定状态的唯一会话是会话 2(ALTER)。会话 1 和会话 3 (分别在 ALTER 之前和之后发布)都被授予了写锁。排序失败的地方是在会话 1 上发生提交的时候。在考虑有序队列时,人们会期望会话 2 获得锁定,事情就会继续进行。但是,由于元数据锁定系统的优先级性质,会话 3 具有锁定,会话 2 仍然等待。如果另一个写入会话进入并启动新事务并获取针对父表的写锁定,则即使会话 3 完成,ALTER 仍将被阻止。只要我保持一个对父表打开元数据锁定的活动事务,子表上的 ALTER 将永远不会完成。更糟糕的是,由于子表上的写锁定成功(但是完整语句正在等待获取父读锁定),所以针对子表的所有传入读取请求都将被阻止!另外,请考虑一下您通常如何对无法完成的语句进行故障排除。您查看已经打开较长时间的事务(在进程列表和 InnoDB 状态中)。但由于阻塞线程现在比 ALTER 线程更年轻,因此您将看到的最旧的事务/线程是 ALTER 。这正是这种情况下发生的情况。在准备发布时,我们的客户端正在运行 ALTER 语句并结合负载测试(一种非常好的做法!)以确保顺利发布。问题是负载测试保持对父表打开一个活动的写事务。这并不是说它只是一直在写,而是有多个线程,一个总是活跃的。 这阻止了 ALTER 完成并阻止对相对静态的子表的随后的读请求。幸运的是,这个问题有一个解决方案(除了从设计模式中驱逐外键)。变量 max_write_lock_count[2] 可用于允许在写入锁定之后在读取锁定之前授予读取锁定连续写锁。默认情况下,此变量设置为 18446744073709551615,如果你对该表发出 10,000 次写入/秒,那么你的读将被锁定 5800 万年……www.zgxue.com防采集请勿采集本网。

具体方法:

查询肯定会加读锁,对千万量级别的场景,考虑主从多个数据库架构环境吧。

(推荐教程:mysql数据库学习教程)

怎么查找mysql中的锁表语句 show processlist; SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程(也就是,与您正在使用的MySQ

查看表被锁状态

show OPEN TABLES where In_use > 0; show global status like 'table_locks%';

# 查询哪些表锁了show OPEN TABLES where In_use > 0;

1 show processlist; SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限,您可以看到所有线程。否则,您只能看到您自己的线程(也就是,与您正在使用的MySQL账户相关的线程)。如果

查看造成死锁的sql语句

第一步,查看行锁使用情况,命令: show statue like 'innodb_row_lock%'; 如下图所示: 第二步,创建数据库表monitor_amount,如下图所示: 第三步,查看innodb的状态,命令: show innodb status \G; 如下图所示: 第四步,向数据库表monitor_

# 查询innodb引擎的运行时信息show engine innodb status;

查询进程

# 查询所有进程show processlist;

解锁(删除进程)

# 删除进程kill id;

查看正在锁的事物

# 查看正在锁的事物SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

查看等待锁的事物

# 查看等待锁的事务SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

内容补充

MySQL锁定状态查看命令

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已经处理完了所有待处理的插入操作,正在等待新的请求。

到此这篇关于mysql查询表是否被锁的方法的文章就介绍到这了,更多相关mysql怎么查询表是否被锁内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

可直接在62616964757a686964616fe78988e69d8331333366306463mysql命令行执行:show engine innodb status\G;查看造成死锁的sql语句,分析索引情况,然后优化sql然后show processlist;show status like ‘%lock%’show OPEN TABLES where In_use > 0; 这个语句记录当前锁表状态 另外可以打开慢查询日志,linux下打开需在my.cnf的[mysqld]里面加上以下内容:slow_query_log=TRUE(有些mysql版本是ON)slow_query_log_file=/usr/local/mysql/slow_query_log.txtlong_query_time=3扩展资料:MySQL锁定状态查看命令Checking table:正在检查数据表(这是自动的)。   Closing tables:正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。 Connect Out:复制从服务器正在连接主服务器。   Copying to tmp table on disk:由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。 Creating tmp table:正在创建临时表以存放部分查询结果。 deleting from main table:服务器正在执行多表删除中的第一部分,刚删除第一个表,1.查看表是否被锁:(1)直接在mysql命令行执行:show engine innodb status\G。(2)查看造成死锁的sql语句,32313133353236313431303231363533e4b893e5b19e31333366306439分析索引情况,然后优化sql。(3)然后show processlist,查看造成死锁占用时间长的sql语句。(4)show status like ‘%lock%。2.查看表被锁状态和结束死锁步骤:(1)查看表被锁状态:show OPEN TABLES where In_use > 0; 这个语句记录当前锁表状态 。(2)查询进程:show processlist查询表被锁进程;查询到相应进程killid。(3)分析锁表的SQL:分析相应SQL,给表加索引,常用字段加索引,表关联字段加索引。(4)查看正在锁的事物:SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS。(5)查看等待锁的事物:SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS。扩展资料MySQL锁定状态查看命令: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:该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。waiting for handler insert:INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。本回答被网友采纳,show OPEN TABLES where In_use > 0;SHOW PROCESSLIST查看数据库中bai表的状du态,是否被锁;kill id //杀掉被锁的zhi表===================================================set autocommit=0;select * from t1 where uid='xxxx' for update //在有索引(例dao如uid)的情内况下是行锁,否则是容表锁insert into t1 values(1,'xxxxx');commit;=====================================================lock tables t1 write|read;insert into t1 values(2,'xxxxx'); //只有insertunlock tables;,show OPEN TABLES where In_use > 0;本回答被提问者和网友采纳,加锁情况32313133353236313431303231363533e78988e69d8331333433643032与死锁原因分析为方便大家复现,完整表结构和数据如下:CREATE TABLE `t3` (`c1` int(11) NOT NULL AUTO_INCREMENT,`c2` int(11) DEFAULT NULL,PRIMARY KEY (`c1`),UNIQUE KEY `c2` (`c2`)) ENGINE=InnoDBinsert into t3 values(1,1),(15,15),(20,20);在 session1 执行 commit 的瞬间,我们会看到 session2、session3 的其中一个报死锁。这个死锁是这样产生的:1. session1 执行 delete  会在唯一索引 c2 的 c2 = 15 这一记录上加 X lock(也就是在MySQL 内部观测到的:X Lock but not gap);2. session2 和 session3 在执行 insert 的时候,由于唯一约束检测发生唯一冲突,会加 S Next-Key Lock,即对 (1,15] 这个区间加锁包括间隙,并且被 seesion1 的 X Lock 阻塞,进入等待;3. session1 在执行 commit 后,会释放 X Lock,session2 和 session3 都获得 S Next-Key Lock;4. session2 和 session3 继续执行插入操作,这个时候 INSERT INTENTION LOCK(插入意向锁)出现了,并且由于插入意向锁会被 gap 锁阻塞,所以 session2 和 session3 互相等待,造成死锁。死锁日志如下: INSERT INTENTION LOCK在之前的死锁分析第四点,如果不分析插入意向锁,也是会造成死锁的,因为插入最终还是要对记录加 X Lock 的,session2 和 session3 还是会互相阻塞互相等待。但是插入意向锁是客观存在的,我们可以在官方手册中查到,不可忽略:Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.插入意向锁其实是一种特殊的 gap lock,但是它不会阻塞其他锁。假设存在值为 4 和 7 的索引记录,尝试插入值 5 和 6 的两个事务在获取插入行上的排它锁之前使用插入意向锁锁定间隙,即在(4,7)上加 gap lock,但是这两个事务不会互相冲突等待。当插入一条记录时,会去检查当前插入位置的下一条记录上是否存在锁对象,如果下一条记录上存在锁对象,就需要判断该锁对象是否锁住了 gap。如果 gap 被锁住了,则插入意向锁与之冲突,进入等待状态(插入意向锁之间并不互斥)。总结一下这把锁的属性:1. 它不会阻塞其他任何锁;2. 它本身仅会被 gap lock 阻塞。在学习 MySQL 过程中,一般只有在它被阻塞的时候才能观察到,所以这也是它常常被忽略的原因吧...GAP LOCK在此例中,另外一个重要的点就是 gap lock,通常情况下我们说到 gap lock 都只会联想到 REPEATABLE-READ 隔离级别利用其解决幻读。但实际上在 READ-COMMITTED 隔离级别,也会存在 gap lock ,只发生在:唯一约束检查到有唯一冲突的时候,会加 S Next-key Lock,即对记录以及与和上一条记录之间的间隙加共享锁。通过下面这个例子就能验证:这里 session1 插入数据遇到唯一冲突,虽然报错,但是对 (15,20] 加的 S Next-Key Lock 并不会马上释放,所以 session2 被阻塞。另外一种情况就是本文开始的例子,当 session2 插入遇到唯一冲突但是因为被 X Lock 阻塞,并不会立刻报错 “Duplicate key”,但是依然要等待获取 S Next-Key Lock 。有个困惑很久的疑问:出现唯一冲突需要加 S Next-Key Lock 是事实,但是加锁的意义是什么?还是说是通过 S Next-Key Lock 来实现的唯一约束检查,但是这样意味着在插入没有遇到唯一冲突的时候,这个锁会立刻释放,这不符合二阶段锁原则。这点希望能与大家一起讨论得到好的解释。如果是在 REPEATABLE-READ,除以上所说的唯一约束冲突外,gap lock 的存在是这样的:普通索引(非唯一索引)的S/X Lock,都带 gap 属性,会锁住记录以及前1条记录到后1条记录的左闭右开区间,比如有[4,6,8]记录,delete 6,则会锁住[4,8)整个区间。对于 gap lock,相信 DBA 们的心情是一样一样的,所以我的建议是:1. 在绝大部分的业务场景下,都可以把 MySQL 的隔离界别设置为 READ-COMMITTED;2. 在业务方便控制字段值唯一的情况下,尽量减少表中唯一索引的数量。锁冲突矩阵前面我们说的 GAP LOCK 其实是锁的属性,另外我们知道 InnoDB 常规锁模式有:S 和 X,即共享锁和排他锁。锁模式和锁属性是可以随意组合的,组合之后的冲突矩阵如下,这对我们分析死锁很有帮助内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql如何根据汉字首字母排序
  • navicat for mysql定时备份数据库及数据恢复详解
  • mssql 添加字段说明
  • mysql游标的使用实例
  • mysql性能优化之table_cache配置参数浅析
  • mysql数据库远程访问权限如何打开(两种方法)
  • mysql之explain使用详解(分析索引)
  • mysql登录警告问题的解决方法
  • mac下mysql初始密码忘记怎么办
  • linux mysql 找回密码
  • 查询mysql 哪些表正在被锁状态
  • 怎么查看mysql表是否被锁定
  • 如何查询mysql中是否表被锁
  • 如何查询mysql数据库表是否被锁 csdn
  • Mysql如何实现查询的时候不锁表
  • 怎么查找mysql中的锁表语句
  • 如何查询mysql中是否表被锁
  • 如何查看mysql中表的锁定情况
  • mysql 查看数据库中有没有锁
  • mysql的 for update查询用到其时,怎么知道是否锁表
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql如何根据汉字首字母排序navicat for mysql定时备份数据库及数据恢复详解mssql 添加字段说明mysql游标的使用实例mysql性能优化之table_cache配置参数浅析mysql数据库远程访问权限如何打开(两种方法)mysql之explain使用详解(分析索引)mysql登录警告问题的解决方法mac下mysql初始密码忘记怎么办linux mysql 找回密码mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql之timestamp(时间戳)用法mysql update语句的用法详解centos 7.0如何启动多个mysql实例教程(myselinux导致php连接mysql异常can""t conn去掉mysql连接时报警声音的方法mysqldump加-w参数备份数据时需要注意的事mysql where语句优化mysql批量执行sql文件的方法mysqldump备份还原和mysqldump导入导出语centos 6.5 下安装mysql 5.6.21的方法error code: 1175 you are using safe upmysql 5.6.37(zip)下载安装配置图文教程
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved