< Back

MySQL Lock

事务的隔离性由锁来实现。

写/写

两个事务去操作同一条记录，T1修改并提交以为自己成功修改了数据，这时T2回滚了，导致了T1修改了个寂寞，这就是脏写的情况。解决的办法就是加锁。当多个未提交的事务相继对一条记录做改动时，让他们排队执行，排队就是通过锁来实现。

当一个事务（比如叫T1）想对一条记录做修改时，会首先看内存中有没有跟这条记录相关联的锁结构，如果没有就会生成一个锁结构与之关联。 比如锁结构中有两个字段：trx记录当前事务是谁，is_waiting代表当前事务是否需要等待。（只要有一个事务要来操作这条记录就会产生一个锁结构，比如这时再来一个新的事务T2，那么就会产生一个新的跟T2关联的锁结构） 这时，因为没有别的事务，所以is_waiting是false，表示他无需等待，可以执行，或者叫做获取锁成功了，或者加锁成功。 当T1还没操作完，另一个事务T2进来的时候，T2生成的锁结构里的is_waiting就是true，或者说他获取锁失败了。 当T1结束（提交或者回滚）后，会去看还有没有别的事务在等待，然后找到了T2，将其is_waiting设置为false，开始执行T2

因为这种情况实在无法容忍，所以在任何一种隔离级别中都解决了。所以主要关注读写的问题

读/写

这种情况，可能发生脏读，不可重复读，幻读的问题。解决这些问题有两种方案。

  • 方案1: 读操作用mvcc，写操作进行加锁 在read commited级别下，可以避免脏读，因为保证每次都读到已经提交的。 在repeatable read级别下，这样还能解决不可重复读和幻读。
  • 方案2: 读写都加锁 对于脏读，比如A负责读，B负责写，B在写的时候加锁了，那么A在B事务完成前都读不了 对于不可重复读，比如A负责读，B负责写，A在读的时候加锁了，那么B在A完成前无法修改 对于幻读，就会有问题，A负责读，比如说id<10的所有数据，此时读到3条，对这三条加锁，但是这并不影响B插入一条新的数据，此时A再读就跟上次结果不一样了

对比：

  • MVCC，读写不冲突，性能更高
  • 加锁，读写要排队，影响性能 一般情况下当然跟愿意采用MVCC来解决读写并发问题，但是业务在某些特殊情况下必须要加锁来执行。

锁的类型

按数据操作类型来分：读锁，写锁。

读锁也叫做共享锁，shared lock，S锁。 写锁也叫做排他锁，exclusive lock，X锁。 innodb提供的粒度比较小，能支持到行锁。

  • 读操作 读操作也可以加x锁，比如：
select * ... for update

只要是加了x锁，就会阻塞，因此即使是读操作，但是因为加了x锁，也会阻塞其他事务。 两个事务只有明确加锁才会造成阻塞行为，比如事务1用到了for update，但是事务2没有加任何锁来读被锁的行，不会阻塞，只有在他也明确加上s或者x锁的时候才会阻塞。

mysql8有几个新特性：

select * from users for share nowait;  -- 如果拿不到锁就会报错
select * from users for share skip locked; -- 如果拿不到锁不阻塞也不报错，但是也拿不到被锁的数据
  • 写操作 泛泛而谈的话，三者都会加上x锁，但是更细节的来说，delete和update会真正加上x锁，insert不会用到，不过insert会用到一个隐式锁来保证在提交前不被访问到。

按照数据操作的粒度来分：表级锁，页级锁，行锁

为了尽可能的提高数据库的并发性，粒度当然是越小越好，但是这样对资源的消耗也会更大一些，因为涉及到获取、检查、释放锁等行为。

  • 表锁 开销最小的策略，也可以避免死锁的问题，但是并发性能大打折扣。 通常使用innodb的时候就不会去使用表锁了，因为可以提供粒度小的行锁。 因为表锁尽量不用，所以我懒得记了。。。

  • 行锁 优点：粒度小，并发性能好 缺点：锁的开销比较大，加锁会比较慢，容易出现死锁

  1. 记录锁 锁一条记录，比如
