一文总结MySQL各种锁

概述

对于后端Java开发人员来说，锁主要有Java锁和DB锁。Java锁，请参考一文总结Java开发各种锁。本文所述的DB锁，可能会局限于MySQL数据库。

隔离级别与锁的关系

  • 在RU级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
  • 在RC级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁
  • 在RR级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁
  • SERIALIZABLE是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成

行锁，表锁，页锁

锁粒度：

  • 表锁：table-level locking，MYISAM引擎，开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突概率高，并发度低；
  • 行锁：row-level locking，INNODB引擎，开销大，加锁慢；会出现死锁；锁粒度小，发生锁冲突的概率低，并发度高；
  • 页锁：page-level locking，BDB引擎，锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。取折衷的页级，一次锁定相邻的一组记录。开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

不同的存储引擎支持的锁粒度是不一样的：

  • InnoDB行锁和表锁都支持，默认为行级锁
  • MyISAM只支持表锁

表锁

表级锁，即锁定整张表。在对表进行插入、更新、删除操作时，需要先获得写锁，锁定整张表，其它读写操作将会被阻塞。读锁之间是不会阻塞的。alter table操作会使用表级锁，所以对数据量很大的表进行alter table操作时，需要谨慎。

行锁

即只锁定数据所在的行，行级锁只在存储引擎层实现。行级锁可以很好的支持并发处理，但是也会导致大量的资源开销。

查看行锁相关的系统变量，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：系统启动后到现在总共等待锁的次数

当索引失效（如类型转换）时，行锁会升级成表锁。

乐观锁、悲观锁

乐观锁，是一种思想，而不是数据库层面上的锁，是需要自己手动去加的锁。通过版本号（时间戳），加字段或CAS算法方式实现。

共享锁和排它锁是悲观锁的不同的实现，都是行级锁。
要使用悲观锁，需要关闭MySQL数据库的自动提交属性，因为MySQL默认使用autocommit模式，即执行一个更新操作后，MySQL会立刻将结果进行提交。
设置MySQL为非autocommit模式：set autocommit=0;

共享锁、排它锁

共享锁

share lock，也叫read lock，S锁，读锁。读取操作创建的锁。共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。

其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据。

在查询语句后面加上lock in share mode，MySQL会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用共享锁的表，而且这些线程读取的是同一个版本的数据。
加上共享锁后，对于update,insert,delete语句会自动加排它锁。

排它锁

exclusive lock，也叫writer lock，X锁，写锁。排它锁指对于多个不同的事务，对同一个资源只能有一把锁。若事务1对数据对象A加上X锁，事务1 可以读A也可以修改A，其他事务不能再对A加任何锁，直到事务1 释放A上的锁。这保证其他事务在事务1释放A上的锁之前不能再读取和修改A。排它锁会阻塞所有的排它锁和共享锁。

读取为什么要加读锁呢：防止数据在被读取的时候被别的线程加上写锁。

对于update, insert, delete语句会自动给涉及到的数据集加排它锁。执行语句后面加上for update就可以。
执行事务时关键字select…for update会锁定数据，防止其他事务更改数据。但是锁定数据也是有规则的。查询条件与锁定范围：

  1. 具体的主键值为查询条件
    比如查询条件为主键ID=1等等，如果此条数据存在，则锁定当前行数据，如果不存在，则不锁定。
  2. 不具体的主键值为查询条件
    比如查询条件为主键ID>1等等，此时会锁定整张数据表。
  3. 查询条件中无主键
    会锁定整张数据表。
  4. 如果查询条件中使用索引为查询条件
    明确指定索引并且查到，则锁定整条数据。如果找不到指定索引数据，则不加锁。

总结

行锁又分共享锁和排他锁。（当然，说的是InnoDB引擎）行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

行级锁的缺点：由于需要请求大量的锁资源，速度慢，内存消耗大。

