数据库的事务和锁

文章目录

• 1、事务
• • 1.1、事务是什么
  • 1.2、为什么要有事务
  • 1.3、事物的特性
  • 1.4、事务的并发问题
  • 1.5、事物的四种隔离级别
  • 1.6、事务运行的三种模式
• 2、锁
• • 2.1、为什么要加锁
  • 2.2、锁是基于什么实现的
  • 2.2、锁的分类
  • • 2.2.1、基于程序员角度分类（乐观锁与悲观锁）
    • • 2.2.1.1、乐观锁
      • 2.2.1.2、悲观锁
    • 2.2.2、基于锁的属性分类（共享锁与排它锁）
    • • 2.2.2.1、共享锁
      • 2.2.2.2、排它锁
      • 2.2.2.3、小总结
    • 2.2.3、基于锁的粒度分类（表锁、行锁、记录锁、间隙锁、临键锁）
    • • 2.2.3.1、表锁
      • 2.2.3.2、行锁
      • • 2.2.3.2.1、记录锁
        • 2.2.3.2.2、间隙锁
        • 2.2.3.2.3、临键锁
    • 2.2.4、基于锁的状态分类（意向共享锁与意向排它锁）
    • • 2.2.4.1、意向共享锁
      • 2.2.4.2、意向排它锁
      • 2.2.4.2、应用场景

1、事务 1.1、事务是什么

事务是代表一个或者一系列 *** 作的最小逻辑单元，所有在这个逻辑单元内的 *** 作要么全部成功，要么就全部失败，不存在任何中间状态，一旦事务失败那么所有的更改都会被撤消，一旦事务成功所有的 *** 作结果都会被保存。

1.2、为什么要有事务

A 账户有 1000 元，B 账户有 1000 元，A 向 B 转账 500 元，流程如下：

1. 查询 A 账户余额，看金额是否有 500 元满足可以转账需求。
2. 满足需求后，然后从 A 账户扣款 500 元（此时 A 账户现在有 500元，B 账户现在有 1000 元）。
3. 最后向 B 账户增加 500 元（此时 A 账户现在有 500 元，B 账户现在有 1500 元，转账完成）。

• 在没有事务的情况下，如果在执行完第 2 步后，执行第 3 步系统发生异常失败了，那么最后的结果是 A 账户少了 500 而 B 账户也没收到转款，500 元凭空消失。还有就是如果在执行第 2 步失败了，然后继续执行第 3 步，那么最后的结果是 A 账户没有扣款而 B 账户收到 500 元转款，500 元凭空增加。
• 在有事务的情况下，在这个 *** 作中，转账就是本次一系列 *** 作的最小逻辑单元，只有3个 *** 作都成功了才算转账成功，任何一个步骤失败都算整个转账 *** 作失败，只要其中任意一个步骤执行失败都不会再往下执行，并对已经执行的数据变更进行恢复。

1.3、事物的特性

• 原子性（Atomicity）
  一个事务必须是一系列 *** 作的最小单元，这系列 *** 作的过程中，要么整个执行，要么整个回滚，不存在只执行了其中某一个或者某几个步骤。
• 一致性（Consistency）
  事务要保证数据库整体数据的完整性和业务的数据的一致性，事务成功提交整体数据修改，事务错误则回滚到数据回到原来的状态。
• 隔离性（Isolation）
  隔离性是说两个事务的执行都是独立隔离开来的，事务之前不会相互影响，多个事务 *** 作一个对象时会以串行等待的方式保证事务相互之间是隔离的。
• 持久性（Durability）
  持久性是指一旦事务成功提交后，只要修改的数据都会进行持久化，不会因为异常、宕机而造成数据错误或丢失。

1.4、事务的并发问题

1. 脏读
   一个事务读取到另一个事务没有提交的修改，就是当另一个事务它还没有提交修改一个事务就读取到了修改（事务 A 读取了事务 B 更新的数据，然后 B 回滚 *** 作，那么A读取到的数据是脏数据）。
2. 不可重复读
   一个事务重复读两次得到不同结果，说明读取 *** 作结果是不可重复的（事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致）。
3. 幻读
   一个事务第二次查询出现第一次没有的结果，说明别的事务已经插入一些数据（系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读）。

1.5、事物的四种隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

1. 读未提交
   读未提交是隔离级别最低的一种事务级别，在这种隔离级别下任何情况下都可能出问题。
2. 读已提交
   只能读取已经提交的数据，这是大多数数据库系统默认的隔离级别，但不是MySQL默认的。