-- 事务1
update users set name = 'tracy' where id = 1; -- 自动加上x锁

-- 事务2
select * from users where where id = 1 for share; -- 阻塞，直到事务1提交

可是如果对应记录不存在，那么加锁了也不会发生阻塞。比如id=2的记录不存在的情况下：

-- 事务1
update users set name = 'tracy' where id = 2; -- 自动加上x锁

-- 事务2
select * from users where where id = 2 for share; -- 记录不存在，所以不会阻塞
  1. 间隙锁（Gap lock） 间隙锁的提出仅仅是为了防止插入幻影记录而提出的。 mysql> select * from students; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | Alice | 20 | | 3 | Bob | 22 | | 5 | Charlie | 19 | | 9 | David | 21 | | 10 | Eve | 23 | +----+---------+------+ 5 rows in set (0.00 sec)
-- 事务1
select * from students where id = 8 for update;

-- 事务2
INSERT INTO students (id, name, age) VALUES (6, 'Tim', 23); -- 阻塞，直到事务1提交

上面id=8的记录因为不存在，8落在5到9这个区间，所以这里会锁上(5,9)这个区间，阻塞insert操作。 如果锁住id=15的记录呢？

-- 事务1
select * from students where id = 15 for share;

-- 事务2
INSERT INTO students (id, name, age) VALUES (11, 'Tim', 23); -- 阻塞，直到事务1提交
INSERT INTO students (id, name, age) VALUES (16, 'Tim', 23); -- 阻塞，直到事务1提交
INSERT INTO students (id, name, age) VALUES (7, 'Tim', 23); -- OK

这种情况下，在大于id之后的就都被锁住了，而之前的记录不受影响。也就是锁住了(10, +∞)

间隙锁容易出现死锁的问题，比如 mysql> select * from students; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | Alice | 20 | | 3 | Bob | 22 | | 5 | Charlie | 19 | | 9 | David | 21 | | 10 | Eve | 23 | +----+---------+------+ 5 rows in set (0.00 sec)

-- 事务1
select * from students where id = 8 for share;

-- 事务2
select * from students where id = 7 for share;
INSERT INTO students (id, name, age) VALUES (8, 'Tim', 23);  -- 阻塞，因为事务1手上拿着锁

-- 事务1
INSERT INTO students (id, name, age) VALUES (6, 'Tim', 23); -- 死锁报错，锁被释放掉，事务2中的id=8的记录被插入
-- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  1. 临键锁（next key locks） 上面间隙锁锁的是一个开区间，比如(5,9)，他不影响两端（5和9）的读写操作。而这里，则可以锁住两头。比如下面select (5, 9]这个区间，所以锁住的也是这个区间。 mysql> select * from students; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | Alice | 20 | | 3 | Bob | 22 | | 5 | Charlie | 19 | | 9 | David | 21 | | 10 | Eve | 23 | +----+---------+------+ 5 rows in set (0.01 sec)
-- 事务1
select * from students where id > 5 and id <= 9 for update;

-- 事务2
select * from students where id = 9 for share;  -- 阻塞
select * from students where id = 5 for share;  -- OK
  1. 插入意向锁（insert intention locks） 在上面的间隙锁锁住区间的时候，此时如果去插入数据会阻塞，innodb会给这个等待插入的事务也分配一个锁，表示他有插入的意向。 在锁释放后，如果有多个事务有这个插入意向锁需要执行，这些事务如果不冲突的话当然能顺利执行，如果冲突的话，哪个先执行呢？这里老师没有说，我测试了下，好像是靠后面那个事务会先执行，但是不知道具体是什么策略。
-- 事务1
select * from students where id > 5 and id <= 9 for update;  -- (5,9]锁住

