mysql数据库锁介绍，如何解锁mysql数据库锁表

本文内容摘要：

• 1.对话中你应该了解的MySQL锁的知识
• 2. MySQL锁和空间锁
• 3、mysql中什么是锁？
• 4.深入理解MySQL数据库中不同的锁（总结）
• 5、MySQL锁了数据库表只能写，为什么还能读？
• 6、mysql中的锁有哪些类型？

面试时你应该了解的MySQL锁知识

背景

数据库锁是一种在多线程、高并发情况下保证数据稳定性和一致性的机制。 MySQL根据底层存储的不同，有不同粒度的锁支持和实现机制。 MyISAM仅支持表锁，而InnoDB同时支持行锁和表锁。目前，MySQL 默认存储是 InnoDB。这里主要介绍InnoDB锁。

使用InnoDB主要有两个优点：一是支持事务；二是支持事务。其次，它支持行锁。

在高并发的情况下，事务的并发处理会带来几个问题

因为高并发事务带来这些问题，所以产生了事务隔离级别

举个例子

如果按照上面的1、2、3、4，你会发现第4步被阻塞了，需要完成第5步才能插入成功，这里我们很惊讶的是，uid=6的行明明被锁了，为什么不能我们插入5？原因是这里使用了next-key算法，并且锁定了整个区间(3,10)。大家有兴趣可以尝试一下。

今天给大家分享一些关于InnoDB MySQL事务和锁的知识。通过我自己的实践经验，我对它更加熟悉了。希望大家在观看的同时也可以尝试一下，以便更好地理解。了解的更深入。

mysql 锁和空格锁

在并发事务读写一条记录时，mysql提出了两种解决方案：

1）使用mvcc方式实现多个事务的并发读写，但这种读取只是“快照读”，一般情况下读取历史版本数据还有一个是“当前读”，一般是加锁实现“当前读”，或者插入、更新、删除也是当前读。

2）采用锁定方式。锁分为共享锁（读锁）和排它锁（写锁），它们需要加锁。

mysql如何处理RR级别的幻读？一般来说，RR级别使用mvcc机制来保证低于后续事务的数据会被加载。但是 select for update 并不会启动 mvcc，而是当前读取。如果后面的事务插入数据并提交，则插入的数据将在RR级别加载。因此，MySQL使用行锁+空间锁（称为附加键锁）来防止当前读期间的插入。

InnoDB中Gap Lock的唯一作用就是阻止其他事务的插入操作，从而防止幻读的发生。

Innodb 自动使用空白锁的条件：

mysql 中的锁是什么

MySQL 有哪些锁？

数据库锁最初是为了解决并发问题而设计的。数据库作为多个用户共享的资源，当发生并发访问时，数据库必须合理管理资源访问规则。实现这些访问规则时，锁是重要的数据。

锁的分类

根据锁定的范围，MySQL 中的锁可以分为三类：全局锁、表级锁和行锁。

全局锁

全局锁用于锁定整个数据库实例。 MySQL提供了添加全局读锁的方法。命令为：

使用读锁刷新表 (FTWRL)

当需要整个数据库实例时 您可以在库处于只读状态时使用此命令。那么，其他线程的数据更新命令（添加、删除、修改）、数据定义命令（创建表、修改表结构）、更新事务提交命令都会被阻塞。

全局锁使用场景

支持完整逻辑数据库备份的终极全局锁使用场景。这意味着选择整个库中的每个表，然后将其另存为文本。

如果整个库都是只读的会怎样？如果您要备份到主数据库，则在备份期间将无法进行更新，业务将基本上陷入停顿。如果在从库上进行备份，备份时从库无法运行从主库同步的binlog，会导致从库滞后。既然你希望整个库是只读的，为什么不使用 set global readonly=true 呢？

read-only 方法也可以让整个库只读，但我还是推荐大家使用 FTWRL 方法，主要有两个原因：

首先，在某些系统上 readonly 值会用于不同的逻辑，比如判断一个库是主库还是后备库。所以编辑全局变量的方式影响比较大，我不建议使用。其次，异常处理机制存在差异。如果执行FTWRL命令后客户端异常断开，MySQL会自动释放全局锁，使整个库恢复到可以正常更新的状态。如果将整个库设置为只读后，客户端出现异常，数据库将保持只读状态。这会导致整个库长期处于不可写状态，风险较高。表级锁

MySQL 中的表级锁 有两种类型的锁：一种是表锁，另一种是元数据（元数据锁，MDL）锁。锁定表的语法为：

locktables ... read/write

与FTWRL类似，可以使用unlocktables主动释放锁，或者在客户端断开连接时自动释放。需要注意的是，锁表的语法不仅限制了其他线程的读写，还限制了该线程后续的操作对象。

MDL 表级锁

MDL 不需要显式使用。当访问表时它会自动添加。 MDL保证读写的正确性。这意味着在查询数据时不允许其他线程访问该表。进行结构性改变。

哪些操作会添加MDL锁？

MDL 在 MySQL 5.5 版本中引入。对表进行增删改查时添加MDL读锁；对表进行结构更改时添加 MDL 写锁。

