数据库事务隔离级别及优缺点

2021.01.18 10:01 53
阅读约 11 分钟

什么是数据库事务

数据库事务transaction是访问并可能操作各种数据项的一个数据库操作序列,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

为什么InnoDB是默认选项

最主要的原因是因为 MyIasm 的锁粒度是表锁,会降低 OLTP 场景下(关系数据库)的写性能。如果是只读场景,对二级索引使用较多,MyIasm 比 InnoDB 更合适。

面临的问题

现代的Web应用程序可能每天需要处理数十万笔交易,而最大的问题就是如何处理这些交易的顺序。从SQL标准所定义的3大问题开始,这种大规模的数据库操作会带来一系列数据完整性问题:

1.脏读(“select”操作问题)

脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据,例如,扣款时发生转账行为导致数据读取的不一致,读提交可以避免这种行为,一个例子:

### 事务 1 ###

SELECT user_login_token_id
FROM tokens
WHERE ...


### 更新未提交到数据库 ###
UPDATE tokens
SET token_status = "INVALID"
WHERE ...

### 事务2 ###

SELECT user_login_token_id
FROM tokens
WHERE ...

如果事务2可以执行select操作,那么将会出现脏读。

2.不可重复读(“update”操作问题)

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

### 事务1 ###

SELECT post_title
FROM posts

### 读取已被修改 ###
SELECT
    post_title,
    post_content
FROM posts

### 事务2 ###

UPDATE posts
SET post_title = "something_new"
WHERE post_title = post_title

3.幻读(“insert”和“delete”操作问题)

指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样,一个例子:

### 事务 1 ###

SELECT post_title
FROM posts

SELECT
    post_title,
    post_content
FROM posts

### 事务 2 ###

INSERT INTO posts
VALUES "something_new", ...

为了避免所有这些麻烦,大多数SQL数据库遵循一套称为ACID的原则,该原则优先处理事务完整性:

  • Atomicity
  • Consistency
  • Isolation
  • Durability

数据库使用锁来防止在事务操作数据时读取或更新数据。

数据库锁是如何工作的