-- 事务2
INSERT INTO students (id, name, age) VALUES (6, 'Tim', 23); -- 阻塞

-- 事务3
INSERT INTO students (id, name, age) VALUES (6, 'Tim', 23); -- 阻塞

-- 事务1 提交 释放锁
-- 事务3会先执行，然后事务2继续阻塞，等待事务3提交后，事务2执行发现id是primary不能重复insert，冲突报错。
  • 页锁 一个表可能会有多个页的数据，所以粒度上来说，页锁是介于表锁和行锁之间的，性能上也同理。他也会出现死锁。 每个层级的锁数量是有限制的，因为锁会占用内存空间，锁空间大小是有限的。当某个层级的锁数量超过了这个层级的阈值，就会进行锁升级。 锁升级就是用更大粒度的锁替代多个更小粒度的锁，比如行锁升级为表锁，这样的好处是占用的锁空间降低了，但同时并发能力也下降了。

从对待锁的态度划分：乐观锁，悲观锁

这两种锁是两种看待数据并发的思维方式，这两者并不是锁，而是锁的设计思想。

  1. 悲观锁（Pessimistic Locking） 总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次都会上锁，这样别人想去拿数据就会阻塞。

案例：商场的秒杀活动 一般会涉及三个步骤：确认库存（select from），生成订单(insert into)，扣掉库存(update set) 这种情况就需要在确认库存的时候就应该上锁(select ... for update)，以避免超卖。

注意⚠️：select ... for update语句在执行过程中所有扫描的行都会被锁上，因此在mysql中用悲观锁必须确定使用了索引，而不是全表扫描，否则会把整个表锁住 悲观锁不适用的场景较多，它存在一些不足，因为它依靠数据的锁机制来实现，以保证程序的并发访问，同时对数据库性能开销影响也很大，特别是长事务而言，这样的开销往往无法接受，这时就需要乐观锁。

  1. 乐观锁（Optimistic Locking） 认为对同一数据的并发操作并不总发生，属于小概率事件，不用每次都上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。可以采用版本号机制或者cas机制实现。 所以他适用于读操作多的应用，这样可以提高吞吐量。
  • 版本号机制 表中设计一个version字段，第一次读的时候获取这个version，对数据修改的时候加上where version=version，每次修改都更改version，比如update set ..., version=version+1 where ... and version=version。如果在修改的时候，找不到对应数据，说明已经被别人修改掉了，那么需要重新对新version的数据来进行修改。

注意⚠️：如果是读写分离的情况，那么就得强制读取master表中的数据

如果对一条数据频繁的修改，那么就会出现一种场景，就是每次只有一个事务修改成功，在业务感知上出现大量失败的操作。这时也可以这样确保不会出现超卖：

update ... set quantity=quantity-num where id = ... and quantity-num > 0;

总结

  • 乐观锁适合于读多的场景，优点是通过程序实现，不存在死锁的问题
  • 悲观锁适合于写多的场景，因为写具有排他性，并发性就差点，但是能很好的防止读写冲突的问题

隐式锁和显式锁

一般来说，通过下面的命令能找到的都叫显式锁，通常比如通过特定语句进行加锁的（比如for update, for share）都叫显示锁

select * from performance_schema.data_lock_waits\G;

这条命令查不到的就是隐式锁。

插入一条数据一般并不加锁，而是通过一种隐式锁的结构来保护这条记录在事务结束前不被别的事务访问。 为什么说一般是不加锁的呢？如果一个事务加入一条记录，然后干点别的啥的就结束了，那么这种情况不加锁的。而如果在事务结束前另外的事务开启了想要来访问这条新插入的数据，这里的访问操作如果是读的话，那就是一个脏读，如果是一个写就是一个脏写，这就造成了并发访问的问题。因此这种情况下就会加锁，所以他是一个延迟加载的锁，当受到别的事务影响的时候才加，这就是一个隐式的行为。

