【MySQL】锁

基础

锁是计算机协调多个进程或线程并发访问某一资源的机制避免争抢。在数据库中除传统的计算资源如CPU、RAM、I/O等的争用以外数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。

相对其他数据库而言MySQL的锁机制比较简单其最显著的特点是不同的存储引擎支持不同的锁机制。从操作数据的角度可以把锁分为

• 表级锁
  • 操作数据时会锁定整个表。
  • 开销小加锁快不会出现死锁锁定粒度大发生锁冲突的概率最高并发度最低。
  • MyISAM和Memory存储引擎使用表级锁。
• 行级锁
  • 操作数据时会锁定当前数据行。
  • 开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度也最高。
  • InnoDB存储引擎默认使用行级锁。
• 页面锁
  • 操作数据是会锁定当前数据所在的页面。
  • 开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间并发度一般。
  • BDB存储引擎采用页面锁。

不同存储引擎对锁的支持情况如下表所示

存储引擎	表级锁	页面锁	行级锁
MyISAM	支持	不支持	不支持
InnoDB	支持	不支持	支持
MEMORY	支持	不支持	不支持
BDB	支持	支持	不支持

从锁的角度来说表级锁更适合于以查询为主只有少量按索引条件更新数据的应用如Web应用而行级锁则更适合于有大量按索引条件并发更新数据同时又有查询的应用如一些在线事务处理系统。

MyISAM

MyISAM存储引擎只支持表锁Table Lock可以分为读锁表读锁Table Read Lock和写锁表写锁Table Write Lock。

MyISAM的读锁和写锁的相互兼容性如下表所示

当前锁模式/请求锁模式	读锁	写锁
读锁	是	否
写锁	否	否

解释

• 对MyISAM表的读操作加读锁不会阻塞其他用户对同一表的读请求但会阻塞其他用户对同一表的写请求
• 对MyISAM表的写操作加写锁不会阻塞当前用户对同一表的读和写请求但会阻塞其他用户对同一表的读和写请求

简单来说MyISAM表的读操作和写操作之间以及写操作之间是串行的。当一个线程获得对一个表的写锁后只有持有锁的线程可以对表进行更新操作其他线程的读、写操作都会等待直到锁被释放为止。

表锁

MyISAM存储引擎在执行查询语句SELECT前会自动给涉及的所有表加读锁在执行更新操作UPDATE、DELETE、INSERT 等前会自动给涉及的所有表加写锁。加锁的过程对于用户是透明的不需要用户干预。

当然用户也可以在操作数据之前显式地加锁

# 加读锁
lock table table_name read;
# 加写锁
lock table table_name write

注意

• 显式加锁一般是为了方便说明问题比如模拟事务操作实现对某一时间点的一致性读取。
• 显式加锁时必须同时取得所有涉及表的锁。也就是说在执行LOCK TABLE后只能访问显式加锁的这些表不能访问未加锁的表。在自动加锁的情况下也是如此MyISAM总是一次获得SQL语句所需要的全部锁这也正是MyISAM不会出现死锁的原因。

并发插入

MyISAM的读锁和写锁是互斥的所以MyISAM表的读和写操作是串行的。但是MyISAM存储引擎也允许设置一个系统变量concurrent_insert来控制并发插入的行为。

• concurrent_insert=0不允许并发插入。
• concurrent_insert=1在MyISAM表没有空洞表的中间没有被删除的行的情况下MyISAM允许在一个进程读取表的同时另一个进程从表尾部插入记录。这是MySQL的默认设置。
• concurrent_insert=2无论MyISAM表有没有空洞都允许在表尾部并发插入记录。

锁调度策略

MyISAM的读写锁调度的策略是写锁优先。如果一个进程请求某个MyISAM表的读锁同时另一个进程也请求同一个表的写锁那么写进程会先获得锁。即使读请求先到锁等待队列写请求后到写请求也会先获得到锁。

这是因为MySQL认为写请求一般比读请求更重要。这也正是MyISAM不适合做写为主的表的存储引擎的原因。因为如果更新操作较多会使得查询操作很难获得读锁从而产生严重的锁等待导致查询操作被阻塞、查询效率低。

InnoDB

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-transaction-model.html

