mysql事务隔离机制&锁

最新推荐文章于 2022-10-22 15:31:28 发布

咸菜one

最新推荐文章于 2022-10-22 15:31:28 发布

阅读量3.5k

点赞数 5

分类专栏：数据库

本文链接：https://blog.csdn.net/xiancaione/article/details/82157019

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Mysql事务隔离级别

事务

MySQL事务

事务定义：事务就是一组原子性的sql查询，或者说一个独立的工作单元。

即事务内的sql语句，要么全部执行成功，要么全部执行失败；
事务的ACID概念：原子性automicity，一致性consistency，隔离性isolation，持久性durability；

ACID原则

原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚；

一致性；数据库总是从一个一致性状态转移到另一个一致性状态；

隔离性：通常来说，一个事务所做的修改在最终提交前，对其他事务都是不可见的；

持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中；此时即使数据库崩溃，数据也不会丢失；

隔离级别

READ UNCOMMITTED（未提交读）
　这个级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，被称为脏读（Dirty Read），这个级别性能不会比其他级别好太多，但缺乏其他级别的很多好处，一般很少使用。

READ COMMITTED（提交读）
　这个级别是大多数数据库系统的默认隔离级别（但MySQL不是）。一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别也叫作不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

REPEATABLE READ（可重复读）
　该级别保证了在同一个事务中多次读取同样记录的结果是一致的，但依然无法解决另外一个幻读（Phantom Read）的问题。幻读，指的是当某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB 和 XtraDB 存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。可重复读是MySQL的默认事务隔离级别。

SERIALIZABLE（可串行化）
　最高的隔离级别，强制事务串行执行，避免了前面说的幻读的问题。但每次读都需要获得表级共享锁，读写相互都会阻塞

所有隔离级别

““
1）read uncommitted : 读取尚未提交的数据：哪个问题都不能解决

2）read committed：读取已经提交的数据：可以解决脏读 —- oracle默认的

3）repeatable read：可重复读：可以解决脏读和不可重复读 —mysql默认的

4）serializable：串行化：可以解决脏读不可重复读和虚读—相当于锁表

““

查看隔离级别

SELECT @@global.tx_isolation; #查看全局隔离级别 SELECT @@session.tx_isolation; #查看会话隔离级别 SELECT @@tx_isolation;

设置隔离级别

set session transaction isolation level 设置事务隔离级别

为了深入理解开始我们的实现吧！！！

实验一

1.新建一张表，添加一些数据如图
table图
2.开启5个连接，为了叙述方便分别命名为 A,B,C,D,E, 并且做如下设置

A 不做设置

B set session transaction isolation level read uncommitted; 设置B的隔离界别为未提交读

C set session transaction isolation level read committed 设置B的隔离界别为提交读

D set session transaction isolation level repeatable read 设置B的隔离界别为可重复读

E set session transaction isolation level serializable 设置B的隔离界别为串行化

A连接中执行如下代码,开启事务，更新表数据

““
start transaction;

update test set balance = balance-50 where id = 1

//commit
““
分别查看 B，C，D，E 会话

select * from test

通过观察发现

只有隔离级别为READ-UNCOMMITTED的在A会话事务未提交的情况下。读取到了未commit的数据（ff)
这会有什么问题呢，加入A在update后没有commit，而是进行了rollback,那么此时就会出现脏读。如何避免这个问题？read commited及以上隔离级别设定，一个事务只能读取另一个事务已经提交的数据，就避免了上面的脏读现象。
隔离级别为read-committed，没有读取到修改，当commit的时候才读取到修改信息这就导致了在一个事务中两次读取到的数据不一致的情况，（也就是read commited下设定，一个事务只能读取另一个事务已经提交的数据）出现了不可重复读的情况。如何避免这个问题？repeatable read及以上级别设定，一个事务里，对数据的多次查询都是读取的一个，无论该数据在中途是否被其他事务修改过，因此也就避免了不可重复读的问题。
实验条件如上不变化，变化的是当隔离级别repeatable read，在D查询之前开启事务，在A提交之前D没有读取到数据，当A commit后D仍然没有读取到A提交的结果，当D提交了数据以后，此时能正常读取到A提交的数据修改。
实验表明：repeatable-read在开启事务的情况下，同一条件的查询返回的结果永远是一致的，无论其它事物是否提交了新的数据
这种隔离级别和repeatable-read类似，只会读取其它事物已提交的内容，有一点不同的地方在于，如果autocommit为false，那么每一条select语句会自动被转化为select … lock in share mode.这样出现一些阻塞情况

实验二

幻读

session A	session D
start transaction;	start transaction;
	INSERT INTO `test` (`name`, `balance`) VALUES (‘lees’, 3333);
select * from test (empty)
	commit;
select * from test (empty)
INSERT INTO `test` (`id`, `name`, `balance`) VALUES (1, ‘lees’, 3333);
Duplicate entry ‘1’ for key ‘PRIMARY’

发生了什么？刚才没有数据，为啥现在有了。写不进去了？这就是出现了幻读。

幻读不可重复读和幻读的区别

很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

如何解决幻读?

在快照读读情况下，mysql通过mvcc来避免幻读。

在当前读读情况下，mysql通过next-key来避免幻读

不懂，往下看，看到最后就明白了。

锁

读写锁

