mysql 的锁以及间隙锁

mysql 为并发事务同时对一条记录进行读写时，提出了两种解决方案：

创新互联建站提供网站设计制作、成都做网站、网页设计，品牌网站制作，一元广告等致力于企业网站建设与公司网站制作，10多年的网站开发和建站经验,助力企业信息化建设，成功案例突破上1000家,是您实现网站建设的好选择.

1）使用 mvcc 的方法，实现多事务的并发读写，但是这种读只是“快照读”，一般读的是历史版本数据，还有一种是“当前读”，一般加锁实现“当前读”，或者 insert、update、delete 也是当前读。

2）使用加锁的方法，锁分为共享锁（读锁），排他锁（写锁）

快照读：就是select

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。

mysql 在 RR 级别怎么处理幻读的呢？一般来说，RR 级别通过 mvcc 机制，保证读到低于后面事务的数据。但是 select for update 不会触发 mvcc，它是当前读。如果后面事务插入数据并提交，那么在 RR 级别就会读到插入的数据。所以，mysql 使用 行锁 + gap 锁（简称 next-key 锁）来防止当前读的时候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

Innodb自动使用间隙锁的条件：

mysql锁的简单想法(不确定对不对)

事务可以看见其他未提交事务的修改。会导致脏读。

事务只能看见其他提交事务的修改。但是如果事务A读取一批数据set，其他事务之后修改了这个数据set并提交(此时事务A没有提交)，这时事务A再读取数据set就跟第一次读取的结果不一致。会导致不可重复读。

在事务执行过程中，重复读到的数据是一致的

事务A读到了事务B未提交的数据。(read uncommitted)

事务A第一次读取行num，此时事务B修改行num并提交，事务A再读行num，数据会发生变化。（read uncommitted, read committed）

事务A第一次查询范围query_range，返回n行，此时事务B在该查询范围内插入了一行数据并提交，事务A再次查询范围query_range会看到B插入的数据。（read uncommitted, read committed, repeatable read）

在同一个事务执行过程中，两次相同的查询，但是查询的返回数据行数不同。

开启sessionA, sessionB

此时锁(行锁、gap锁)的情况如下图,标红为加锁，采用next key lock。(不光在索引a上加锁，索引a中3所对应的主键索引也会加锁，只画了索引a)

注意：这里是主键索引的顺序与a索引的顺序一致的情况。一致的情况下，新插入的4会插入在原来的4之后。我们定义函数index_key(x), 表示获取x所对应的主键索引，new(x)表示新插入的x，old(x)表示已经存在的x。如果index_key(old(4))index_key(new(4))，那新的4是插不进去的，因为新的4会被放在老的4的前面。同理，新插入的2也有可能插入进去(只要index_key(new(2))index_key(old(2)))。具体的例子整理后发出

对于sessionB，插入2，3失败，插入4成功。(如图lock.png，新插入的2会被2与3之间gap锁阻止，新插入的3肯定失败，但是新插入的4就没问题)

开启sessionA：

这个时候情况如下图所示:

如果按照实例1的分析，不看主键索引id，应该插入(id=4,a=3)应该会被gap锁锁定，但是事实是可以插入成功。因为插入的(i4=d,a=3)放在了(id=5,a=3)的上面。

同样的道理，不看主键索引id，通过实例1的分析，插入(id=6,a=7)应该可以插入，但是事实不行，因为(id=6,a=7)被a=5与a=7之间的gap锁阻止了。

新插入的(id=4, a=3)，需要判断会插入在(id=5, a=3)之前还是之后，很明显之前(45)，那么(id=3, a=5)与(id=5, a=3)之间的gap锁不会阻止。

新插入的(id=6, a=7)，需要判断会插入在(id=7, a=7)之前还是之后，很明显之前(67)，那么(id=6, a=7)会被(id=3, a=5)与(id=5, a=3)与(id=7, a=7)之间的gap锁阻止。

mysql读数据时怎么加写锁

加锁情况与死锁原因分析

为方便大家复现，完整表结构和数据如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert 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，即共享锁和排他锁。锁模式和锁属性是可以随意组合的，组合之后的冲突矩阵如下，这对我们分析死锁很有帮助：

请点击输入图片描述

mysql默认隔离级别怎么加锁

修改方法

有两种方法可以对配置了 systemd 的程序进行资源隔离：1. 命令行修改：通过执行 systemctl set-property 命令实现，形式为 systemctl set-property name parameter=value；修改默认即时生效。2. 手工修改文件：直接编辑程序的 systemd unit file 文件，完成之后需手工执行 systemctl daemon-reload 更新配置，并重启服务 systemctl restart name.service。

systemd unit file 里支持的资源隔离配置项，如常见的：

CPUQuota=value