InnoDB与MyISAM的最大不同有两点一是支持事务二是采用了行锁。行锁开销大加锁慢会出现死锁锁定粒度最小发生锁冲突的概率最低并发度也最高。InnoDB默认使用行锁。

事务

参考InnoDB-事务一节。

多个事务并发运行能大大增加数据库资源的利用率提高数据库系统的事务的吞吐量从而可以支持更多的用户。但是因为并发事务处理可能操作相同的数据会导致一些问题

• 修改丢失Lost to modify指一个事务修改了一个数据后还没有进行提交此时另外一个事务也修改了这个数据那么第一个事务的修改就丢失了。这种情况称为修改丢失。
• 脏读Dirty read指一个事务修改了一个数据后还没有进行提交此时另外一个事务使用了这个修改过的数据。因为第一个事务还没有提交我们可以认为第二个事务读到的数据是“脏数据”。这种情况称为脏读。
• 不可重复读Unrepeatable read指在一个事务内多次读取同一个数据的结果不一样。如果在一个事务两次读取一个数据之间另一个事务对这个数据进行了修改导致第一个事务两次读取的结果不一样。这种情况称为不可重复读。
• 幻读Phantom read幻读与不可重复读类似是指一个事务以一定的查询条件读取了一些数据之后按相同的查询条件发现多了一些原本不存在的记录这是由于其他并发事务插入了满足该查询条件的新数据。这种情况称为幻读。

不可重复读和幻读的区别不可重复读的重点是数据修改比如多次读取一条记录发现其中某些列的值被修改幻读的重点是数据增加或者删除比如多次读取某种条件的数据发现记录增加或减少了。

并发事务

在上面的问题中“修改丢失”通常是应该完全避免的。但是防止丢失修改不能单靠数据库事务控制来解决更需要应用程序加必要的锁来解决。而“脏读”、“不可重复读”和“幻读”其实都是数据库一致性问题必须由数据库系统提供一定的事务隔离机制来解决。

数据库系统实现事务隔离的方式基本上可以分为两种

• 锁在读取数据前对其加锁阻止其他事务对数据进行修改。
• 多版本并发控制Multi-Version Concurrency Control简称MVCCMVCC不需要对数据加锁而是通过一定机制在一个数据请求时间点生成一个一致性数据快照snapshot并用这个快照来提供一定级别语句级或事务级的一致性读取。从用户的角度来看好像数据库可以提供同一个数据的多个版本。

数据库的事务隔离越严格并发副作用越小但付出的代价也就越大。事务隔离实质上就是使用事务在一定程度上“串行化” 进行这显然与“并发” 是矛盾的。

为了解决“隔离”与“并发”的矛盾ISO/ANSI SQL92标准定义了四种隔离级别每个级别的隔离程度不同允许出现的副作用也不同。隔离级别从低到到高分别是

• 读未提交Read Uncommitted最低的隔离级别允许事务读取其他事没有提交的数据修改可能会导致脏读、幻读或不可重复读。
• 读已提交Read Committed允许事务读取其他事务已经提交的数据修改可以防止脏读但是可能会导致不可重复读和幻读。
• 可重复读Repeatable ReadMySQL默认的隔离级别。在一个事务中多次读取同一个数据的结果是一致的除非是自己进行修改。该级别可以防止脏读和不可重复读但是可能会导致幻读。
• 可串行化Serializable最高的隔离级别完全服从ACID的隔离级别。所有的事务依次逐个执行事务之间就完全不可能产生干扰。该级别可以防止脏读、不可重复读以及幻读。

不同隔离级别的特性如下

隔离级别	读数据一致性	脏读	不可重复读	幻读
读未提交	最低级别	是	是	是
读已提交	语句级	否	是	是
可重复读	事务级	否	否	是
可串行化	最高级别事务级	否	否	否

需要说明的是不同数据库系统不一定完全实现了上述4个隔离级别。例如Oracle只提供Read Committed和Serializable两个标准隔离级别另外还提供自己定义的Read Only隔离级别。MySQL支持全部4个隔离级别但在具体实现和应用时有很多需要注意的地方。

比如InnoDB存储引擎默认的隔离级别是Repeatable Read可以查看tx_isolation变量

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

