mysql 独占锁_MySQL（三）、锁

最新推荐文章于 2023-06-15 15:17:17 发布

油腻少年

最新推荐文章于 2023-06-15 15:17:17 发布

阅读量621

点赞数

文章标签： mysql 独占锁

本文链接：https://blog.csdn.net/weixin_42397335/article/details/113414335

版权

MySQL系列：

MySQL(一)、InnoDB索引原理及优化

MySQL(二)、事务

简介

锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，MySQL中的锁是在服务器层或者存储引擎层实现的。

锁级别

MySQL引擎默认的锁级别：

MyISAM：表级锁；
Memory：表级锁；
InnoDB：支持行级锁和表级锁，默认为行级锁。
BDB：页面锁或表级锁，默认为页面锁。(MySQL5.1之后弃用)

(在后面的文章中会详细对比MySQL的常用引擎，下面对MySQL支持的锁进行介绍)

全局锁

对整个数据库实例加锁，加锁后整个库处于只读状态的时候，之后其他线程的以下语句会被阻塞：DML(增删改)、DDL、事务提交。不会影响DQL(查询请求)，从而获取一致性视图，保证数据的完整性。

全局锁：https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

全局锁命令：

-- 加全局锁 (FTWRL)flush tables with read lock; -- 释放全局锁unlock tables;或断开加全局锁的会话连接

错误信息：[Error Code: 1223, SQL State: HY000] Can't execute the query because you have a conflicting read lock

全局锁的场景是全库备份，但前面也介绍了，全局锁的时候库是只读状态。此时如果在主库加全局锁进行备份，业务就会停摆，如果在从库加全局锁，就无法同步主库的操作导致主从延迟。其实在实际应用中我们可以对不同的存储引擎使用不同的策略备份。

使用mysqldump工具备份：

InnoDB引擎：利用MVCC提供一致性视图，开启一个RR级别的事务，保证备份过程中数据可以正常更新，dump对应参数为：--single-transaction；
MyISAM引擎：利用FTWRL加全局锁，dump对应参数为：--lock-all-tables；

表级锁

表级锁(Table-level Locking)：表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持；

优点：开销小，加锁快；不会出现死锁；
缺点：锁定粒度大，发出锁冲突的概率最高，并发度最低。

表级锁：https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

表锁命令：

-- 加表锁lock tables … read/write; -- 释放表锁unlock tables;或断开加表锁的会话连接

错误信息：[Error Code: 1099, SQL State: HY000] Table 'csdn' was locked with a READ lock and can't be updated

表读锁：

表写锁：

元数据锁(Metadata Locking)：MySQL5.5之后新增，MDL也是表级别的锁，不需要显示的使用，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，可以有多个线程同时对一张表增删改查；
读写锁之间、写锁之间是互斥的，用来保证表结构变更操作的安全性。如果有两个线程要同时给一个表加字段，其中一个要阻塞等待另一个执行完才能开始执行。

页级锁

页级锁(Page-level Locking)：锁定表中某些行集合(称做页)，被锁定的行只对锁定最初的线程是可行。如果另外一个线程想要向这些行写数据，它必须等到锁被释放。不过其他页的行仍然可以使用。在MySQL5.1之前BDB引擎默认页级锁，之后被弃用。

行级锁

行级锁(Row-level Locking)：基于索引数据结构实现，是 MySQL 中锁定粒度最细的一种锁，只有线程当前使用的行被锁定，其他行对于其他线程都是可用的；InnoDB引擎默认的锁级别。

优点：发生锁冲突几率低，并发高
缺点：开销大，加锁慢；会出现死锁的情况；

行级锁包括共享锁、排他锁和更新锁。在下面的内容中具体介绍。

InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引会锁住整个表。

InnoDB锁

InnoDB锁：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

共享锁S

共享锁(Shared Locks)：简称S锁，也被称为读锁。读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰。

语法：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁X

排他锁(Exclusive Locks)：简称X锁，也叫独占锁、写锁。一个独占锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL 会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。

语法：

SELECT * FROM table_name WHERE ... FOR UPDATE

意向锁

意向锁(Intention Locks)：提前声明一个意向，并获取表级别的意向锁(共享意向锁 IS 或排他意向锁 IX)，如果获取成功，则稍后将要或正在(才被允许)，对该表的某些行加锁(S或X)了。(除了 LOCK TABLES ... WRITE,会锁住表中所有行，其他场景意向锁实际不锁住任何行)。

意向锁包含：