锁定实际上是如何发生的?让我们看一下两个并发事务,看看锁是如何工作的:

  • 事务1(T1)想要读取用户的密码并将其更改为新的密码(SELECTUPDATE
  • 交易2(T2)想要读取该用户的密码(SELECT

将最左边的列视为时间轴。当事务获取锁时,其他事务基本上将无法与锁定的数据进行交互。这些并发事务将如何一起发挥作用:

时间

操作1

操作2

1Select 第1行,并获得对第1行的锁 
2 尝试Select 第1行,但被T1的锁阻止
3Update 第1行并提交事务 
4 Select 第一行

可以看到事务1由SELECTUPDATE组成,而事务1获取到的锁会一直持续到该事务committed以后。

最常见的锁是行级锁,也就是我们上面给出的这个例子。但是锁是在数据库内存中维护的,因此在任何时候激活过多的锁在计算上都是不允许的。也就是睡,如果DBMS(数据库管理系统)内存不足,则可能会将锁升级到更高级别以释放空间。例如,如果一个数据库表在40个不同的行上具有40个不同的活动锁,则DBMS可能会将锁升级到表级别锁,以便它可以在使用更少内存的情况下继续阻塞。

锁可以存在于数据的较大部分中,甚至可以存在于整个数据库中。系统级更新可能会获取数据库锁,这将阻止数据库任何部分上的所有事务。这种锁很少见,但是表级锁却经常使用(例如上面的升级示例),而页面或文件锁(这意味着不同数据库设置之间的不同情况)也很少出现。

数据库隔离级别

  1. Read uncommitted(读未提交):最低级别,几乎不执行任何操作
  2. Read Committed(读已提交):可避免脏读的发生。
  3. Repeatable read(可重复读):可避免脏读,不可重复读的发生。
  4. Serializable(串行化):避免脏读、不可重复读、幻读的发生。

级别最低的是Read uncommitted级别。

级别越高,执行效率越低;隔离级别的设置只对当前链接有效。

Mysql的默认隔离级别是:可重复读:Repeatable read;

Oracle数据库中,只支持seralizable(串行化)级别和Read committed();默认的是Read committed级别;

读未提交

这是最低的隔离级别,几乎不执行任何操作,这意味着即使修改尚未提交,事务也可以读取由其他事务处理的数据,它不能解决上面提出任一问题。

时间

事务1

事务2

1Select 第一行 
2 Select 第1行(即使T1当前正在操作)
3Update第1行并提交事务 

事务1在启动时不获取锁,因此事务2可以执行其SELECT查询。在此示例中,一切正常:但是,如果T2从T1更新后进行了SELECT查询,但T1被回滚(中止),那么我们手上的内容将会很脏。

已提交(避免脏读)

隔离的下一个级别是READ_COMMITTED,它在执行sql更新操作过程中添加了锁,以避免脏读。在READ_COMMITTED中,事务仅可在提交写入后读取数据。让我们使用我们的两个事务,但稍微改变一下顺序:T2将T1写入后读取数据,但是随后T1被回滚(由于某种原因)。

时间

事务1

事务2

1Select 第一行 
2Update第1行,获取锁 
3 尝试Select 第1行,但被T1的锁定阻止
4回滚事务 
5 Select 第一行

READ_COMMITTED可以避免此处的脏读:如果允许T2在时间3读取第1行,则该读取将无效;T1最终回滚,因此T2读取的数据实际上是错误的。由于在时间2获得了锁定(多亏了READ_COMMITTED!),所以一切都顺利进行,并且T2等待执行其SELECT查询。

可重复读(避免不可重复读)

最受欢迎的隔离级别是REPEATABLE_READ,它避免不可重复的读取。此隔离级别从一开始久锁定数据事务-包括SELECT查询-而不是在开始查询之后,这增加了隔离的附加层。

如果从上面回忆,那么当一个事务有两个SELECT查询,而另一个事务能够插入或更新使这两个SELECT查询显示不同结果的行时,就会发生不可重复的读取。REPEATABLE_READ隔离通过在开始第一个SELECT查询时让第一个事务获取锁来避免这种情况:

时间

事务1

事务2

1Select 第1行,获取对第1行的锁定 
2 尝试Update 第1行,但被T1的锁定阻止
3Select 第一行,提交事务 
4 Update 第1行

如果您真的很聪明,您可能会注意到REPEATABLE_READ仍然无法解决幻像读取:事务1仅获得了它正在使用的行的锁(行1)而不是全表的锁,因此事务2仍然能够插入一行,可能会出现在T1的第二个SELECT查询中。避免这种情况的唯一方法是使用比行更高的锁定级别,这是我们的下一个隔离级别。

可序列化

这是最高的隔离级别,并且很难定义:实际上,SERIALIZABLE意味着从用户的角度来看,事务看起来就像是顺序执行,而不是并发执行。换句话说,隔离是如此激烈,以至于只有在最终结果与顺序执行相同的情况下,事务才能同时执行。

SERIALIZABLE还使用范围锁来避免幻像读取。范围锁是介于锁定行和锁定表之间的一种机制:如果您正在运行SELECT带有WHERE子句的查询,则范围锁将锁定存在于所选行附近(前后)的某些行。

Wikipedia有一个很好的摘要图表,用于总结这些隔离级别如何帮助避免SQL标准读取问题:

您可以将隔离级别视为锁设置:它们确定何时获取和释放锁以及该过程的强度。大多数现代SQL数据库都将REPEATABLE_READ用作默认隔离级别。

📗参考:

https://baijiahao.baidu.com/s?id=1611918898724887602&wfr=spider&for=pc

字数:2866 发布于 1 个月前
Copyright 2018-2021 Siques