由于使用了间隙锁还可以避免幻读问题所以InnoDB默认的隔离级别已经达到了Serializable隔离级别的要求。

行锁

InnoDB存储引擎实现了标准的的行级锁Row-level Locking有两种类型

• 共享锁Shared Lock简称S允许持有锁的事务读取一行。共享锁也可以称为读锁。
• 排他锁Exclusive Lock简称X允许持有锁的事务更新或删除一行。排他锁也可以称为写锁。

如果事务 T1 在行 r 上持有共享锁则来自某个不同事务 T2 的请求在行 r 上的锁将按如下方式处理

• 可以立即授予 T2 的共享锁请求。 结果T1 和 T2 都持有 r 上的 S 锁。
• 不能立即授予 T2 对排他锁的请求。

如果事务 T1 在行 r 上持有排他锁则无法授予来自某个不同事务 T2 对 r 上任一类型锁的请求。相反事务 T2 必须等待事务 T1 释放它对行 r 的锁定。

为了允许行锁和表锁共存实现多粒度锁机制InnoDB还有两种内部使用的意向锁Intention Locks。这两种意向锁都是表锁

• 意向共享锁Intention Shared Lock简称IS事务打算给数据加共享锁在加共享锁之前必须先取得该表的意向共享锁。
• 意向排他锁Intention Exclusive Lock简称IX事务打算在数据加排他锁在加排他锁之前必须先取得该表的意向排他锁。

上述锁模式的兼容情况如下表所示

当前锁模式｜请求锁模式	X	IX	S	IS
X	冲突	冲突	冲突	冲突
IX	冲突	兼容	冲突	兼容
S	冲突	冲突	兼容	兼容
IS	冲突	兼容	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容InnoDB就把请求的锁授予该事务反之如果两者不兼容那么该事务就要等待锁释放。需要注意意向锁是InnoDB存储引擎自动加的不需要程序员干预所以我们只需要考虑共享锁和排他锁。

对于更新语句UPDATE、DELETE和INSERTInnoDB会自动给涉及的数据加排他锁。加锁的过程对于用户是透明的不需要用户干预。对于普通查询语句SELECTInnoDB不会加任何锁不过用户可以显式给数据加共享锁或排他锁

-- 共享锁
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
-- 排他锁
SELECT * FROM table_name WHERE ... FOR UPDATE;

行锁升级为表锁InnoDB的行锁是基于索引的如果使用不通过索引的查询条件对数据加锁那么InnoDB将对表中的所有记录加锁实际效果跟表锁一样。

间隙锁如果使用范围条件而不是相等条件的查询条件请求锁那么InnoDB会给符合查询条件的已有数据进行加锁对于键值在查询条件范围内但并不存在的记录可以称为间隙InnoDB也会对这个间隙加锁。这种锁机制就是所谓的间隙锁间隙锁只能在Repeatable Read隔离级别下使用。间隙锁的好处是可以避免幻读问题坏处是会降低并发度。

行锁争用情况

通过检查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从系统启动到现在等待行锁的总次数

如果发现锁争用比较严重比如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比较高可以通过查询information_schema数据库中相关的表来查看锁情况或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据等。

行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的。如果没有索引InnoDB将通过隐藏的聚簇索引来对数据记录员加锁。

InnoDB行锁分为3种情形

1记录锁Record lock

记录锁对一个索引记录加锁。比如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止任何其他事务对该行的插入、更新和删除操作。

记录锁总是给索引记录加锁即使一个表没有索引。对于这种情况InnoDB会创建一个隐藏的聚簇索引并使用这个索引进行记录锁定。

2间隙锁Gap lock

间隙锁对索引记录之间的“间隙”加锁比如SELECT c1 FROM t WHERE c1 >= 10 and c1 <= 20 FOR UPDATE;阻止任何其他事务插入一条t.c1=15的记录无论该列是否有等于该值的记录。

一个间隙可能跨越单个索引值、多个索引值甚至是空的。

间隙锁是性能和并发性之间权衡的一部分并且用于某些事务隔离级别而不是其他事务隔离级别。

3Next-Key lock