意向共享锁(Intention Shared Lock)：简称IS锁，事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁.
意向独占锁(Intention Exclusive Lock)：简称IX锁，事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁.

意向锁协议：在事务能够获取表中行上的共享锁之前，它必须首先获取表上的IS锁或更强的锁。在事务能够获取表中的行上的独占锁之前，它必须首先获取表上的IX锁。

InnoDB锁兼容性列表

	IX	S	IS
X
IX	兼容		兼容
S		兼容	兼容
IS	兼容	兼容	兼容

共享锁和排他锁都是标准的行级锁，意向锁是表级锁。
意向锁是比X、S更弱的锁，存在一种预判的意义！先获取更弱的IX、IS锁，如果获取失败就不必再花费更大的开销获取更强的X、S锁。

记录锁

记录锁(Record Locks)：最简单的行锁，行锁是加在索引上的，如果查询命令不走索引时，这条语句会锁住所有记录也就是锁表，如果语句的执行能够执行某一个字段的索引，那么仅会锁住满足 where 的行(Record Lock)。

间隙锁

间隙锁(Gap Locks)：锁定索引记录之间的间隙([2])，或者锁定一个索引记录之前的间隙([1])，或者锁定一个索引记录之后的间隙([3])。

例：

如图[1]、[2]、[3]部分。一般作用于我们的范围筛选查询> 、< 、between......

例如， SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事务将值3插入到列 userId 中。因为该范围内所有现有值之间的间隙都是锁定的。

对于使用唯一索引来搜索唯一行的语句 select a from ，不产生间隙锁定。(不包含组合唯一索引，也就是说 gapLock 不作用于单列唯一索引)。

例如，如果id列有唯一的索引，下面的语句只对id值为100的行使用索引记录锁，其他会话是否在前一个间隙中插入行并不重要:

SELECT * FROM t1 WHERE id = 100; 如果id没有索引或具有非唯一索引，则语句将锁定前面的间隙。

间隙可以跨越单个索引值、多个索引值(如上图2,3)，甚至是空的；

间隙锁是性能和并发性之间权衡的一种折衷，用于某些特定的事务隔离级别，如RC级别(RC级别：REPEATABLE READ，我司为了减少死锁，关闭了gap锁，使用RR级别)；

在重叠的间隙中(或者说重叠的行记录)中允许gap共存；

例如，同一个 gap 中，允许一个事务持有 gap X-Lock(gap 写锁\排他锁)，同时另一个事务在这个 gap 中持有(gap 写锁\排他锁)；

Next-Key Lock

记录锁与与间隙锁的结合。

例：存在一个查询匹配 b=3 的行(b上有个非唯一索引)，那么所谓 NextLock 就是：在b=3 的行加了 RecordLock 并且使用 GapLock 锁定了 b=3 之前(“之前”：索引排序)的所有行记录。

innodb 中默认隔离级别(RR)下，next key Lock 自动开启；

插入意向锁

插入意向锁(Insert Intention Locks)：是一种间隙锁，在行执行 INSERT 之前的插入操作设置。如果多个事务 INSERT 到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突。假设有值为4和7的索引记录，现在有两事务分别尝试插入值为 5 和 6 的记录，在获得插入行的排他锁之前，都使用插入意向锁锁住 4 和 7 之间的间隙，但两者之间并不会相互阻塞，因为这两行并不冲突。

插入意向锁只会和间隙或者 Next-key 锁冲突，正如上面所说，间隙锁作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

不同类型的锁之间的兼容如下表所示：

	RECORED	GAP	NEXT-KEY	II GAP(插入意向锁)
RECORED		兼容		兼容
GAP	兼容	兼容	兼容	兼容
NEXT-KEY		兼容		兼容
II GAP	兼容			兼容

自增锁

自增锁(Auto-inc Locks)：是一种特殊的表级别锁，专门针对事务插入AUTO_INCREMENT类型的列。如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

站内摘来的一张时序图，帮助理解MySQL的事务和锁：

扩展

死锁

死锁(Dead Lock)：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

产生死锁的4个必要条件：

互斥条件：一个资源每次只能被一个线程使用
请求与保持条件：一个线程因请求资源而阻塞时，对已获得的资源保持不放
不剥夺条件：进程已经获得的资源，在未使用完之前，不能强行剥夺
循环等待条件：若干线程之间形成一种头尾相接的循环等待资源关系

死锁检测：

对于死锁，我们可以通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑(默认是开启状态)。

wait-for graph 算法来主动进行死锁检测：innodb 还提供了 wait-for graph 算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，wait-for graph 算法都会被触发。

