MySQL：事务1（锁与隔离级别）

-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

2.2、自动提交

MySQL 默认采用隐式提交策略（autocommit）。每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

-- 查看 AUTOCOMMIT
SHOW VARIABLES LIKE 'AUTOCOMMIT';

-- 关闭 AUTOCOMMIT
SET autocommit = 0;

-- 开启 AUTOCOMMIT
SET autocommit = 1;

3、事务的属性

ACID 是数据库事务正确执行的四个基本要素。

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性。

只有满足一致性，事务的执行结果才是正确的。
在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
事务满足持久化是为了能应对系统崩溃的情况。

二、问题与处理办法

1、事务带来的问题

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题：

脏读

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

不可重复读

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

幻读

幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

幻读其实可以看作是不可重复读的一种特殊情况：

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样，这一点下文我们将会介绍。

2、隔离级别

为了解决事务可能产生的问题，并且兼顾事务的并发性，mysql提供了4种不同的隔离级别，分别能够应对不同的问题。

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。（InnoDB可以在RR级别下解决幻读的问题）。
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

串行化能避免所有的问题，但是顺序执行必然导致并发性能的下降，因此MySQL默认的隔离级别为RR（可重复读），可通过如下语句查询与修改：

-- 查看事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 设置事务隔离级别为 READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 设置事务隔离级别为 READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置事务隔离级别为 REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置事务隔离级别为 SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MySQL（准确来说是InnoDB）的隔离级别基于锁和 MVCC 机制共同实现的。下面我们讲解下锁。

三、MySQL中的锁

MySQL中的锁按照不同的划分标准有多种不同的类型，下面我们逐个介绍下。

1、表锁与行锁

从数据库的锁粒度来看，MySQL 中提供了两种封锁粒度：行级锁和表级锁。

表级锁（table lock） - MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁（row lock） - MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。MyISAM不支持行锁。

应该尽量只锁定需要修改的那部分数据，而不是所有的资源。锁定的数据量越少，锁竞争的发生频率就越小，系统的并发程度就越高。但是加锁需要消耗资源，锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。因此锁粒度越小，系统开销就越大。因此在选择锁粒度时，需要在锁开销和并发程度之间做一个权衡。

InnoDB 的行锁是针对索引字段加的锁，如果没有索引呢？这里需要注意的是，如果没有命中索引，InnoDB会选择全表扫描，而因为主键的存在，所以其实这里会给主键上的所有行记录加行锁。（如果没有主键 InnoDB 将会创建隐藏的聚簇索引，因此主键索引必然存在。不过这里行锁的类别有些特殊，我们会在下文介绍）

2、读锁和写锁

不论是表级锁还是行级锁，都存在读锁（Share Lock，S 锁，共享锁）和写锁（Exclusive Lock，X 锁，排他锁）这两类：

读锁（S 锁） ：又称共享锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
写锁（X 锁） ：又称排他锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

写锁与任何的锁都不兼容，读锁仅和读锁兼容。

	S锁	X锁
S锁	兼容	不兼容
X锁	不兼容	不兼容

InnoDB对于普通的select语句不会加锁（因为有MVCC机制存在），当使用当前读时会加排他锁。注意：行锁都是排他锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

3、意向锁

在存在行级锁和表级锁的情况下，事务 T 想要对表 A 加 X 锁，就需要先检测是否有其它事务对表 A 或者表 A 中的任意一行加了锁，那么就需要对表 A 的每一行都检测一次，这是非常耗时的。

当存在表级锁和行级锁的情况下，必须先申请意向锁（表级锁，但不是真的加锁），再获取行级锁。使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。

意向锁规定：

IX/IS 是表锁；
X/S 是行锁。
一个事务在获得某个数据行的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；
一个事务在获得某个数据行的 X 锁之前，必须先获得表的 IX 锁。

通过引入意向锁，事务 T 想要对表 A 加 X 锁，只需要先检测是否有其它事务对表 A 加了 X/IX/S/IS 锁，如果加了就表示有其它事务正在使用这个表或者表中某一行的锁，因此事务 T 加 X 锁失败。

各种锁的兼容关系如下：

-	X	IX	S	IS
X	❌	❌	❌	❌
IX	❌	✔️	❌	✔️
S	❌	❌	✔️	✔️
IS	❌	✔️	✔️	✔️

解释如下：

任意 IS/IX 锁之间都是兼容的，因为它们只表示想要对表加锁，而不是真正加锁；
这里兼容关系针对的是表级锁，而表级的 IX 锁和行级的 X 锁兼容，两个事务可以对两个数据行加 X 锁。（事务 T1 想要对数据行 R1 加 X 锁，事务 T2 想要对同一个表的数据行 R2 加 X 锁，两个事务都需要对该表加 IX 锁，但是 IX 锁是兼容的，并且 IX 锁与行级的 X 锁也是兼容的，因此两个事务都能加锁成功，对同一个表中的两个数据行做修改。）