Next-Key是前两种锁的组合即索引记录上的记录锁和索引记录之间的间隙上的间隙锁的组合。举例emp_tab表有101条记录其emp_id分别是1、2、…、100、101对于SELECT * from emp_tab where emp_id > 100 FOR UPDATE来说InnoDB不仅会对符合查询条件的emp_id等于101的记录加记录锁也会对emp_id大于101的“间隙“加锁。

InnoDB使用Next-Key锁的目的一方面是为了防止幻读满足相关隔离级别的要求另一方面是为满足其恢复和复制的需要。

很显然在使用范围条件查询并锁定记录时InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入这往往会造成严重的锁等待。因此在实际开发中尤其是并发插入比较多的应用应该尽量使用相等条件来访问更新数据避免使用范围条件。

需要特别说明的是如果使用相等的查询条件给一个不存在的记录加锁InnoDB也会使用Next-Key锁会阻塞其他事务插入数据。

总结InnoDB行锁的实现特点意味着

• InnoDB的行锁是基于索引的如果使用不通过索引的查询条件对数据加锁那么InnoDB将对表中的所有记录加锁实际效果跟表锁一样。
• 行锁是针对索引记录加锁。如果不同的数据记录具有相同的索引键那么不同的事务对这些数据记录加锁会出现锁冲突。
• 当表有多个索引时不同的事务可以使用不同的索引锁定不同的数据记录不论是使用主键索引、唯一索引还是普通索引InnoDB都会使用行锁对数据记录加锁。如果使用不同的索引对同一个数据记录加锁那么会出现锁冲突。
• 即便在查询条件种使用了索引字段但是是否使用来检索数据是由MySQL通过判断不同执行计划的代价来决定的。如果MySQL认为全表扫描效率更高那么就不会使用索引这种情况下会对所有记录加锁。因此在分析锁冲突时必须先检查SQL的执行计划以确认是否真正使用了索引。
• 避免使用范围条件给数据加锁应该尽量使用相等条件。
• 避免使用相等条件给不存在的记录加锁。

恢复和复制对InnoDB锁机制的影响

MySQL通过Binlog记录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句并由此实现MySQL数据库的恢复和主从复制。MySQL的恢复机制有以下特点

• MySQL的恢复是SQL语句级的也就是在从数据库重新执行BINLOG中的SQL语句。
• MySQL 的Binlog是按照事务提交的先后顺序记录的恢复也是按这个顺序进行的。

因此要正确恢复或复制数据就必须满足在一个事务未提交前其他并发事务不能插入满足其锁定条件的任何记录也就是不允许出现幻读。这已经超过了SQL92标准Repeatable Read隔离级别的要求实际上是要求事务要串行化。这也是许多情况下InnoDB要用到Next-Key锁的原因比如在用范围条件更新记录时无论是在Read Committed还是Repeatable Read隔离级别下InnoDB都要使用Next-Key锁但这并不是隔离级别要求的。

MySQL在处理insert into target_tab select * from source_tab where ...和create table new_tab ...select ... From source_tab where ...等语句时给source_tab加共享锁而没有使用多版本数据一致性技术。加锁的主要原因是为了保证恢复和复制的正确性具体例子参考《深入浅出MySQL》20.3.6节。但是加锁可能会阻止对源表的并发更新如果查询比较复杂会造成严重的性能问题。所以在实际中应该尽量避免使用。

死锁

MyISAM存储引擎使用表锁而且不会发生死锁的情况这是因为MyISAM总是一次性获得所需的全部锁要么全部获得要么等待。但是对于InnoDB存储引擎锁是逐步获得的导致可能发生死锁的情况。

死锁是指多个事务都需要获取其他事务持有的排他锁才能继续完成事务而且事务之间存在循环等待的情况。比如两个事务都在等待获取对方持有的排他锁无法继续各自的事务导致事务失败。

发生死锁后InnoDB一般都能自动检测到并使一个事务释放锁并回退另一个事务获得锁继续完成事务。但是在涉及外部锁或表锁的情况下InnoDB并不能完全自动检测到死锁这需要设置锁等待超时参数innodb_lock_wait_timeout来解决。

通常来说死锁都是应用设计的问题通过调整业务流程、数据库对象设计、事务大小以及访问数据库的SQL语句绝大数思索都可以避免。为了尽可能减少死锁建议