表锁下又分为两种模式：表读锁（表共享读锁，Table Read Lock）、表写锁（表独占写锁，Table Write Lock）
在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞。读锁和写锁是互斥的，读写操作是串行。
如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在MySQL里边，写锁是优先于读锁的。写锁和读锁优先级，可以通过参数调节的：max_write_lock_count和low-priority-updates。

InnoDB引擎的行锁是怎么实现的？
InnoDB是基于索引来完成行锁，select * from tab_with_index where id = 1 for update;，for update可根据条件来完成行锁锁定，要求id是有索引键的列。如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起。

InnoDB存储引擎的锁的算法有三种

  • Record lock：单个行记录上的锁
  • Gap lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

  1. innodb对于行的查询使用next-key lock
  2. Next-locking keying为了解决Phantom Problem幻读问题
  3. 当查询的索引含有唯一属性时，将next-key lock降级为record key
  4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
  5. 两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

三种锁

Record Lock

记录锁，也叫行锁，封锁查询条件里指定的某条记录，是排他锁。

GAP Lock

间隙锁，当用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做间隙，GAP。比如有id为：1、3、5的3条数据，查找1-5范围的数据，则1-5都会被加上锁。2、4也在1-5的范围中，但是不存在这些数据记录，2、4就被称为间隙。InnoDB对间隙加锁，即间隙锁。间隙锁只能用于RR隔离级别。

间隙锁的使用场景：

  1. 解决幻读：Repeatable Read隔离级别下再通过GAP锁即可避免幻读
  2. 满足恢复和复制的需要

间隙锁的危害
范围查找时，会把整个范围的数据全部锁定住，即便这个范围内不存在的一些数据，也会被无辜的锁定住，比如我要在1、3、5中插入2，这个时候1-5都被锁定，根本无法插入2。在某些场景下会对性能产生很大的影响

MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

Next-key lock

译为临键锁，记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间，可防止任何会更改索引值位置的插入或更新。

可用于解决幻读和不可重复读。为了防止幻读，临键锁阻止特定条件的新记录的插入，因为插入时要获取插入意向锁，与已持有的临键锁冲突。

如果把事务的隔离级别降级为RC，临键锁则也会失效。

临键锁比间隙锁更严格，并发性能低一些。

读写锁

读锁，Locking read，select加锁主要有2种：

  • select ... for update
  • select ... lock in share mode

select * from test where id > 3 and name <'A' for update;，将所有id>3的记录进行加锁，而不是对范围id>3 and name <'A'进行加锁，name字段没有索引。

UPDATE及DELETE语句通常会对通过索引扫描的记录加上next-key locks，忽略没有用到索引的那部分where语句。

如果一个SQL通过二级索引访问表记录，并且在二级索引上设置一个锁，那innodb将会在对应的聚簇索引(主键)记录上也加上一把锁。

如果一个SQL语句无法通过索引进行Locking read，UPDATE，DELETE，那MySQL将扫描整个表，表中的每一行都将被锁定（在RC级别，通过semi-consistent read，能够提前释放不符合条件的记录；在RR级别，需设置innodb_locks_unsafe_for_binlog=1，才能打开semi-consistent read）。在某些场景下，锁也不会立即被释放。如union查询，生成一张临时表，导致临时表的行记录和原始表的行记录丢失联系，只能等待查询执行结束才能释放。

SELECT ... FROM是快照读，读取数据的快照，不会加任何锁（RU，RC，RR等3种隔离级别）。但是将隔离级别设置成SERIALIZABLE后，如果索引是非唯一索引，那么将在相应的记录上加上一个共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
SELECT ... FROM ... LOCK IN SHARE MODE在所有索引扫描范围的索引记录上加共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
SELECT ... FROM ... FOR UPDATE语句在所有索引扫描范围的索引记录上加排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。这将阻塞其他会话利用SELECT ... FROM ... LOCK IN SHARE MODE 读取相同的记录，但是快照读将忽略记录上的锁。

