加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。
锁是计算机协调多个进程或线程并发访问某一资源的机制。
数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。
锁是用于管理对公共资源的并发控制。也就是说在并发的情况下,会出现资源竞争,所以需要加锁。
加锁解决了 多用户环境下保证数据库完整性和一致性。
Lock的对象是事务,用来锁定的是数据库中的对象,如表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放(不同事务隔离级别释放的时间可能不同)。
锁的开销是较为昂贵的,锁策略其实就是保证了线程安全的同时获取最大的性能之间的平衡策略。
行锁:即只允许事务读一行数据。行锁的粒度实在每一条行数据,当然也带来了最大开销,但是行锁可以最大限度的支持并发处理。
开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
最大程度的支持并发,同时也带来了最大的锁开销。
行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统
在 InnoDB 中,除单个 SQL 组成的事务外,锁是逐步获得的,这就决定了在 InnoDB 中发生死锁是可能的。
行级锁只在存储引擎层实现,而Mysql服务器层没有实现。
表锁:允许事务在行级上的锁和表级上的锁同时存在。锁定整个表,开销最小,但是也阻塞了整个表。
开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
这些存储引擎通过总是一次性同时获取所有需要的锁以及总是按相同的顺序获取表锁来避免死锁。
表级锁更适合于以查询为主,并发用户少,只有少量按索引条件更新数据的应用,如Web 应用。
Mysql的表级别锁分为两类:元数据锁(Metadata Lock,MDL)、表锁。
元数据锁(MDL) 不需要显式使用,在访问一个表的时候会被自动加上。这个特性需要MySQL5.5版本以上才会支持,
页面锁(page-level locking)
页级锁定是 MySQL 中比较独特的一种锁定级别,在其他数据库管理软件中也并不是太常见。
页面锁开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力也同样是介于上面二者之间。另外,页级锁定和行级锁定一样,会发生死锁。
在数据库实现资源锁定的过程中,随着锁定资源颗粒度的减小,锁定相同数据量的数据所需要消耗的内存数量是越来越多的,实现算法也会越来越复杂。
不过,随着锁定资源颗粒度的减小,应用程序的访问请求遇到锁等待的可能性也会随之降低,系统整体并发度也随之提升。
使用页级锁定的主要是 BerkeleyDB 存储引擎。
MySQL 提供全局锁来对整个数据库实例加锁。
FLUSH TABLES WITH READ LOCK
这条语句一般都是用来备份的,当执行这条语句后,数据库所有打开的表都会被关闭,并且使用全局读锁锁定数据库的所有表,同时,其他线程的更新语句(增删改),数据定义语句(建表,修改表结构)和更新类的事务提交都会被阻塞。
在mysql 8.0 以后,对于备份,mysql可以直接使用备份锁。
LOCK INSTANCE FOR BACKUP UNLOCK INSTANCE
这个锁的作用范围更广,这个锁会阻止文件的创建,重命名,删除,包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及账户的管理都会被阻塞。当然这些操作对于内存临时表来说是可以执行的,为什么内存表不受这些限制呢?因为内存表不需要备份,所以也就没必要满足这些条件。
所有的存储引擎都以自己的方式显现了锁机制,服务器层完全不了解存储引擎中的锁实现:
默认情况下,表锁和行锁都是自动获得的, 不需要额外的命令。
但是在有的情况下, 用户需要明确地进行锁表或者进行事务的控制, 以便确保整个事务的完整性,这样就需要使用事务控制和锁定语句来完成。
InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。
Innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是,Innodb的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让Innodb的整体性能表现不仅不能比MyISAM高,甚至可能会更差。
InnoDB 实现了以下两种类型的行锁:
为了支持在不同粒度上进行加锁操作(允许行锁和表锁共存,实现多粒度锁机制),InnoDB 还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁:
由于InnoDB存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下所示
如果一个事务请求的锁模式与当前的锁兼容, InnoDB 就将请求的锁授予该事务; 反之, 如果两者不兼容,该事务就要等待锁释放
若将上锁的对象看成一棵树,那么对最下层的对象上锁,也就是对最细粒度的对象进行上锁,那么首先需要对粗粒度的对象上锁。例上图,如果需要对页上的记录r进行上X锁,那么分别需要对数据库A、表、页上意向锁IX,最后对记录r上X锁。若其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。举例来说,在对记录r加X锁之前,已经有事务对表1进行了S表锁,那么表1上已存在S锁,之后事务需要对记录r在表1上加上IX,由于不兼容,所以该事务需要等待表锁操作的完成。
innodb的意向锁主要用户多粒度的锁并存的情况。比如事务A要在一个表上加S锁,如果表中的一行已被事务B加了X锁,那么该锁的申请也应被阻塞。如果表中的数据很多,逐行检查锁标志的开销将很大,系统的性能将会受到影响。为了解决这个问题,可以在表级上引入新的锁类型来表示其所属行的加锁情况,这就引出了“意向锁”的概念。
举个例子,如果表中记录1亿,事务A把其中有几条记录上了行锁了,这时事务B需要给这个表加表级锁,如果没有意向锁的话,那就要去表中查找这一亿条记录是否上锁了。如果存在意向锁,那么假如事务A在更新一条记录之前,先加意向锁,再加X锁,事务B先检查该表上是否存在意向锁,存在的意向锁是否与自己准备加的锁冲突,如果有冲突,则等待直到事务A释放,而无须逐条记录去检测。事务B更新表时,其实无须知道到底哪一行被锁了,它只要知道反正有一行被锁了就行了。
主要作用是处理行锁和表锁之间的矛盾,能够显示“某个事务正在某一行上持有了锁,或者准备去持有锁”
行锁是加在索引上的
Innodb中的索引数据结构是 B+ 树,数据是有序排列的,从根节点到叶子节点一层层找到对应的数据。
普通索引,也叫做辅助索引,叶子节点存放的是主键值。主键上的索引叫做聚集索引,表里的每一条记录都存放在主键的叶子节点上。当通过辅助索引select 查询数据的时候,会先在辅助索引中找到对应的主键值,然后用主键值在聚集索引中找到该条记录。
举个例子,用name=Alice来查询的时候,会先找到对应的主键值是18 ,然后用18在下面的聚集索引中找到name=Alice的记录内容是 77 和 Alice。
表中每一行的数据,是组织存放在聚集索引中的,所以叫做索引组织表。
记录锁、间隙锁、临键锁都是排它锁
事务加锁后锁住的只是表的某一条记录。
记录锁出现条件:精准条件命中,并且命中的条件字段是唯一索引;
例如:update user_info set name=’张三’ where id=1 ,这里的id是唯一索引。
Record Lock总是会去锁住索引记录,如果InnoDB存储引擎表在建立的时候没有设置任何一个索引,那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。
记录锁的作用:加了记录锁之后可以避免数据在查询的时候被修改的重复读问题,也避免了在修改的事务未提交前被其他事务读取的脏读问题。
临键锁是INNODB的行锁默认算法,它是记录锁和间隙锁的组合,临键锁会把查询出来的记录锁住,同时也会把该范围查询内的所有间隙空间也会锁住,再之它会把相邻的下一个区间也会锁住。
临键锁出现条件:范围查询并命中,查询命中了索引。
比如下面表的数据执行 select * from user_info where id>1 and id<=13 for update ;
会锁住ID为 1,5,10的记录;同时会锁住,1至5,5至10,10至15的区间。
临键锁的作用:结合记录锁和间隙锁的特性,临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后,在范围区间内数据不允许被修改和插入。
Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法,在Next-Key Lock算法下,InnoDB对于行的查询都是采用这种锁定算法。
除了Next-Key Locking,还有Previous-Key Locking技术。
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁。
很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这往往会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。
以上情况,都会产生间隙锁
推荐阅读《MySQL的锁机制 - 记录锁、间隙锁、临键锁》
MySQL 通过 BINLOG 录入执行成功的 INSERT、UPDATE、DELETE 等更新数据的 SQL 语句,并由此实现 MySQL 数据库的恢复和主从复制。MySQL 的恢复机制(复制其实就是在 Slave Mysql 不断做基于 BINLOG 的恢复)有以下特点:
由此可见,MySQL 的恢复机制要求:在一个事务未提交前,其他并发事务不能插入满足其锁定条件的任何记录,也就是不允许出现幻读。
这张图里出现了三种锁
不是所有索引都会加上Next-key Lock的,在查询的列是唯一索引(包含主键索引)的情况下,Next-key Lock会降级为Record Lock。
CREATE TABLE z (a INT,b INT,PRIMARY KEY(a),KEY(b));// a是主键索引,b是普通索引
INSERT INTO z select1,1;
INSERT INTO z select3,1;
INSERT INTO z select5,3;
INSERT INTO z select7,6;
INSERT INTO z select10,8;
这时候在会话A中执行 SELECT*FROM z WHERE b=3FOR UPDATE ,索引锁定如下:
这时候会话B执行的语句落在锁定范围内的都会进行waiting
SELECT * FROM z WHERE a =5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4,2;
INSERT INTO z SELECT 6,5;
用户可以通过以下两种方式来显示的关闭Gap Lock:
从上面的例子可以看出来,Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内,设计它的目的是用来解决Phontom Problem(幻读问题)。在MySQL默认的隔离级别(Repeatable Read)下,InnoDB就是使用它来解决幻读问题。
InnoDB在事务执行过程中,使用两阶段锁协议:
select ... lock in share mode //共享锁
select ... for update //排他锁
在执行这个 select 查询语句的时候,会将对应的索引访问条目进行上排他锁(X 锁),也就是说这个语句对应的锁就相当于update带来的效果。
select *** for update 的使用场景:为了让自己查到的数据确保是最新数据,并且查到后的数据只允许自己来修改的时候,需要用到 for update 子句。
通过锁住聚集索引中的节点来锁住这条记录(锁住id=10的索引,即锁住了这条记录)。
这里的name上加了唯一索引,唯一索引本质上是辅助索引,加了唯一约束。所以会先在辅助索引上找到name为d的索引记录,在辅助索引中加锁,然后查找聚集索引,锁住对应索引记录。
试想一下,如果有并发的另外一个SQL,是直接通过主键索引id=30来更新,会先在聚集索引中请求加锁。如果只在辅助索引中加锁的话,两个并发SQL之间是互相感知不到的。
in share mode 子句的作用就是将查找到的数据加上一个 share 锁,这个就是表示其他的事务只能对这些数据进行简单的select 操作,并不能够进行 DML 操作。
select *** lock in share mode 使用场景:为了确保自己查到的数据没有被其他的事务正在修改,也就是说确保查到的数据是最新的数据,并且不允许其他人来修改数据。但是自己不一定能够修改数据,因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
前一个上的是排他锁(X 锁),一旦一个事务获取了这个锁,其他的事务是没法在这些数据上执行 for update ;后一个是共享锁,多个事务可以同时的对相同数据执行 lock in share mode。
select for update 语句,相当于一个 update 语句。在业务繁忙的情况下,如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待,从而影响数据库的并发使用效率。
select lock in share mode 语句是一个给查找的数据上一个共享锁(S 锁)的功能,它允许其他的事务也对该数据上S锁,但是不能够允许对该数据进行修改。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。
默认是 MVCC 机制(“一致性非锁定读-consistent nonlocking read”)保证 RR 级别的隔离正确性,是不上锁的。
可以选择手动上锁:select xxxx for update (排他锁); select xxxx lock in share mode(共享锁),称之为“一致性锁定读”。
使用锁之后,就能在 RR 级别下,避免幻读。当然,默认的 MVCC 读,也能避免幻读。
既然 RR 能够防止幻读,那么,SERIALIZABLE 有啥用呢?防止丢失更新。
这个时候,我们必须使用 SERIALIZABLE 级别进行串行读取。
最后,行锁的实现原理就是锁住聚集索引,如果你查询的时候,没有正确地击中索引,MySql 优化器将会抛弃行锁,使用表锁。
锁和多版本数据(MVCC)是 InnoDB 实现一致性读和 ISO/ANSI SQL92 隔离级别的手段。
因此,在不同的隔离级别下,InnoDB 处理 SQL 时采用的一致性读策略和需要的锁是不同的:
对于许多 SQL,隔离级别越高,InnoDB 给记录集加的锁就越严格(尤其是使用范围条件的时候),产生锁冲突的可能性也就越高,从而对并发性事务处理性能的 影响也就越大。
因此, 我们在应用中, 应该尽量使用较低的隔离级别, 以减少锁争用的机率。实际上,通过优化事务逻辑,大部分应用使用 Read Commited 隔离级别就足够了。对于一些确实需要更高隔离级别的事务, 可以通过在程序中执行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 动态改变隔离级别的方式满足需求。
合理利用 InnoDB 的行级锁定,做到扬长避短
对于 InnoDB 表,在绝大部分情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择 InnoDB 表的理由。
事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅这个事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突,这种情况下可以考虑使用表锁来提高该事务的执行速度。
事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表,从而避免死锁、减少数据库因事务回滚带来的开销。
在 InnoDB 下,使用表锁要注意以下两点:
正确的方式见如下语句,例如,如果需要写表 t1 并从表 t 读,可以按如下做:
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;
MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后, 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待,直到锁被释放为止。
默认情况下,写锁比读锁具有更高的优先级:当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求。 (This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.)。
这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。同时,一些需要长时间运行的查询操作,也会使写线程“饿死” ,应用中应尽量避免出现长时间运行的查询操作(在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行)。
可以设置改变读锁和写锁的优先级:
这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。
MyISAM存储引擎支持并发插入,以减少给定表的读和写操作之间的争用:
如果MyISAM表在数据文件中间没有空闲块,则行始终插入数据文件的末尾。 在这种情况下,你可以自由混合并发使用MyISAM表的INSERT和SELECT语句而不需要加锁——你可以在其他线程进行读操作的时候,同时将行插入到MyISAM表中。 文件中间的空闲块可能是从表格中间删除或更新的行而产生的。 如果文件中间有空闲快,则并发插入会被禁用,但是当所有空闲块都填充有新数据时,它又会自动重新启用。 要控制此行为,可以使用MySQL的concurrent_insert系统变量。
如果你使用LOCK TABLES显式获取表锁,则可以请求READ LOCAL锁而不是READ锁,以便在锁定表时,其他会话可以使用并发插入。
显示锁sql语句
MyISAM 存储引擎有一个控制是否打开 Concurrent Insert 功能的参数选项:
可以利用 MyISAM 存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。
例如,将 concurrent_insert 系统变量设为 2,总是允许并发插入;同时,通过定期在系统空闲时段执行 OPTIMIZE TABLE 语句来整理空间碎片,收回因删除记录而产生的中间空洞。
在自动加锁的情况下,MyISAM 表不会出现死锁(MyISAM 总是一次获得 SQL 语句所需要的全部锁)。
如果出现死锁,可以用 SHOW INNODB STATUS 命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息,如引发死锁的 SQL 语句,事务已经获得的锁,正在等待什么锁,以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
下面两条简单的SQL,他们加什么锁?
select * from t1 where id = 10
delete from t1 where id = 10
如果要分析加锁情况,必须还要知道以下的一些前提,前提不同,加锁处理的方式也不同
根据上述情况,有以下几种组合
排列组合还没有列举完全,但是看起来,已经很多了。真的有必要这么复杂吗?事实上,要分析加锁,就是需要这么复杂。但是从另一个角度来说,只要你选定了一种组合,SQL需要加哪些锁,其实也就确定了。接下来挑几个比较经典的组合
这个组合,是最简单,最容易分析的组合。id是主键,Read Committed隔离级别,给定SQL:delete from t1 where id = 10; 只需要将主键上,id = 10的记录加上X锁即可。如下图1:
结论:id是主键时,此SQL只需要在id=10这条记录上加X锁即可。
这个组合,id不是主键,而是一个Unique的二级索引键值。那么在RC隔离级别下,delete from t1 where id = 10; 需要加什么锁呢?见下图2:
id是unique索引,而主键是name列。此时,加锁的情况由于组合一有所不同。由于id是unique索引,因此delete语句会选择走id列的索引进行where条件的过滤,在找到id=10的记录后,首先会将unique索引上的id=10索引记录加上X锁,同时,会根据读取到的name列,回主键索引(聚簇索引),然后将聚簇索引上的name = ‘d’ 对应的主键索引项加X锁。
结论:若id列是unique列,其上有unique索引。那么SQL需要加两个X锁,一个对应于id unique索引上的id = 10的记录,另一把锁对应于聚簇索引上的[name='d',id=10]的记录、
相对于组合一、二,组合三又发生了变化,隔离级别仍旧是RC不变,但是id列上的约束又降低了,id列不再唯一,只有一个普通的索引。假设delete from t1 where id = 10; 语句,仍旧选择id列上的索引进行过滤where条件,那么此时会持有哪些锁?同样见下图3:根据此图,可以看到,首先,id列索引上,满足id = 10查询条件的记录,均已加锁。同时,这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于,组合二最多只有一个满足等值查询的记录,而组合三会将所有满足查询条件的记录都加锁。
结论:若id列上有非唯一索引,那么对应的所有满足SQL查询条件的记录,都会被加锁。同时,这些记录在主键索引上的记录,也会被加锁。
还记得前面提到的MySQL的四种隔离级别的区别吗?RC隔离级别允许幻读,而RR隔离级别,不允许存在幻读。但是在组合五、组合六中,加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下, 组合七,Repeatable Read隔离级别,id上有一个非唯一索引,执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤,最后的加锁行为,是怎么样的呢?同样看下图1:
结论:Repeatable Read隔离级别下,id列上有一个非唯一索引,对应SQL:delete from t1 where id = 10; 首先,通过id索引定位到第一条满足查询条件的记录,加记录上的X锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁,然后返回;然后读取下一条,重复进行。直至进行到第一条不满足条件的记录[11,f],此时,不需要加记录X锁,但是仍旧需要加GAP锁,最后返回结束。 什么时候会取得gap lock或nextkey lock 这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。
相对于前面三个组合,这是一个比较特殊的情况。id列上没有索引,where id = 10;这个过滤条件,没法通过索引进行过滤,那么只能走全表扫描做过滤。对应于这个组合,SQL会加什么锁?或者是换句话说,全表扫描时,会加什么锁?这个答案也有很多:有人说会在表上加X锁;有人说会将聚簇索引上,选择出来的id = 10;的记录加上X锁。那么实际情况呢?请看下图2:
由于id列上没有索引,因此只能走聚簇索引,进行全部扫描。从图中可以看到,满足删除条件的记录有两条,但是,聚簇索引上所有的记录,都被加上了X锁。无论记录是否满足条件,全部被加上X锁。既不是加表锁,也不是在满足条件的记录上加行锁。 有人可能会问?为什么不是只在满足条件的记录上加锁呢?这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL Server层进行过滤。因此也就把所有的记录,都锁上了。
结论:若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上X锁。但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。同时,优化也违背了2PL的约束。
组合八,Repeatable Read隔离级别下的最后一种情况,id列上没有索引。此时SQL:delete from t1 where id = 10; 没有其他的路径可以选择,只能进行全表扫描。最终的加锁情况,图3所示:
结论:在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作。当然,也可以通过触发semi-consistent read,来缓解加锁开销与并发影响,但是semi-consistent read本身也会带来其他问题,不建议使用。
上面的四个组合,都是在Read Committed隔离级别下的加锁行为,接下来的四个组合,是在Repeatable Read隔离级别下的加锁行为。 组合五,id列是主键列,Repeatable Read隔离级别,针对delete from t1 where id = 10; 这条SQL,加锁与组合一:[id主键,Read Committed]一致。
与组合五类似,组合六的加锁,与组合二:[id唯一索引,Read Committed]一致。两个X锁,id唯一索引满足条件的记录上一个,对应的聚簇索引上的记录一个。
针对前面提到的简单的SQL,最后一个情况:Serializable隔离级别。对于SQL2:delete from t1 where id = 10; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致,因此不做介绍。 Serializable隔离级别,影响的是SQL1:select * from t1 where id = 10; 这条SQL,在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC。
结论:在MySQL/InnoDB中,所谓的读不加锁,并不适用于所有的情况,而是隔离级别相关的。Serializable隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。
这种情况很好理解,事务A和事务B操作两张表,但出现循环等待锁情况。
这种情况比较常见,之前遇到两个job在执行数据批量更新时,jobA处理的的id列表为[1,2,3,4],而job处理的id列表为[8,9,10,4,2],这样就造成了死锁。
这种情况比较隐晦,事务A在执行时,除了在二级索引加锁外,还会在聚簇索引上加锁,在聚簇索引上加锁的顺序是[1,4,2,3,5],而事务B执行时,只在聚簇索引上加锁,加锁顺序是[1,2,3,4,5],这样就造成了死锁的可能性。
innodb在RR级别下,如下的情况也会产生死锁,比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。
结论:在MySQL/InnoDB中,所谓的读不加锁,并不适用于所有的情况,而是隔离级别相关的。Serializable隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。
相关链接:
MySQL锁总结 https://zhuanlan.zhihu.com/p/29150809
MySQL锁机制——你想知道的都在这了! https://zhuanlan.zhihu.com/p/75673270
详解mysql的各种锁(表锁、行锁、共享锁、意向共享锁、记录锁、间隙锁、临键锁) https://zhuanlan.zhihu.com/p/52312376
对于MySQL你必须要了解的锁知识 https://zhuanlan.zhihu.com/p/62525459
mysql锁机制总结,以及优化建议 https://zhuanlan.zhihu.com/p/70889229
深入理解MySQL――锁、事务与并发控制 这才是正确的! https://zhuanlan.zhihu.com/p/36060546
深入理解MySQL锁 https://zhuanlan.zhihu.com/p/8355298
MySQL(Innodb)索引的原理 https://zhuanlan.zhihu.com/p/62018452
数据库两大神器【索引和锁】 https://zhuanlan.zhihu.com/p/40396971
深入理解MySQL锁 https://zhuanlan.zhihu.com/p/83552985
原文:再谈mysql锁机制及原理-锁的诠释 - mysql - 周陆军的个人网站 修改更新只在原文,文有不妥之处,请留言告知。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。