该参数表示服务可以获取的最大 CPU 时间，value 为百分数形式，高于 100% 表示可使用 1 核以上的 CPU。与 cgroup cpu 控制器 cpu.cfs_quota_us 配置项对应。

MemoryLimit=value

该参数表示服务可以使用的最大内存量，value 可以使用 K, M, G, T 等后缀表示值的大小。与 cgroup memory 控制器 memory.limit_in_bytes 配置项对应。

事务的4种隔离级别

READ UNCOMMITTED       未提交读，可以读取未提交的数据。

READ COMMITTED         已提交读，对于锁定读(select with for update 或者 for share)、update 和 delete 语句，InnoDB 仅锁定索引记录，而不锁定它们之间的间隙，因此允许在锁定的记录旁边自由插入新记录。                    

Gap locking 仅用于外键约束检查和重复键检查。

REPEATABLE READ        可重复读，事务中的一致性读取读取的是事务第一次读取所建立的快照。

SERIALIZABLE           序列化在了解了 4 种隔离级别的需求后，在采用锁控制隔离级别的基础上，我们需要了解加锁的对象(数据本身间隙)，以及了解整个数据范围的全集组成。

数据范围全集组成

SQL 语句根据条件判断不需要扫描的数据范围（不加锁）；

SQL 语句根据条件扫描到的可能需要加锁的数据范围；

以单个数据范围为例，数据范围全集包含：（数据范围不一定是连续的值，也可能是间隔的值组成）

如何对MySQL数据库表进行锁定

服务器由两种表的锁定方法：

1.内部锁定

内部锁定可以避免客户机的请求相互干扰——例如，避免客户机的SELECT查询被另一个客户机的UPDATE查询所干扰。也可以利用内部锁定机制防止服务器在利用myisamchk或isamchk检查或修复表时对表的访问。

语法：

锁定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解锁表：UNLOCK TABLES

LOCK TABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。

如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。

每个线程等待(没有超时)直到它获得它请求的所有锁。

WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁, 随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。

显然对于检查，你只需要获得读锁。再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表。对于修复，你必须获得些所以防止任何客户机在你对表进行操作时修改它。

2.外部锁定

服务器还可以使用外部锁定（文件级锁）来防止其它程序在服务器使用表时修改文件。通常，在表的检查操作中服务器将外部锁定与myisamchk或isamchk作合使用。但是，外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作。对运行myisamchk或isamchk所选择的过程取决于服务器是否能使用外部锁定。如果不使用，则必修使用内部锁定协议。

如果服务器用--skip-locking选项运行，则外部锁定禁用。该选项在某些系统中是缺省的，如Linux。可以通过运行mysqladmin variables命令确定服务器是否能够使用外部锁定。检查skip_locking变量的值并按以下方法进行：

◆

如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表。服务器和实用程序将合作对表进行访问。但是，运行任何一个实用程序之前，应该使用mysqladmin

flush-tables。为了修复表，应该使用表的修复锁定协议。

◆

如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，最好关闭服务器。如果坚持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它。必须使用卡党的锁定协议告诉服务器是该表不被其他客户机访问。

检查表的锁定协议

本节只介绍如果使用表的内部锁定。对于检查表的锁定协议，此过程只针对表的检查，不针对表的修复。

1.调用mysql发布下列语句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name READ;mysqlFLUSH TABLES;

该锁防止其它客户机在检查时写入该表和修改该表。FLUSH语句导致服务器关闭表的文件，它将刷新仍在告诉缓存中的任何为写入的改变。

2.执行检查过程

$myisamchk tbl_name$ isamchk tbl_name

3.释放表锁

mysqlUNLOCK TABLES;

如果myisamchk或isamchk指出发现该表的问题，将需要执行表的修复。

修复表的锁定协议

这里只介绍如果使用表的内部锁定。修复表的锁定过程类似于检查表的锁定过程，但有两个区别。第一，你必须得到写锁而非读锁。由于你需要修改表，因此根本不允许客户机对其进行访问。第二，必须在执行修复之后发布FLUSH

TABLE语句，因为myisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速缓存，否则服务器不会注意到这个改变。本例同样适合优化表的过程。

1.调用mysql发布下列语句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name WRITE;mysqlFLUSH TABLES;

2.做数据表的拷贝，然后运行myisamchk和isamchk：

$cp tbl_name.* /some/other/dir$myisamchk --recover tbl_name$ isamchk --recover tbl_name

--recover选项只是针对安装而设置的。这些特殊选项的选择将取决与你执行修复的类型。

3.再次刷新高速缓存，并释放表锁：

mysqlFLUSH TABLES;mysqlUNLOCK TABLES;

网站栏目：怎么加锁mysql 怎么加锁微信钱包
标题网址：http://chengdu.cdxwcx.cn/article/docphch.html