3. 可重复读
   同一个事务先后查询结果一样，Mysql InnoDB默认实现可重复读级别，此级别可能出现幻读。
4. 可串行化
   最严格的隔离级别。在隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

1.6、事务运行的三种模式

1. 自动提交事务
   每一条单独的SQL语句都在其执行完成后进行自动提交事务，即执行 SQL 语句后就会马上自动隐式执行 COMMIT *** 作。如果出现错误，则进行事务回滚至之前状态。
2. 显式事务
   通过指定事务开始语句来显式开启事务来作为开始，并由以提交命令或者回滚命令来提交或者回滚事务作为结束的一段代码就是一个用户定义的显式事务。以 begin transaction 开始，以 commit 或 rollback 结束。
3. 隐性事务
   在隐式事务中，无需使用 begin transaction 来开启事务，每个SQL语句第一次执行就会开启一个事务，直到用 commit 来提交或者 rollback 来回滚结束事务。

2、锁 2.1、为什么要加锁

加锁是为了解决事务的隔离性问题，让事务之间相互不影响，每个事务进行 *** 作的时候都必须先对数据加上一把锁，防止其他事务同时 *** 作数据时候造成读取和存储的不正确，来保持数据库数据的一致性。

2.2、锁是基于什么实现的

数据库里面的锁是基于索引实现的，在Innodb中我们的锁都是作用在索引上面的，当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）。

2.2、锁的分类

• 基于锁的属性分类：共享锁、排他锁。
• 基于锁的粒度分类：表锁、行锁、记录锁、间隙锁、临键锁。
• 基于锁的状态分类：意向共享锁、意向排它锁。

2.2.1、基于程序员角度分类（乐观锁与悲观锁） 2.2.1.1、乐观锁

乐观锁不是数据库自带的，需要自己去实现。乐观锁是指 *** 作数据库时(更新 *** 作)，想法很乐观，认为这次的 *** 作不会导致冲突，在 *** 作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

通常实现是这样的：在表中的数据进行 *** 作时(更新)，先给数据表加一个版本(version)字段，每 *** 作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段，如果要对那条记录进行 *** 作(更新)，则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行 *** 作，则可以执行更新，将version字段的值加1，如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行 *** 作了，则不进行更新 *** 作。

2.2.1.2、悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在 *** 作数据时，认为此 *** 作会出现数据冲突，所以在进行每次 *** 作时都要通过获取锁才能进行对相同数据的 *** 作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

2.2.2、基于锁的属性分类（共享锁与排它锁） 2.2.2.1、共享锁

又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

用法

在查询语句后面增加 LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁。

SELECT ... LOCK IN SHARE MODE

验证

打开一个查询窗口，进行共享锁测试，给ID等于100的记录添加共享锁，SQL如下：

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE

在加了共享锁后，打开一个新的查询窗口，进行共享锁测试

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE;-- 使用共享锁 查询到数据
SELECT * FROM yang WHERE id = 100 FOR UPDATE; -- 1205 - 使用排它锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100;-- 不加锁 查询到数据

2.2.2.2、排它锁

又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

用法

在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁

SELECT ... FOR UPDATE

验证

打开一个查询窗口，进行共享锁测试，给ID等于100的记录添加排它锁，SQL如下：

SELECT * FROM yang WHERE id = 100 FOR UPDATE

在加了排它锁后，打开一个新的查询窗口，进行排它锁测试

SELECT * FROM yang WHERE id = 100 LOCK IN SHARE MODE;-- 使用共享锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100 FOR UPDATE; -- 1205 - 使用排它锁 Lock wait timeout exceeded; try restarting transaction
SELECT * FROM yang WHERE id = 100;-- 不加锁 查询到数据

2.2.2.3、小总结

• 共享锁就是多个事务只能读数据不能改数据。
• 对于排他锁的理解可能就有些差别，以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。
• mysql InnoDB引擎默认的修改数据语句，update，delete，insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。
• 加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

2.2.3、基于锁的粒度分类（表锁、行锁、记录锁、间隙锁、临键锁） 2.2.3.1、表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问。

特点： 粒度大，加锁简单，容易冲突

2.2.3.2、行锁

行锁是对所有行级别锁的一个统称，比如下面说的记录锁、间隙锁、临键锁都是属于行锁， 行锁是指加锁的时候锁住的是表的某一行或多行记录，多个事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问。

特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高。

2.2.3.2.1、记录锁

记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。比如

SELECT * FROM yang WHERE id = 1 FOR UPDATE

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