场景1:对于聚簇索引记录来说，有一个trx_id隐藏列，记录着最后改动该记录的事务id。这时如果另外一个事务2要访问这条记录，发现事务1还活跃着，那么事务2会给事务1建立一个锁结构，事务1的is_waiting设置为false，事务2自己的设置为true。

场景2:对于二级索引记录来说，因为没有trx_id，所以页上的Page Header部分有一个PAGE_MAX_TRX_ID，该属性代表对该页面做改动的最大的事务id，如果这个值小于当前最小的活跃事务id，那么说明对该页面做修改的事务都提交了，否则就需要先找二级索引记录，然后回表，然后重复场景2

测试：

-- 先查看
mysql> select * from performance_schema.data_lock_waits\G;
Empty set (0.00 sec)

ERROR: 
No query specified
-- 事务1
begin;
INSERT INTO students (id, name, age) VALUES (6, 'Tim', 23);

-- 先查看发现还是啥也没有
mysql> select * from performance_schema.data_lock_waits\G;
Empty set (0.00 sec)

-- 事务2
begin;
select * from students for share; -- 阻塞，说明有锁

-- 再次查看，发现被创建了，这个时候其实能查到，就算是变成显式锁了
mysql> select * from performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 281472983434024:5:4:9:281472896399752
REQUESTING_ENGINE_TRANSACTION_ID: 562947960144680
            REQUESTING_THREAD_ID: 48
             REQUESTING_EVENT_ID: 28
REQUESTING_OBJECT_INSTANCE_BEGIN: 281472896399752
         BLOCKING_ENGINE_LOCK_ID: 281472983432408:5:4:9:281472896387184
  BLOCKING_ENGINE_TRANSACTION_ID: 3862
              BLOCKING_THREAD_ID: 48
               BLOCKING_EVENT_ID: 28
  BLOCKING_OBJECT_INSTANCE_BEGIN: 281472896387184
1 row in set (0.00 sec)

全局锁

锁整个数据库实例，让数据库处于只读状态。使用的典型场景是：做全库逻辑备份

flush tables with read lock;

死锁

-- 事务1
begin;
update students set age=10 where id = 1;

-- 事务2
begin;
update students set age=10 where id = 3;

-- 事务1
update students set age=10 where id = 3;  -- 阻塞，因为事务2拿着id=3的锁

-- 事务2
update students set age=10 where id = 1; -- 死锁报错。

出现死锁后，有两种策略

  • 进入等待，直到超时，超时时间可以通过innodb_lock_wait_timeout来设置，默认是50秒。如果是这种策略，第一个被锁住的线程要50秒才会超时退出，对很多在线服务来说是不可接受的。但是这个值又不能设置得太小，比如设置成1秒，如果没有出现死锁，他只是在正常的等待，那么这个时候让他超时，就算是误伤了。 mysql> show variables like 'innodb_lock_wait_timeout'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 50 | +--------------------------+-------+ 1 row in set (0.01 sec)

  • 另外一种就是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务可以继续执行。innodb_deadlock_detect设置为on，表示开启这个逻辑。默认是开启的。 mysql> show variables like 'innodb_deadlock_detect'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_deadlock_detect | ON | +------------------------+-------+ 1 row in set (0.00 sec) 这种策略的成本分析：每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是1000*1000=百万级的，这期间要消耗大量CPU资源，这时你就会看到CPU利用率很高。 办法1: 不用这种策略。业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁，就回滚，然后通过业务重试一般就没事了，对业务是无损的。如果不用这种策略，那就意味着会出现大量的超时，这对业务有损的。所以结论是，成本高点就高点。 办法2: 控制并发量，如果能控制比如一行最多只有10个线程在更新，那么死锁检测的成本就很低了。这个并发控制要在数据库服务端做，基本思路是，对于相同行的更新，在进入引擎之前排队，这样在innodb内部就不会有大量的死锁检测工作了。 还有一种思路就是，可以考虑将一行改成逻辑上多行来减少锁冲突。