无论何时，只要有多个查询需要在同一时刻修改数据，就会产生并发控制问题。（并发，数据一致性，首先想到的是什么？锁）
解决并发常见的解决方案是实现一个有两种类型的锁组成的锁系统，这两种类型的锁同城被称为共享锁（shared lock）和排他锁（exclusive lock）也叫做读锁（read lock）和写锁（write lock）

读锁是共享的，或者说是相互不阻塞的，多个客户在同一时刻可以同时读取同一个资源，而互不干扰

写锁是排他的，一个写锁会阻塞其他的写锁和读锁

锁粒度

一种提高共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。

更理想的方式是，只对会修改数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发成都越高，只要互相之间不发生冲突即可。

加锁带来的问题

问题是加锁也需要消耗资源。锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能可能会因此受到影响。

所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然也会影响到性能。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁（row-level lock），并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能提供更好的性能。
而MySQL则提供了多种选择。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能，但同时却会失去对另外一些应用场景的良好支持。好在MySQL支持多个存储引擎的架构，所以不需要单一的通用解决方案。

下面将介绍两种最重要的锁策略。

表锁（table lock）
表锁是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表。一个用户在对表进行写操作（插入、删除、更新等）前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。当没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。

行级锁（row lock）
行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。在InnoDB和XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。

一次封锁or两段锁？

因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次封锁法，就是在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁，但在数据库中却不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。
数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。

解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

事务	加锁/解锁处理
begin；	start transaction;
insert into test …	加insert对应的锁
update test set …	加update对应的锁
delete from test ….	加delete对应的锁
commit;	事务提交时，同时释放insert、update、delete对应的锁

这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

悲观锁和乐观锁

悲观锁

正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

要说明的是，MVCC的实现没有固定的规范，每个数据库都会有不同的实现方式，这里讨论的是InnoDB的MVCC。

MVCC在MySQL的InnoDB中的实现

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。在可重读Repeatable

field1	field2	field3	create_version	delete_version
value1	value2	value3	create_version_value	delete_version_value

mvcc

reads事务隔离级别下：

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。

INSERT时，保存当前事务版本号为行的创建版本号

DELETE时，保存当前事务版本号为行的删除版本号

UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

通过MVCC，虽然每行记录都需要额外的存储空间，更多的行检查工作以及一些额外的维护工作，但可以减少锁的使用，大多数读操作都不用加锁，读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，也只锁住必要行。

在RR级别中，通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，是不及时的数据，不是数据库当前的数据！这在一些对于数据的时效特别敏感的业务中，就很可能出问题。

对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。很显然，在MVCC中：

快照读：就是select
select * from table ….;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update;
delete;
事务的隔离级别实际上都是定义了当前读的级别，MySQL为了减少锁处理（包括等待其它锁）的时间，提升并发能力，引入了快照读的概念，使得select不用加锁。而update、insert这些“当前读”，就需要另外的模块来解决了。

写（”当前读”）

事务的隔离级别中虽然只定义了读数据的要求，实际上这也可以说是写数据的要求。上文的“读”，实际是讲的快照读；而这里说的“写”就是当前读了。
为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。

Next-Key锁

InnoDB有三种行锁的算法：

1，Record Lock：单个行记录上的锁。

2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

实验三
先设置隔离级别 set session transaction isolation level repeatable read

sessonA	B
select * from test;
update test set balance = balance+1350 where id > 9
	INSERT INTO `test` (`name`, `balance`) VALUES (‘lees’, 3333); 会发生什么？
select * from test;
commit
	事务Acommit后，事务B的insert执行

如果sessionB改为如下，会发生什么？
INSERT INTO test (id,name, balance)
VALUES
(5,’lees’, 3333);

RR级别中，事务A在update后加锁，事务B无法插入新数据，这样事务A在update前后读的数据保持一致，避免了幻读。这个锁，就是Gap锁。

上述查询发生了什么？
sessionA更新的时候，inndb讲数据分成了以下（自行想象B+Tree结构）

(negative infinity, 9],（9,],

update test set balance = balance+1350 where id > 9;不仅用行锁，锁住了相应的数据行；同时也在两边的区间，（9,]，都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。避免了 where id > 9 操作时候出现幻读的情况

如果把 update test set balance = balance+1350 where id > 9 换成 update test set balance = balance+1350 where id = 9 的时候会发生什么呢？

因为InnoDB对于行的查询都是采用了Next-Key Lock的算法当查询的时候如果是一个范围的时候，会发生Next-Key Lock。但是，当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

注意：通过主键或则唯一索引来锁定不存在的值，也会产生GAP锁定

总结：行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。

一些建议

mysql中默认事务隔离级别是可重复读时并不会锁住读取到的行
事务隔离级别为读提交时，写数据只会锁住相应的行
事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间隙锁、行锁、下一键锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表。
事务隔离级别为串行化时，读写数据都会锁住整张表
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。
Serializable 这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

最后不要看到select就说不会加锁了，在Serializable这个级别，还是会加锁的！

[参考文献]

咸菜one

关注

5
点赞
踩
30

收藏

觉得还不错? 一键收藏
0
评论
mysql事务隔离机制&锁

Mysql事务隔离级别事务MySQL事务事务定义：事务就是一组原子性的sql查询，或者说一个独立的工作单元。即事务内的sql语句，要么全部执行成功，要么全部执行失败；事务的ACID概念：原子性automicity，一致性consistency，隔离性isolation，持久性durability；ACID原则原子性：一个事务必须被视为一个不可分割的最小工作...
复制链接

扫一扫