MySQL锁表和解锁操作

1、确定mysql有锁表的情况则使用以下命令查看锁表进程

创新互联是一家专注于成都网站制作、成都网站建设与策划设计,山东网站建设哪家好?创新互联做网站,专注于网站建设10多年,网设计领域的专业建站公司;建站业务涵盖:山东等地区。山东做网站价格咨询:18982081108

2、杀掉查询结果中已经锁表的trx_mysql_thread_id

扩展：

1、查看锁的事务

2、查看等待锁的事务

3、查询是否锁表：

4、查询进程

如何查询mysql中是否表被锁

可直接在mysql命令行执行：show engine innodb status\G;

查看造成死锁的sql语句，分析索引情况，然后优化sql然后show processlist;

另外可以打开慢查询日志，linux下打开需在my.cnf的[mysqld]里面加上以下内容：

怎么查看mysql表是否被锁定

当你开始执行一个 ALTER ，而你遇到了可怕的“元数据锁定等待”，我敢肯定你一定遇见过。我最近遇到了一个案例，其中被更改的表要执行一个很小范围的更新（100行）。ALTER 在负载测试期间一直等待了几个小时。在停止负载测试后，ALTER 按预期在不到一秒的时间内就完成了。那么这里发生了什么？

检查外键

每当有奇数次锁定时，我的第一直觉就是检查外键。当然这张表有一些外键引用了一个更繁忙的表。但是这种行为似乎仍然很奇怪。对表运行 ALTER 时，会针对子表请求一个 SHARED_UPGRADEABLE 元数据锁。还有针对父级的 SHARED_READ_ONLY 元数据锁。

我们来看看如何根据文档获取元数据锁定[1]：

如果给定锁定有多个服务器，则首先满足最高优先级锁定请求，并且与 max_write_lock_count系统变量有关。写锁定请求的优先级高于读取锁定请求。

[1]：

请务必注意锁定顺序是序列化的：语句逐个获取元数据锁，而不是同时获取，并在此过程中执行死锁检测。

通常在考虑队列时考虑先进先出。如果我发出以下三个语句（按此顺序），它们将按以下顺序完成：

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 万年……

MySQL数据库中查询表是否被锁以及解锁

1.查看表被锁状态

2.查看造成死锁的sql语句

3.查询进程

4.解锁(删除进程)

5.查看正在锁的事物  (8.0以下版本)

6.查看等待锁的事物 (8.0以下版本)

MySQL从入门到精通(九) MySQL锁，各种锁

锁是计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统的计算资源（CPU、RAM、I/O）争用外，数据也是一种供许多用户共享的资源，如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言是尤其重要，也更加复杂。MySQL中的锁，按照锁的粒度分为:1、全局锁，就锁定数据库中的所有表。2、表级锁，每次操作锁住整张表。3、行级锁，每次操作锁住对应的行数据。

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将阻塞。其典型的使用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。但是对数据库加全局锁是有弊端的，如在主库上备份，那么在备份期间都不能执行更新，业务会受影响，第二如果是在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟。

解决办法是在innodb引擎中，备份时加上--single-transaction参数来完成不加锁的一致性数据备份。

添加全局锁: flush tables with read lock; 解锁 unlock tables。

表级锁,每次操作会锁住整张表.锁定粒度大,发送锁冲突的概率最高,并发读最低,应用在myisam、innodb、BOB等存储引擎中。表级锁分为: 表锁、元数据锁(meta data lock, MDL)和意向锁。

表锁又分为: 表共享读锁 read lock、表独占写锁write lock

语法: 1、加锁 lock tables 表名 ... read/write

2、释放锁 unlock tables 或者关闭客户端连接

注意: 读锁不会阻塞其它客户端的读，但是会阻塞其它客户端的写，写锁既会阻塞其它客户端的读，又会阻塞其它客户端的写。大家可以拿一张表来测试看看。

元数据锁,在加锁过程中是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上，MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML和DDL冲突，保证读写的正确性。

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作时，加MDL写锁(排他).

查看元数据锁:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向锁，为了避免DML在执行时，加的行锁与表锁的冲突，在innodb中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。意向锁分为,意向共享锁is由语句select ... lock in share mode添加。意向排他锁ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生锁冲突的概率最高，并发读最高，应用在innodb存储引擎中。

innodb的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，对于行级锁，主要分为以下三类:

1、行锁或者叫record lock记录锁，锁定单个行记录的锁，防止其他事物对次行进行update和delete操作，在RC,RR隔离级别下都支持。

2、间隙锁Gap lock,锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事物在这个间隙进行insert操作，产生幻读，在RR隔离级别下都支持。

3、临键锁Next-key-lock,行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap,在RR隔离级别下支持。

innodb实现了以下两种类型的行锁

1、共享锁 S: 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

2、排他锁 X: 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

insert 语句 排他锁 自动添加的

update语句 排他锁 自动添加

delete 语句 排他锁 自动添加

select 正常查询语句 不加锁 。。。

select 。。。lock in share mode 共享锁 需要手动在select 之后加lock in share mode

select 。。。for update 排他锁 需要手动在select之后添加for update

默认情况下，innodb在repeatable read事务隔离级别运行，innodb使用next-key锁进行搜索和索引扫描，以防止幻读。

间隙锁唯一目的是防止其它事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用的间隙锁。

MYSQL数据库怎么查看 哪些表被锁了

以下五种方法可以快速定位全局锁的位置，仅供参考。

方法1：利用 metadata_locks 视图

此方法仅适用于 MySQL 5.7 以上版本，该版本 performance_schema 新增了 metadata_locks，如果上锁前启用了元数据锁的探针（默认是未启用的），可以比较容易的定位全局锁会话。

方法2：利用 events_statements_history 视图此方法适用于 MySQL 5.6 以上版本，启用 performance_schema.eventsstatements_history（5.6 默认未启用，5.7 默认启用），该表会 SQL 历史记录执行，如果请求太多，会自动清理早期的信息，有可能将上锁会话的信息清理掉。

方法3：利用 gdb 工具如果上述两种都用不了或者没来得及启用，可以尝试第三种方法。利用 gdb 找到所有线程信息，查看每个线程中持有全局锁对象，输出对应的会话 ID，为了便于快速定位，我写成了脚本形式。也可以使用 gdb 交互模式，但 attach mysql 进程后 mysql 会完全 hang 住，读请求也会受到影响，不建议使用交互模式。

方法4：show processlist

如果备份程序使用的特定用户执行备份，如果是 root 用户备份，那 time 值越大的是持锁会话的概率越大，如果业务也用 root 访问，重点是 state 和 info 为空的，这里有个小技巧可以快速筛选，筛选后尝试 kill 对应 ID，再观察是否还有 wait global read lock 状态的会话。

方法5：重启试试！

当前名称：mysql怎么查询表的锁,mysql锁表
分享地址：http://chengdu.cdxwcx.cn/article/heodsd.html