史上最全MySQL各种锁详解

最新推荐文章于 2024-09-23 09:12:23 发布

子龙技术

最新推荐文章于 2024-09-23 09:12:23 发布

阅读量963

点赞数 5

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_30823993/article/details/134915622

版权

锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。

MySQL锁可以按模式分类为：乐观锁与悲观锁。按粒度分可以分为全局锁、表级锁、页级锁、行级锁。按属性可以分为：共享锁、排它锁。按状态分为：意向共享锁、意向排它锁。按算法分为：间隙锁、临键锁、记录锁。

锁分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分，分为表锁和行锁

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

例如lock tables t1 read, t2 write; 命令，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能在unlock tables之前访问其他表。

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

行锁

每次操作锁住一行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高，但是加锁慢、开销大，容易发生死锁现象。

InnoDB与MYISAM的最大不同有两点：

InnoDB支持事务（TRANSACTION）
InnoDB支持行级锁

InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

实现方式

在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

可以看到由于session1迟迟未提交事务，session2在等待session1释放锁时出现了超过锁定超时的警告了。

无索引行锁会升级为表锁(RR级别会升级为表锁，RC级别不会升级为表锁)

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：select * from test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定行的session提交

卧槽，又来一个 Java 神器！！

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙。间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁在某些情况下可以解决幻读问题。

假设account表里数据如下：

那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间，

在Session_1下面执行 update account set name = ‘张三’ where id > 8 and id <18;，则其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在(3,20]区间都无法修改数据，注意最后那个20也是包含在内的。

间隙锁是在可重复读隔离级别下才会生效。

事务A：

事务B：

事务A：

事务B：