• 不同业务流程应该尽量以相同的顺序访问多个表。比如两个业务流程都需要更新表A和B应该约定使用相同的访问顺序。
• 在程序以批量方式处理数据时最好先对数据排序保证每个线程按固定的顺序来处理记录。
• 如果有更新操作应该直接申请足够级别的锁排他锁而不应该先申请共享锁等到更新时再申请排他锁。
• 不要给不存在的记录加锁。在Repeatable Read隔离级别下如果两个事务同时对相同条件的记录用SELECT ... FOR UPDATE加排他锁在没有符合该条件记录的情况下两个事务都会加锁成功。程序发现记录尚不存在并试图插入一条新纪录就会导致死锁。
• 控制事务的大小。大的事务需要锁定的记录更多处理时间更长更容易产生死锁。
• 使用合理的索引。如果查询条件不走索引将会给表的所有记录加锁死锁的概率大大增大。
• 降低隔离级别。如果业务允许将隔离级别调低也是较好的选择比如将隔离级别从Repeatable Read调整为Read Commited可以避免一些因为间隙锁造成的死锁。

尽管通过上面介绍的设计和SQL优化等措施可以大大减少死锁但死锁仍然很难避免。如果要分析死锁可以用SHOW INNODB STATUS命令来查看最后一个死锁的相关信息。

MVCC

mysql的MVCC多版本并发控制
https://www.bilibili.com/video/BV1xT411F7TW/?spm_id_from=333.999.0.0&vd_source=e591c11c1982728d43f020ce0b3830b8

MVCC的全称是Multi-Version Concurrency Control即多版本并发控制。MVCC通过维护同一个数据的多个版本或快照Snapshot来解决快照读时的读写冲突。MVCC的优点是可以避免读操作加锁导致的阻塞减少了开销提高了并发性能。

当前读读取记录的最新版本。读取时需要保证其他并发事务不能修改当前记录因此会对读取的记录进行加锁。比如SELECT LOCK IN SHARE MODESELECT FOR UPDATEUPDATEINSERTDELETE。
快照读非阻塞读即不加锁的 SELECT 操作。快照读的实现是基于多版本并发控制 MVCC。具体来说快照读是读取MVCC多版本数据链中的某个快照版本。

MVCC只有InnoDB存储引擎支持MyISAM不支持而且只能用在Read Committed和Repeatable Read两种隔离级别。对于其他两种隔离级别Read Uncommitted可以直接读其他事务未提交的数据也就是总是读取最新的数据因此不需要MVCC而串行级别Serializable根本不允许事务并发所有事务完全按顺序执行可以认为快照读在这个级别下会退化成当前读因此也不需要MVCC。

底层实现和原理

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间一个保存行的过期时间或删除时间。当然存储的并不是实际的时间值而是系统版本号每开始一个新的事务系统版本号就会递增。事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录的版本号进行比较。——来自《高性能MySQL》

具体来说MVCC的实现主要是依赖每行记录中的两个隐藏字段DB_TRX_ID、DB_ROLL_PTR以及读视图Read View

• DB_TRX_ID创建或修改该条记录的事务 ID。另外事务ID会随着时间越来越大后发生的事务ID要大于之前发生的事务。
• DB_ROLL_PTR回滚指针指向这条记录的上一个版本。
• Read View事务进行快照读生成的读视图记录并维护系统当前活跃事务的ID包括
  • rw_trx_ids创建Read View时所有的活跃事务ID。事务活跃意味着事务还没有提交。
  • min_trx_id活跃的事务ID列表rw_trx_ids中的最小事务ID。
  • max_trx_id创建Read View时将要分配给下一个事务的ID。
  • curr_trx_id创建Read View时的当前事务ID。

举例在通过下列SQL命令操作一条数据后

insert test_tab(id, name) values (1, "mysql"); -- DB_TRX_ID = 100

update test_tab set name = "sql" where id = 1; -- DB_TRX_ID = 200

update test_tab set name = "mvcc" where id = 1; -- DB_TRX_ID = 300

这条数据的版本链如下图所示

解释新版本数据的事务ID大于旧版本的并且有指针指向旧版本数据。

