理解数据库事务隔离级别

原创 _ 2024-12-02

461

1、数据库ACID

在数据库管理系统（DBMS）中，事务是指一系列操作的集合，这些操作必须作为一个单一的、不可分割的工作单元执行。事务的目标是确保数据库的完整性和一致性，尤其是在面对系统故障或并发访问的情况下。在讨论数据库事务时，通常会提到ACID四个核心原则，代表事务的四个关键特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。这些特性共同定义了事务的行为以及它们如何与数据库的其他操作进行交互，从而保障数据库系统的可靠性和数据的安全性。

原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
一致性（Consistency）：事务开始前和结束后，数据库都必须处于一致的状态。
隔离性（Isolation）：事务的执行不应受其他事务的干扰。
持久性（Durability）：一旦事务提交，其结果应该永久保存。

2、事务隔离性

在数据库系统中，事务的并发执行可以导致一些不希望发生的情况，比如脏读（Dirty Read）、不可重复读（Non-repeatable Read）和幻读（Phantom Read）。这些问题都涉及到一个事务正在进行时，另一个事务读取或修改了未提交的数据，从而影响到前者的操作结果。为了解决这些问题，数据库管理系统提供了不同的隔离级别来平衡性能和数据一致性。

脏读：一个事务读取一个并发未提交的事务写入的数据。
不可重复读：一个事务重新读取它以前读过的数据并发现数据已被另一个事务修改（自初始读取以来已提交）。值数据行内容
幻读：事务重新执行返回一组满足搜索条件的行的查询，并发现由于另一个最近提交的事务，满足条件的行集已经发生变化。主要值行数变化
序列化异常：成功提交一组事务的结果与每次运行这些事务的所有可能顺序不一致。

3、事务隔离级别

事务的隔离性是为了保证在多用户并发环境中，每个事务能像独立执行一样，避免互相影响。数据库系统通过锁机制、时间戳排序等技术实现不同隔离级别下的并发控制，从而确保事务的正确性和系统的稳定性。

读未提交：可以读到未提交事务的数据，该模式下会出现脏读、不可重复读、幻读、序列化异常
读已提交：解决了脏读(既读取到未提交数据)问题，但是只要已经提交的数据就能读取到，那么就是能够读取到其它事务已经提交(不可重复读)、幻读(其它事务增加行数)、序列化异常问题
可重复读:可以重复读取事务开始第一次读取的数据(还是读取了原来的数据)，那么就不会读取其它事务修改的数据(行数和字段内容)
序列化:按顺序执行事务，解决了所有问题

4、隔离配置方式

oracle

SET TRANSACTION ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }

oracle数据库支持序列化和读已提交，默认未读已提交，那么它可以解决读未提交为题，但是处理不可重复读的问题。

mysql

 REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED、SERIALIZABLE

mysql默认事务隔离级别为可重复读，那么默认级别情况下已经解决了读未提交、不可重复读的问题，单可重复的隔离级别也会导致幻读。

postgresql

SERIALIZABLE、REPEATABLE READ、READ COMMITTED、READ UNCOMMITTED

postgresql的默认隔离级别显示为读已提交，官方文档也说支持读未提交隔离级别，但是在读未提交隔离级别下依然可以实现隔离读未提交。以下是postgresql官方文档给出的各隔离级别可以解决的隔离问题。

隔离级别	脏读			不可重复读		幻读				序列化异常
读取未提交	允许，但不适用于PG	可能的	可能的			可能的
读已提交	不可能			可能的	可能的			可能的
可重复读	不可能			不可能	允许，但不适用于 PG	可能的
可序列化	不可能			不可能	不可能			不可能

5、测试隔离级别

1、读未提交

可以实现读未提交隔离级别的只有postgresql和mysql，而postgresql所实现的读未提交隔离级别与读已提交一致，所以只能在mysql中复现读未提交情况。oracle不支持读未提交隔离级别。

1、postgresql

--session 1
postgres=# commit;
COMMIT
postgres=# SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET
postgres=# show transaction_isolation;
 transaction_isolation 
-----------------------
 read uncommitted
(1 row)

postgres=# select * from t1;
 id |    name    
----+------------
  1 | yc        
  4 | gy        
  3 | szs       
  2 | yyy       
(4 rows)

设置事务隔离级别未读未提交

--session 2
postgres=# update t1 set name='wz' where id=2;
UPDATE 1

--session 1 
postgres=# select * from t1;
 id |    name    
----+------------
  1 | yc        
  4 | gy        
  3 | szs       
  2 | yyy       
(4 rows)

依然无法实现脏读，看不到其它事务为提交的数据

--session 2
postgres=# commit;
COMMIT
postgres=# select * from t1;
 id |    name    
----+------------
  1 | yc        
  4 | gy        
  3 | szs       
  2 | wz        
(4 rows)

--session 1


postgres=# select * from t1;
 id |    name    
----+------------
  1 | yc        
  4 | gy        
  3 | szs       
  2 | wz        
(4 rows)

经过测试，postgresql数据库虽然支持读未提交隔离级别，但是即使设置隔离级别未读未提交，依然不存在脏读。也就是说postgresql的读未提交隔离级别与读已提交隔离级别一样，满足读已提交隔离级别。

2、mysql测试读未提交

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

可以看到mysql数据库的默认隔离级别是可重复读

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+---