UPDATE ... WHERE ...语句在所有索引扫描范围的记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加行锁record lock。
当UPDATE 操作修改主键记录的时候，将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候，将会在插入新的二级索引记录之前，在其二级索引上加上一把共享锁。
DELETE FROM ... WHERE ... 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加行锁record lock。
INSERT ... ON DUPLICATE KEY UPDATE 和普通的INSERT并不相同。如果碰到重复键值，INSERT ... ON DUPLICATE KEY UPDATE 将在记录上加排他的next-key锁。
REPLACE 在没有碰到重复键值的时候和普通的INSERT是一样的，如果碰到重复键，将在记录上加一个排他的next-key锁。
INSERT INTO T SELECT ... FROM S WHERE ... 语句在插入T表的每条记录上加上 index record lock 。如果隔离级别是 READ COMMITTED，或启用innodb_locks_unsafe_for_binlog且事务隔离级别不是SERIALIZABLE，那么innodb将通过快照读取表S(no locks)。否则，innodb将在S表的记录上加共享的next-key锁。
CREATE TABLE ... SELECT ... 和INSERT INTO T SELECT ... FROM S WHERE ...一样，在S上加共享的next-key锁或者进行快照读取（(no locks))
REPLACE INTO t SELECT ... FROM s WHERE ... 和UPDATE t ... WHERE col IN (SELECT ... FROM s ...)中的select部分将在表s上加共享的next-key锁。

当碰到有自增列的表的时候，innodb在自增列的索引最后面加上一个排他锁，叫AUTO-INC table lock。AUTO-INC table lock会在语句执行完成后进行释放，而不是事务结束。如果AUTO-INC table lock被一个会话占有，那么其他会话将无法在该表中插入数据。innodb可以预先获取sql需要多少自增的大小，而不需要去申请锁，参考参数innodb_autoinc_lock_mode。

如果一张表的外键约束被启用了，任何在该表上的插入、更新、删除都将需要加共享的 record-level locks来检查是否满足约束。如果约束检查失败，innodb也会加上共享的 record-level locks。

lock tables 是用来加表级锁，它是由MySQL的server层来加这把锁的。当innodb_table_locks = 1 (the default) 以及 autocommit = 0的时候，innodb能够感知表锁，同时server层了解到innodb已经加了row-level locks。否则，innodb将无法自动检测到死锁，同时server无法确定是否有行级锁，导致当其他会话占用行级锁的时候还能获得表锁。

INSERT 语句会对插入成功的记录上加排他锁，这个锁是一个行锁 index-record lock，并不是next-key锁，因此就没有gap锁，它将不会阻止其他会话在该条记录之前的gap插入记录。

在插入记录之前，将会加上一种叫 insert intention gap 的 gap 锁。这个 insert intention gap表示它有意向在这个index gap插入记录，如果其他会话在这个index gap中插入的位置不相同，那么将不需要等待。假设存在索引记录4和7，会话A要插入记录5，会话B要插入记录6，每个会话在插入记录之前都需要锁定4和7之间gap，但是他们彼此不会互相堵塞，因为插入的位置不相同。

如果出现唯一键冲突，将在重复键上加一个共享锁。如果会话1插入一条记录，没有提交，他会在该记录上加上排他锁，会话2和会话3都尝试插入该重复记录，那么他们都会被堵塞，会话2和会话3将尝试在该记录上申请加一个共享锁。如果此时会话1回滚，将发生死锁。