多版本非最新版本的数据存储在UNDO日志中而且该日志也可以用来实现事务回滚。

注意读视图Read View在Read Committed和Repeatable Read两种隔离级别下的工作机制略有不同

• 对于Read Committed每次快照读查询都会创建一个新的读视图Read View因此读视图中的活跃的事务ID列表可能会变化因为其他并发的事务可能已经提交。这意味着在这个隔离级别下一个事务可以读取到其他并发事务提交的修改。这也就是为什么这个隔离级别称为读已提交Read Committed。
• 对于Repeatable Read只在第一次快照读时创建一个读视图Read View并且在整个事务期间保持不变。因此读视图Read View中的活跃事务ID应该都小于创建该读视图的事务ID。这意味着在这个隔离级别下创建该读视图的事务只能读取DB_TRX_ID小于当前事务ID的那些版本的数据。

根据上面这条数据举例说明一下快照读的原理。如果有一个快照读事务尝试查询SELECT name FROM test_tab where id = 1

1假设快照读所在的事务ID=301读视图Read View的当前活跃的事务包括205255300其中事务205和255可能数据id=1无关那么该查询会查到name=sql。具体来说查询从DB_TRX_ID=300版本的数据开始发现对应的事务未提交因为300在活跃事务中然后根据DB_ROLL_PTR向后查找历史版本最后发现DB_TRX_ID=200版本的数据已经提交因为200不在活跃事务中返回这个版本的数据。

2假设快照读所在的事务ID=150读视图Read View的当前活跃的事务包括205255300那么该查询在不同的隔离级别会查到不同的结果

• 对于Read Committed会查到name=sql因为该隔离级别允许一个事务事务ID=150读取其他事务事务ID=200提交的更新。
• 对于Repeatable Read会查到name=mysql因为该隔离级别只能读取DB_TRX_ID小于当前操作事务的ID的数据。

3假设快照读所在的事务ID=50读视图Read View的当前活跃的事务包括205255300那么该查询在不同的隔离级别会查到不同的结果对于Read Committed会查到name=sql对于Repeatable Read会查不到任何结果。

4假设快照读所在的事务ID=300读视图Read View的当前活跃的事务包括205255300那么该查询所处的事务是活跃的事务还没有提交所以可以观察到当前事务的更新返回name=mvcc。

5假设快照读所在的事务ID=299读视图Read View的当前活跃的事务包括205255300

• 第一次查询对于Read Committed和Repeatable Read都会查到name=sql。
• 假设事务ID=300提交。
• 第二次查询对于Read Committed会创建一个新的读视图活跃的事务包括205255因此可以查到name=mvcc对于Repeatable Read读视图不会变化因此仍然查到name=sql。这也就是为什么这个隔离级别称为可重复读Repeatable Read。

由于旧数据并不真正的删除所以必须对这些数据进行清理Innodb会开启一个后台线程执行清理工作具体的规则是将删除版本号小于当前系统版本的行删除这个过程叫做purge。

悲观锁和乐观锁

一般来说并发事务有三种数据冲突

• 读读冲突并发读之间互不影响。
• 读写冲突加锁或使用MVCC机制。
• 写写冲突必须加锁包括悲观锁Pessimistic lock和乐观锁Optimistic lock。

解决写写冲突的方法有

1使用Serializable隔离级别事务串行执行那么就没有任何冲突。为了事务并发大多数情况都不会使用这种方法。

2使用悲观锁。悲观锁是在数据库层面加锁但是等待锁会导致阻塞。

举例SELECT * FROM tab FOR UPDATE在SELECT语句后边加了FOR UPDATE相当于加写锁排他锁。加了写锁以后其他事务不能进行修改需要等待当前事务执行完。

3使用乐观锁。

乐观锁不是数据库层面上的锁而是一种思想具体实现是在表中添加一个版本字段version。当更新某个数据时检查该数据的版本是否和预期相同。如果相同才可以更新否则不可以更新。

举例UPDATE tab SET name=xxx,version=version+1 WHERE ID={id} AND version={version}该语句会判断version字段与当前的version字段是否相等相等则更新成功否则更新失败。所以如果两个事务同时更新同一行数据一个事务更新成功其他事务就会更新失败。