避免死锁的方法：

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低发生死锁的可能性；
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

如何解决热点行更新导致的性能问题？

如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用；
控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了；
将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高；

MyISAM 中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待。

活锁

活锁、死锁本质上是一样的：都是多个线程任务没有任何进展。原因是在获取共享资源时，并发多线程或多进程声明资源占用(即加锁)的顺序有冲突，死锁是加不上就死等(多线程都处于阻塞状态)；活锁是加不上就放开已获得的资源重试，这种情况下线程并没有阻塞所以是活的状态，但是只是在做无用功(每次重试都是失败的)，其实多核活锁不太常见(资源分配充足)。

例：资源A和B，进程P1和P2

start：P1 lock AP2 lock BP1 lock B fail context switchP2 lock A fail context switchP1 release AP2 release Bgoto start    #活锁，不断尝试，做无用功

悲观锁

顾名思义，就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改，所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制，事实上关系数据库中的行锁，表锁，读写锁都是悲观锁。

乐观锁

乐观锁一般是指用户自己实现的一种锁机制。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理(也就是不加锁)，而在进行更新后，再去判断是否有冲突了。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

常见乐观锁实现方式：

版本号机制：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。
CAS算法：比较与交换(compare and swap)，是一种很有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步(Non-blocking Synchronization)。CAS算法涉及到三个操作值：
- 需要读写的内存值 V
- 进行比较的值 A
- 拟写入的新值 B

当且仅当 V 的值等于 A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作(比较和替换是一个原子操作)。一般情况下是一个自旋操作，即不断的重试。

注意：乐观锁只能用于本系统控制，无法阻止外系统更新；

并发控制(MVCC)

MVCC (multiple-version-concurrency-control)：行级锁的变种，在普通读情况下避免了加锁操作，因此开销更低。虽然实现不同，但通常都是实现非阻塞读，对于写操作只锁定必要的行。

一致性读 (就是读取快照)select * from table ....
当前读(就是读取实际的持久化的数据)，特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。select * from table where ... lock in share mode; select * from table where ... for update; insert; update ; delete;

注意：select ...... from table ...... (没有额外加锁后缀)使用MVCC，保证了读快照(MySQL 称为 consistent read)，所谓一致性读或者读快照就是读取当前事务开始之前的数据快照，在这个事务开始之后的更新不会被读到。

InnoDB的 MVCC 通常是通过在每行数据后边保存两个隐藏的列来实现(其实是三列，第三列是用于事务回滚)，一个保存了行的创建版本号，另一个保存了行的更新版本号(上一次被更新数据的版本号) 这个版本号是每个事务的版本号，递增的。这样保证了 InnoDB对读操作不需要加锁也能保证正确读取数据。

在MySQL 默认的 Repeatable Read 隔离级别下， MVCC 的具体操作：

Select(快照读，所谓读快照就是读取当前事务之前的数据)：
a.InnoDB 只 select 查找版本号早于当前版本号的数据行，这样保证了读取的数据要么是在这个事务开始之前就已经 commit 了的(早于当前版本号)，要么是在这个事务自身中执行创建操作的数据(等于当前版本号)。
b.查找行的更新版本号要么未定义，要么大于当前的版本号(为了保证事务可以读到老数据)，这样保证了事务读取到在当前事务开始之后未被更新的数据。
注意：这里的 select 不能有 for update、lock in share 语句。总之要只返回满足以下条件的行数据，达到了快照读的效果：

(行创建版本号当前版本号 && (行更新版本号==null or 行更新版本号>当前版本号 ) )

Insert：InnoDB为这个事务中新插入的行，保存当前事务版本号的行作为行的行创建版本号。
Delete：InnoDB 为每一个删除的行保存当前事务版本号，作为行的删除标记。
Update：将存在两条数据，保持当前版本号作为更新后的数据的新增版本号，同时保存当前版本号作为老数据行的更新版本号。

当前版本号—写—>新数据行创建版本号 && 当前版本号—写—>老数据更新版本号();

希望本文对你有帮助，请点个赞鼓励一下作者吧~ 谢谢！

油腻少年

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 独占锁_MySQL（三）、锁

MySQL系列：MySQL(一)、InnoDB索引原理及优化MySQL(二)、事务简介锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，MySQL中的锁是在服务器层或者存储引擎层实现的。锁级别MySQL引擎默认的锁级别：MyISAM：表级锁；Memory：表级锁；InnoDB：支持行级锁和表级锁，默认为行级锁。...
复制链接

扫一扫