读锁不是互斥的，因此多个线程可以同时对表进行增删改查。读写锁和写锁是互斥的，用于保证表结构更改操作的安全。如果两个线程想要同时向表中添加字段，其中一个线程必须等待另一个线程完成执行。更改表结构时要注意什么？

在表中添加字段、编辑字段或添加索引时，需要扫描整个表的数据。运行大表时，必须特别小心，不要影响在线服务。事实上，即使是一个小表，操作不小心也会出现问题，导致整个库充满线程。

例如

让我们看看以下操作顺序。假设表t是一个小表。

图片

会话 A 首先开始。此时t表就会被加一个MDL读锁。由于会话B也需要MDL读锁，因此可以正常启动。会话C会被阻塞，因为会话A的MDL读锁还没有释放，而会话C需要MDL写锁，所以只能被阻塞，并且读写锁是互斥的。如果只有会话C本身被阻塞也没关系，但是后续所有对表t使用MDL读锁的请求也会被会话C阻塞。前面我们说过，所有对该表的增删改查操作必须首先请求MDL读锁，然后它们都被锁定，这意味着该表现在完全不可读写。

如果某个表被频繁查询，而客户端有重试机制，也就是说超时后会启动新的会话，再次发出请求，这个库的线程很快就会满。交易中的MDL锁是在订单执行之初请求的，但订单结束后不会立即释放，而是在整个交易确认后释放。

如何解决这个表结构改变问题

理想的机制是在alter table命令中设置等待时间。如果能在指定的等待时间内获得MDL写锁是最好的。如果没有，则无法获得。不要屏蔽下面的交易语句，先放弃。

ALTER TABLE tbl_name NOWAIT 添加列 ... ALTER TABLE tbl_name WAIT N 添加列 ...

深入了解 MySQL 数据库中的不同锁（总结）

MyISAM 和 InnoDB 存储使用的锁：

细块粒度：

由于 InnoDB 存储引擎支持行级锁，因此意向锁（因为意向锁是表锁）实际上不会阻塞，除非有其他要求比全表扫描。所以表级意向锁和行级锁的兼容性如下：

参考

参考

行锁的三种算法：

该命令阻止其他事务插入 10 到 20 之间的数字不管这个数字是否存在。空格可以包含 0 个、1 个或多个索引值。

共享锁：

独占锁：

乐观锁：总是假设最好的情况，认为每次获取数据时其他人都不会修改它（天真）并且不锁定数据流量但更新它会评估该期间的其他交易是否更新了该数据。如果已经更新，则会失败并重试；适合读多写少的场景。

实现乐观锁的方法有：

关闭自动发送后，我们需要手动启动事务。

以上实现了悲观锁。悲观锁是悲观主义者。它会认为，当我们在事务A中操作数据1时，事务B会修改数据1。因此，在第2步，我们在更改数据查询后，会直接加排它锁（X），防止其他事务修改事务 1. 排他锁只有在我们确认后才会被释放。

mysql锁了数据库表只能写，为什么还能读？

锁的作用就是将权限设为私有，让其他人无法使用。如果您自己锁定了手表，当然可以继续使用。

1。表级锁定是MySQL存储引擎中最细粒度的锁定机制。这种锁定机制的最大特点是实现逻辑非常简单，给系统带来的负面影响最小。所以获取和释放锁是非常快的。由于表级锁一次性锁定了整个表，因此可以避免困扰我们的死锁问题。

2。数据库锁定机制简单来说就是数据库为了保证并发访问时数据的一致性和各种共享资源的有序性而设计的规则。

3。每个数据库都需要适当的锁定机制，MySQL 也不例外。

4。由于自身架构的特点，MySQL数据库有多个引擎来存储数据。每个存储模块都有不同的应用场景特征。为了满足每个特定应用场景的需求，每个存储引擎所拥有的锁定机制都是针对其所面临的特定场景进行优化和设计的，因此每个存储引擎的锁定机制也完全不同。

5。一般来说，MySQL存储引擎使用三种类型（级别）的锁定机制：行级锁定、页级锁定和表级锁定。我们先来分析一下MySQL中这三种锁的特点以及各自的优缺点。

mysql中的锁有哪些类型

全局锁

顾名思义，全局锁会锁定整个数据库实例。 MySQL提供了增加全局读锁的方法。该命令是使用读锁刷新表 (FTWRL)。如果需要使整个库只读，可以使用此命令。来自其他线程的以下命令将被阻塞：数据更新命令（添加、删除和修改数据）、数据定义命令（包括创建表、修改表结构等）以及更新事务的提交命令。

表级锁

MySQL中有两种表级锁：一种是表锁，另一种是元数据锁（metadata lock，MDL）。

表锁

表锁的语法是表锁...读/写。与FTWRL类似，可以使用unlock表主动释放锁，或者在客户端断开连接时自动释放锁。需要注意的是，锁表的语法不仅限制了其他线程的读写，还限制了该线程后续的操作对象。

元数据锁

MDL不需要显式使用，访问表时会自动添加。 MDL的作用是保证读写的正确性。你可以想象一下，如果一个查询遍历表中的数据，执行过程中另一个线程对表结构进行了更改并删除了列，那么查询线程得到的结果与表结构不匹配，这是肯定不可能的。来自