在唯一索引（unique index）上使用唯一的查询条件（unique search condition），会使用记录锁(record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁（gap lock）与临键锁（next-key lock）；
其他的查询条件和索引条件，InnoDB会封锁被扫描的索引范围，并使用间隙锁与临键锁，避免索引范围区间插入记录；

和加锁select类似，如果在唯一索引上使用唯一的查询条件执行update/delete，如update t set name=xxx where id=1;也只加记录锁；

否则，符合查询条件的索引记录之前，都会加排他临键锁(exclusive next-key lock)，来封锁索引记录与之前的区间；
如果update的是聚集索引(clustered index)记录，则对应的普通索引(secondary index)记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的：普通索引存储PK的值，检索普通索引本质上要二次扫描聚集索引。

insert和update与delete不同，用排它锁封锁被插入的索引记录，而不会封锁记录之前的范围。同时，会在插入区间加插入意向锁(insert intention lock)，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY插入。

死锁

死锁是指多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。数据库系统为了解决这个问题，实现死锁检测和死锁超时机制。在MySQL的InnoDB存储引擎中，解决死锁的方法是将持有最少行级排它锁的事务进行回滚。

常见的解决死锁的方法：

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

当有死锁发生时，通常是由于项目的程序中出现冗长的事务，或是由于隔离级别设置的不合适等。在事务使用中注意以下几点：

  1. 尽量保持事务的短小精悍，做出一系列关联的更新操作后立即提交事务，以降低死锁的可能性。特别是不要让有关联的MySQL会话长时间挂起未提交的事务
  2. 建议使用更低的隔离级别，如READ COMMITTED
  3. 在同一事务内修改多张表，或一张表内的不同行时，每次以相同的顺序执行操作。以便让事务形成清晰的锁操作队列而规避死锁

死锁检测

通过SQL语句查询锁表相关信息：

  1. 查询表打开情况：SHOW OPEN TABLES WHERE IN_USE> 0
  2. 查询锁情况列表：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS
  3. 查询锁等待信息，其中blocking_lock_id是当前事务在等待的事务：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS
  4. 查询死锁日志：SHOW ENGINE INNODB STATUS
    这条语句只能显示最新的一条死锁，无法完全捕获到系统发生的所有死锁信息。
    如果想要记录所有的死锁日志，需要打开innodb_print_all_deadlocks参数，将所有的死锁日志记录到errorlog中。
  5. 查询锁等待时间：SHOW STATUS LIKE '%lock%'

MySQL数据库通过死锁检测innodb_deadlock_detect和死锁超时时间innodb_lock_wait_timeout两个参数来进行死锁解决。

死锁检测：在MySQL 8.0中，增加一个新的动态变量innodb_deadlock_detect，用来控制InnoDB是否执行死锁检测。该参数的默认值为ON，即打开死锁检测。开启后InnoDB在加锁时会检测加锁后是否会造成死锁，如果会加锁，就回滚代价最小的那一个事务。
死锁超时时间：这个参数可以用来处理检测不出来的死锁，或是避免长时间等待较长的事务的情况。

对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果禁用死锁检测，而改为依靠参数innodb_lock_wait_timeout来释放长时间占用锁资源的事务可能会更加高效。即，在确认死锁检测功能影响系统的性能且禁用死锁检测不会带来负面影响时，可尝试关闭innodb_deadlock_detect选项。另外，禁用InnoDB死锁检测，需要及时调整参数innodb_lock_wait_timeout值，以满足实际的需求。

InnoDB存储引擎可以检测到死锁的循环依赖，并立即返回错误。

外部锁的死锁检测：InnoDB不能完全自动检测死锁。发生死锁后，InnoDB一般自动检查，一个事务释放锁后，另一个事务获得锁，继续完成事务。但是，如果涉及外部锁或表部锁，InnoDB不能完全自动检测死锁，则需要设置锁等待超时参数innodb_lock_wait_timeout来解决。

如果业务处理不好可以用分布式事务锁或乐观锁

总结

在这里插入图片描述

  1. MyISAM存储引擎执行SQL语句自动加锁。查询语句给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT等）给涉及的表加写锁，这个过程并不需要用户干预；
  2. 乐观锁其实是一种思想；
  3. 悲观锁用的就是数据库的行锁；

参考

posted @ 2024-08-21 17:25  johnny233  阅读(23)  评论(0)  编辑  收藏  举报