4、乐观锁与悲观锁

乐观锁和悲观锁是多事务并发时保证数据隔离性和统一性的手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作，使用锁机制，在查询完数据的时候就把事务锁起来，直到提交事务（COMMIT）。
乐观锁：假设不会发生并发冲突，不进行加锁而使用多版本并发控制（MVCC），只在事务提交时检查是否违反数据完整性。

悲观锁的实现方式即为各种锁（表锁、行锁等），乐观锁的实现主要依靠MVCC机制。

5、行锁的进一步理解

在InnoDB中，默认的隔离级别为RR（可重复读），该级别防止幻读的手段分为2种情况：

快照读（一致性非锁定读）：由 MVCC 机制来保证不出现幻读。
当前读（一致性锁定读）：使用 Next-Key Lock 进行加锁来保证不出现幻读。

快照读即普通的select语句，MVCC机制我们会在后续进行讲解，这里只需要将其理解为读取旧版本数据即可，只读取旧数据自然不会读取到新数据。当前读（INSERT、UPDATE、DELETE等除了普通select的操作），为了读取最新的数据，所以要加锁。

5.1、行锁的类型

InnoDB中的行锁有三种：

记录锁（Record Lock） ：也被称为记录锁，属于单个行记录上的锁。
间隙锁（Gap Lock） ：锁定一个范围，不包括记录本身。
临键锁（Next-key Lock） ：Record Lock+Gap Lock，锁定一个范围，包含记录本身。记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。

下面针对几种不同的情况分别进行分析：

（1）当命中主键或唯一索引时，由于这两种索引存在唯一性，where条件全部精确命中(=或者in)，这种场景本身就不会出现幻读，所以只会加行记录锁。

（2）没有索引的列，走全表扫描，按照主键索引一行行扫描，会在主键上加next-key lock锁，例如主键上值（1，3，5），那么next-key lock锁定的范围为(-∞,1]、(1,3]、(3,5]、(5, +supremum]。

（3）非唯一索引的列，会在查找范围加上next-key lock。若产生回表还会在主键索引上加上记录锁。

5.2、两阶段锁协议

高并发时对一条记录进行更新的情况下，由于更新记录所在的事务还可能存在其他操作，导致一个事务比较长，当有大量请求进入时，就可能导致一些请求同时进入到事务中。

又因为锁的竞争是不公平的，当多个事务同时对一条记录进行更新时，极端情况下，一个更新操作进去排队系统后，可能会一直拿不到锁，最后因超时被系统打断踢出。

上图中的操作，虽然都是在一个事务中，但锁的申请在不同时间，只有当其他操作都执行完，才会释放所有锁。因为扣除库存是更新操作，属于行锁，这将会影响到其他操作该数据的事务，所以我们应该尽量避免长时间地持有该锁，尽快释放该锁。又因为先新建订单和先扣除库存都不会影响业务，所以我们可以将扣除库存操作放到最后，也就是使用执行顺序 1，以此尽量减小锁的持有时间。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。这个设定就告诉我们，如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

四、死锁

1、死锁的定义

死锁是指两个或多个事务竞争同一资源，并请求锁定对方占用的资源，从而导致恶性循环的现象。（这里的死锁专指Deadlock，不包括锁等待超时lock wait timeout，相关内容可以看我这篇文章《mysql运维脚本与个人理解》）

产生死锁的场景：

当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。
多个事务同时锁定同一个资源时，也会产生死锁。

2、死锁的场景

一个常见的场景是两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。

以上图为例，两个事务产生了锁争用，便导致了死锁。

3、死锁的预防与处理

3.1、预防

预防死锁的注意事项：

在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；
更新表时，尽量使用主键更新；
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
设置合理的锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。

3.2、处理

当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。为了解决死锁问题，不同数据库实现了各自的死锁检测和超时机制。InnoDB 的处理策略是：将持有最少行级排它锁的事务进行回滚。

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。你可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

jchen104

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL：事务1（锁与隔离级别）

将小明的余额减少 1000 元将小红的余额增加 1000 元事务会把这一个或多个操作看成逻辑上的一个整体，这个整体包含的操作要么都成功，要么都要失败。这样就不会出现A余额减少而B的余额却并没有增加的情况。事务是由存储引擎层实现的，不是所有的 Mysql 存储引擎都实现了事务处理。支持事务的存储引擎有：InnoDB 和 NDB Cluster。不支持事务的存储引擎，代表有：MyISAM。用户可以根据业务是否需要事务处理（事务处理可以保证数据安全，但会增加系统开销），选择合适的存储引擎。.........
复制链接

扫一扫