transaction-isolation =  READ-UNCOMMITTED
transaction-read-only = OFF

调整事务隔离级别

mysql> show variables like '%transaction_isolation%';
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.01 sec)

已经设置为读未提交

mysql> show variables like '%commit%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| autocommit                              | OFF               |

关闭自动提交

--session 1
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yc   |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

--session 2

mysql> update t1 set name='yyy' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
未提交
--session 1

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yc   |
|    2 | yyy  |
+------+------+
2 rows in set (0.00 sec)

--session 2

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

--session 1

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yc   |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

可以看到在mysql的读未提交模式中，session1会话可以看到其session2会话未提交的数据，当session2回滚后session1查询结果又改变。也就是说session1读取了脏数据，既所谓的脏读。

3、oracle

SQL> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
                                     *
ERROR at line 1:
ORA-02179: valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }

只可以设置读已提交和序列化，不可以设置读未提交和可重复读

2、读已经提交

oracle、mysql、postgresql数据库均支持读已提交隔离级别，切oracle与postgresql的默认隔离级别均为读已提交。读已提交隔离模式下虽然解决了读取其它事务未提交的数据的问题，但依然无法解决重复读取事务开始时间的相同数据，既可重复读的问题，那么会导致事务执行期间如果其它并发事务提交了数据，前后两次读取数据不一致的情况，既所谓的可重复读问题。

1、postgresql

测试1

postgres=# SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# commit;
ROLLBACK
postgres=# SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET

postgres=# show transaction_isolation ;
 transaction_isolation 
-----------------------
 read committed
(1 row)

--session 1

postgres=# begin;
WARNING:  there is already a transaction in progress
BEGIN
postgres=# select * from t1;
 id |    name    
----+------------
  1 | yc        
  4 | gy        
  3 | szs       
  2 | wz        
(4 rows)

postgres=# update t1 set name='yyyy' where id=2;
UPDATE 1

--session 1

postgres=# select * from t1;
 id |    name    
----+------------
  1 | yc        
  4 | gy        
  3 | szs       
  2 | wz        
(4 rows)

--session 2

postgres=# commit;
COMMIT

--session 1

postgres=# select * from t1;
 id |    name    
----+------------
  1 | yc        
  4 | gy        
  3 | szs       
  2 | yyyy      
(4 rows)

postgres=# end;
COMMIT

测试2

--sesson 1
postgres=# begin;
BEGIN
postgres=# select count(*) from t1;
 count 
-------
     4
(1 row)

--session 2
postgres=# insert into t1 values(5,'sss');
INSERT 0 1


--session 1
postgres=# select count(*) from t1;
 count 
-------
     4
(1 row)

--session 2

postgres=# commit;
COMMIT

--session 1

postgres=# select count(*) from t1;
 count 
-------
     5
(1 row)

由上面的测试可以看到，当隔离模式为读已提交时，事务只能读到已经提交的事务，无法读到其它会话未提交的事务。那么读已提交解决了读未提交隔离模式下的脏读问题。但是他在事务过程中，其它事务修改的数据(不可重复读)、增加的行数(幻读)只要是提交的，都可以读取，也就是依然存在不可重复读与幻读问题。

3、可重复读

为了保证事务运行期间前后两次读取的数据相同，mysql通过间隙锁的方式实现了可重复度的隔离级别。在事务执行期间，不论其它并发事务如何修改数据，本事务读取的永远是相同的数据。但是也因为事务前后两次读取相同数据，那么在事务执行期间其它并发事务如果修改并提交了其中的数据，本事务是读取不到，这就导致了很魔幻的事情，既所谓的幻读。

测试1

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yc   |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

mysql> show variables like '%transaction_isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

--session 1
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yc   |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

--session 2

mysql> update t1 set name='yyyy' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--session 1

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yc   |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

--session 2

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

--session 1

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yc   |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yyyy |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

--session 1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

--session 2


mysql> insert into t1 values(3,'zzz');
Query OK, 1 row affected (0.00 sec)

--session 1

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

--session 2

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

--session 1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

测试3

--session 1
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

--session 2


mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.01 sec)

--session 1

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.01 sec)

--session 2
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yyyy |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)


--session 1

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

测试4

--session 1
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yyyy |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

--session 2
mysql> create unique index idx_t1 on t1(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(3,'a');
Query OK, 1 row affected (0.00 sec)

--session 1
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yyyy |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

--session 2

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

--session 1
mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | yyyy |
|    2 | wz   |
+------+------+
2 rows in set (0.00 sec)

mysql> insert into t1 values(3,'a');
ERROR 1062 (23000): Duplicate entry '3' for key 't1.idx_t1'

看不到第三行数据缺无法insert，提示主键冲突，很玄幻。

通过上面测试可以看到session 1在自己的事务过程中，不论其它会话如何修改数值、插入行数、提交，session 1用于只能读到自己会话开始时候的数据。这样就解决了不可重复读的问题，也就是一直在读取他会话开始时的那份快照，但是insert、delete提交行数依然无法读到，那么当前时间读取的数据很玄幻，既所谓的幻读。

墨力计划数据库 oracle mysql pg

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

理解数据库事务隔离级别

1、数据库ACID

2、事务隔离性

3、事务隔离级别

4、隔离配置方式

5、测试隔离级别

1、读未提交

1、postgresql

2、mysql测试读未提交

3、oracle

2、读已经提交

1、postgresql

3、可重复读

评论