• id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
• 同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

2.2.3.2.2、间隙锁

它是行锁中的一种，它锁定的是一个范围区间的索引，遵守左开右闭原则。

案例说明

id	number	source
5	10	100
7	8	200
10	7	200
12	7	100

以上表，id为主键，number为唯一索引，score为普通索引

唯一索引（范围内的查询语句，会产生间隙锁）

开启事务A

BEGIN;
SELECT * FROM YANG WHERE number >= 5 AND number < 9 FOR UPDATE;

开启事务B

INSERT INTO yang(id, number, score) VALUES (13, 4, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (14, 5, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (15, 6, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (16, 7, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (17, 8, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (18, 9, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (19, 10, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (20, 11, 100);-- 不阻塞

number = 5，唯一索引等值查询，加行锁 5
number > 5 和 number < 9，唯一索引范围查询，加锁 (负无穷，5]、(5，7]、(7，10]
综上，加锁范围为 [负无穷，10]

唯一索引（该记录不存在，会产生间隙锁，如果记录存在，则只会产生记录锁）

开启事务A

BEGIN;
SELECT * FROM YANG WHERE number = 3 FOR UPDATE;

开启事务B

INSERT INTO yang(id, number, score) VALUES (12, 3, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (13, 4, 100);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (14, 5, 100);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (15, 6, 100);-- 不阻塞

number = 3 不存在，扫描到5，加锁 (负无穷，5]
索引（唯一和普通）等值查询，向右遍历时最后一个不满足等值条件的时候，next-key lock退化为间隙锁(左开右开)，退化为(负无穷，5）

普通索引等值查询

开启事务A

BEGIN;
SELECT * FROM YANG WHERE score = 70 FOR UPDATE;

开启事务B

INSERT INTO yang(id, number, score) VALUES (8, 8, 59);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (8, 8, 60);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (8, 8, 65);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (8, 8, 70);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (8, 8, 75);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (8, 8, 80);-- 不阻塞

开启事务C，例如 score 为 80 受唯一索引影响

INSERT INTO yang(id, number, score) VALUES (1, 1, 80);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (2, 2, 80);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (3, 3, 80);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (4, 4, 80);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (5, 5, 80);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (6, 6, 80);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (7, 7, 80);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (8, 8, 80);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (9, 9, 80);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (10, 10, 80);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (11, 11, 80);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (12, 12, 80);-- 阻塞
INSERT INTO yang(id, number, score) VALUES (13, 13, 80);-- 不阻塞
INSERT INTO yang(id, number, score) VALUES (14, 14, 80);-- 不阻塞

在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样，在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

2.2.3.2.3、临键锁

临键锁也属于行锁的一种，并且它是 INNODB 的行锁默认算法，临键锁 nextkeylock 是行锁与间隙锁的并集，是mysql加锁的基本单位。

2.2.4、基于锁的状态分类（意向共享锁与意向排它锁） 2.2.4.1、意向共享锁

意向共享(IS)锁：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。 
SELECT column FROM table ... LOCK IN SHARE MODE;

2.2.4.2、意向排它锁

意向排他(IX)锁：事务有意向对表中的某些行加排他锁（X锁）

 -- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
 SELECT column FROM table ... FOR UPDATE;

• 意向共享锁（IS）和 意向排他锁（IX）都是表锁。
• 意向锁是一种 不与行级锁冲突的表级锁。
• 意向锁是 InnoDB自动加的， 不需用户干预。

2.2.4.2、应用场景

假设，一个事务 A 给表的第 R 行加了写锁，另一个事务 B 想调整表结构，对整个表 *** 作，给表加一个写锁，需要执行两个步骤：

1. 检查表是否被上了读锁或者写锁。
2. 遍历所有行，查看每一行是否被上了读锁或写锁。

步骤1可以很快执行，但是步骤2，则需要非常耗时，效率低下。
意向锁的作用，相当于就是在低层次资源是否使用，加了一个标识，提速步骤2的检查。
有了意向锁之后，事务 A 给表的第 R 行加了写锁时候，会先给表上意向锁。

事务B想对表上写锁， *** 作步骤如下：

1. 检查表是否被上了读锁或写锁。
2. 检查表是否被上了意向锁（因为所有行的读写之前，会先上意向锁），即可判断表中是否有任何一行被上了读锁或写锁。

步骤2在检查逻辑的时间复杂度，由于增加了意向锁之后，时间复杂度由O(n)，提升到了O(1)，效率大幅提升。