数据库事务级别

数据库事务级别

数据库的事务级别

我们都知道数据库存在4个级别。

• READ_UNCOMMITTED
• READ_COMMITTED
• REPEATABLE_READ
• SERIALIZABLE

PG官方介绍

这种问题也是面试当中经常问到的，非常easy。对应每种级别解读了什么问题，可能某些同学也存在疑惑了。我恰好看到postgresql官方的介绍觉得不错，引用下。

The phenomena which are prohibited at various levels are:

dirty read
A transaction reads data written by a concurrent uncommitted transaction.

nonrepeatable read
A transaction re-reads data it has previously read and finds that data has been 
modified by another transaction (that committed since the initial read).

phantom read
A transaction re-executes a query returning a set of rows that satisfy a search condition and finds
 that the set of rows satisfying the condition has changed due to another recently-committed 
transaction.

serialization anomaly
The result of successfully committing a group of transactions is inconsistent with all possible 
orderings of running those transactions one at a time.

The SQL standard and PostgreSQL-implemented transaction isolation levels are 
described in Table 13-1.

Table 13-1. Transaction Isolation Levels

Isolation Level	Dirty Read	Nonrepeatable Read	Phantom Read	Serialization Anomaly
Read uncommitted	Allowed, but not in PG	Possible	Possible	Possible
Read committed	Not possible	Possible	Possible	Possible
Repeatable read	Not possible	Not possible	Allowed, but not in PG	Possible
Serializable	Not possible	Not possible	Not possible	Not possible

事务级别解释

pg的文档介绍了：

• 脏读：个事务读取到另外一个事务未提交的数据。
• 不可重复读：一个事务以同样的条件再次读取数据时，发现数据被其他一个事务修改了。
• 幻读：个事务再次执行一个不改变查询条件的查询语句时发现返回的结果集增加了。
• 序列化异常：一个事务组的返回结果与执行顺序不一致。

事务级别理解示例

这里 “不可重复读” 与 “幻读” 两个概念不太好理解。

举例说明下：

不可重复读:
1、T1：select * from users where id=1;
2、T2：update users set name="test" where id=1
3、T1: select * from users where id=1;
T1事务两次结果不一致。

幻读：
1、T1：select * from users ; -- null
2、T2：insert into `users`(`id`, `name`) values (1, 'XXX');
3、T1：select * from users ; -- 存在数据
T1查询users表不存在数据，T1再次查询数据不为空，这个叫幻读。

转载于:https://my.oschina.net/u/1436757/blog/1648597