临键锁(Next-key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，是一个左开右闭区间。临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

卧槽，又来一个 Java 神器！！

记录锁

记录锁也叫行锁，例如：

select * from emp where empno = 1 for update;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法；乐观锁（ Optimistic Locking ）是相对悲观锁而言，乐观锁是假设认为即使在并发环境中，外界对数据的操作一般是不会造成冲突，所以并不会去加锁(所以乐观锁不是一把锁)，而是在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回冲突信息，让用户决定如何去做下一步，比如说重试，直至成功为止；数据库的乐观锁，并不是利用数据库本身的锁去实现的，可能是利用某种实现逻辑去实现做到乐观锁的思想。

数据库的乐观并发控制要解决的是数据库并发场景下的写-写冲突，指在用无锁的方式去解决

CAS思想

数据库乐观锁的具体实现几乎就跟Java中乐观锁采用的CAS算法思想是一致，所以我们可以从CAS算法中学习到数据库乐观锁的设计：

CAS指令全称为Compare and Swap，它是系统的指令集，整个CAS操作是一个原子操作，是不可分割的。从具体的描述上，我们可以这么看CAS操作：

CAS指令需要3个操作数，分别是内存位置V，旧的预期值A,和新值B。CAS指令执行时，当我们读取的内置位置V的现值等于旧预期值A时，处理器才会将新值B去更新内置位置V的值。否则它就不执行更新，但无论是否更新V的值，都会返回V的旧值。

我们通俗的放到代码层次上去理解i = 2; i++，就是说：

首先线程1从内存位置V中读取到了值，保存并作为旧预期值A. (v = 2 ,a = 2)
然后在因为i要进行++操作，系统会比较内存位置V的现值跟旧预期值A进行比较，既V =? A。
如果相等，B = i++ = 3 ，新值B就会对内存位置V进行更新，所以内存位置V的值就变成了B的值，3
如果不相等，则说明有其他的线程修改过了内存位置V的值，比如线程2在线程1修改i的值前就更新了i的值。，所以线程1会更新变量i失败。但线程不会挂起，而是返回失败状态，等待调用线程决定是否重试或其他操作。(通常会重试直到成功)
数据库层的乐观锁实现也类似代码层面的实现

实现方式

通常乐观锁的实现有两种，但它们的内在都是CAS思想的设计：

方式一：使用数据版本（version）实现
这是乐观锁最常用的一种实现方式。什么是数据版本呢？就是在表中增添一个字段作为该记录的版本标识，比如叫version，每次对该记录的写操作都会让 version+ 1。
所以当我们读取了数据(包括version)，做出更新，要提交的时候，就会拿取得的version去跟数据库中的version比较是否一致，如果一致则代表这个时间段，并没有其他的线程的也修改过这个数据，给予更新，同时version + 1；如果不一致，则代表在这个时间段，该记录以及被其他线程修改过了，认为是过期数据，返回冲突信息，让用户决定下一步动作，比如重试（重新读取最新数据，再过更新）

update table set num = num + 1 , version = version + 1 where version = #{version} and id = #{id}

方式二：使用时间戳(timestamp)实现

表中增加一个字段，名称无所谓，比如叫update_time, 字段类型使用时间戳（timestamp）
原理和方式一一致，也是在更新提交的时检查当前数据库中数据的时间戳和自己更新前取到的时间戳是否一致，如果一致则代表此刻没有冲突，可以提交更新，同时时间戳更新为当前时间，否则就是该时间段有其他线程也更新提交过，返回冲突信息，等待用户下一步动作。

update table set num = num + 1 ,update_time = unix_timestamp(now()) where id = #{id} and update_time = #{updateTime}

但是我们要注意的是，要实现乐观锁的思想的同时，我们必须要要保证CAS多个操作的原子性，即获取数据库数据的版本，拿数据库的数据版本与之前拿到的版本的比较，以及更新数据等这几个操作的执行必须是连贯执行，具有复合操作的原子性；所以如果是数据库的SQL,那么我们就要保证多个SQL操作处于同一个事务中。

悲观锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法; 悲观锁指的是采用一种持悲观消极的态度，默认数据被外界访问时，必然会产生冲突，所以在数据处理的整个过程中都采用加锁的状态，保证同一时间，只有一个线程可以访问到数据，实现数据的排他性；通常，数据库的悲观锁是利用数据库本身提供的锁机制去实现的.

数据库的悲观并发控制可以解决读-写冲突和写-写冲突,指在用加锁的方式去解决。

实现方式

数据库的悲观锁就是利用数据库本身提供的锁去实现的（select…for update是MySQL提供的实现悲观锁的方式）

外界要访问某条数据，那它就要首先向数据库申请该数据的锁
如果获得成功，那它就可以操作该数据，在它操作期间，其他客户端就无法再操作该数据了
如果获得失败，则代表同一时间已有其他客户端获得了该锁，那就必须等待其他客户端释放锁

共享锁

共享锁，又称之为读锁，简称S锁，当事务A对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能添加写锁。只有当事务A上的读锁被释放后，其他事务才能对其添加写锁。

共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免”不可重读”的问题的出现。

实现方式

select …lock in share mode

session1持有共享锁，未提交事务。session2的查询不受影响，但是update操作会被一直阻塞，直到超时。

排它锁

排它锁，又称之为写锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读写，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。

MySQL InnoDB引擎默认update,delete,insert都会自动给涉及到的数据加上排它锁，select语句默认不会加任何锁类型。

session1 不提交事务，session2不能加锁。

	兼容性	加锁方式
S锁：共享锁	加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁	select…lock in share mode
X锁：排他锁	加了X锁的记录，不允许其他事务再加S锁或者X锁	select…for update

意向共享锁和意向排它锁

意向锁是表锁，为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。

意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁

为什么意向锁是表级锁呢？

当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）；

（1）如果意向锁是行锁，则需要遍历每一行数据去确认；

（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

下图表示意向锁和共享锁、排他锁的兼容关系。
1.当事务A对某个数据范围（行或表）上了“某锁”后，另一个事务B是否能在这个数据范围上“某锁”。
2.意向锁相互兼容，因为IX、IS只是表明申请更低层次级别元素（比如 page、记录）的X、S操作。
3.表级S锁和X、IX锁不兼容：因为上了表级S锁后，不允许其他事务再加X锁。
4.表级X锁和 IS、IX、S、X不兼容：因为上了表级X锁后，会修改数据，所以即使是行级排他锁，因为表级锁定的行肯定包括行级锁定的行，所以表级X和IX、X都不兼容。

注意：上了行级X锁后，行级X锁不会因为有别的事务上了IX而堵塞，一个mysql是允许多个行级X锁同时存在的，只要他们不是针对相同的数据行。

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

-- 查看事务 
select * from INFORMATION_SCHEMA.INNODB_TRX; 
-- 查看锁 
select * from INFORMATION_SCHEMA.INNODB_LOCKS; 
-- 查看锁等待 
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到 
kill trx_mysql_thread_id 
-- 查看锁等待详细信息 
show engine innodb status\G;

死锁

set tx_isolation=‘repeatable-read’;

Session_1执行：select * from account where id=1 for update;

Session_2执行：select * from account where id=2 for update;

Session_1执行：select * from account where id=2 for update;

Session_2执行：select * from account where id=1 for update;

查看近期死锁日志信息：show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