postgresql&oracle&mysql的ACID

ACID是什么

• 事务的属性
  原子性： 一个事务的所有部分必须都完成，或者都不完成。
  一致性： 查询的结果必须与数据库在查询开始时的状态一致。
  隔离性： 除了作出变更的会话，其他会话都无法看到未提交的数据。
  持久性：事务一旦完成，不可丢失。

oracle的ACID实现

默认隔离级别为read_commited

属性	实现方式
原子性	undo
一致性	SCN
隔离性	UNDO
持久性	REDO/UNDO LGWR

• oracle 中DDL会导致事务的自动提交

mysql的ACID实现

默认隔离级别为read_repeatable

属性	实现方式
原子性	REDO
一致性	UNDO
隔离性	锁+MVCC
持久性	REDO

• innoDB才有redolog
• 事务提交后binlog一次性写入，redolog在事务进行中持续写入，默认是事务提交时刷盘，保证原子性和持久性(innodb_flush_log_at_trx_commit可改)
• 写操作隔离用锁实现。
  1.锁的分类按照功能：
  读锁（共享锁），写锁（排他锁）；
  2.按照范围：表级，行级；
  3.意向锁（表级），间隙锁（防止幻读，保证一个事务中两次查询获得的数据一致）
• 读和写的隔离，用MVCC实现（靠每行上的隐藏列和undo实现）
  1.快照读，历史记录从undo中获取
  2.当前读，insert、update、delete获取的是最新版本数据

postgresql的ACID实现

默认隔离级别为read_commited

属性	实现方式
原子性	MVCC
一致性	约束/RD隔离级别
隔离性	MVCC
持久性	WAL

MVCC实现原理

1）数据文件中存放同一逻辑行的多个行版本（称为Tuple）

2）每个行版本的头部记录创建以及删除该行版本的事务的ID（分别称为xmin和xmax）

3）每个事务的状态（运行中，中止或提交）记录在pg_clog文件中

4）根据上面的数据并运用一定的规则每个事务只会看到一个特定的行版本

通过MVCC读写事务可以分别在不同的行版本上工作，因此能够在互不冲突的情况下并发执行。

隔离级别测试不可重复读

PG默认隔离级别为RD

session1开一个事务，session2 update并提交，session1在事务里能查到session2提交的数据

session1

postgres=# begin ;
BEGIN
postgres=# select * from dy2;
 id | name 
----+------
  2 | b
  3 | c
  5 | c
  1 | f
(4 rows)

session2

postgres=# begin ;
BEGIN
postgres=# select * from dy2;
 id | name 
----+------
  2 | b
  3 | c
  5 | c
  1 | f
(4 rows)

postgres=# update dy2 set id =10 where name ='b'
postgres-# ;
UPDATE 1
postgres=# select * from dy2;
 id | name 
----+------
  3 | c
  5 | c
  1 | f
 10 | b
(4 rows)

postgres=# commit;
COMMIT

session1

postgres=# select * from dy2;
 id | name 
----+------
  3 | c
  5 | c
  1 | f
 10 | b
(4 rows)

MYSQL默认隔离级别RR

session2修改的数据，session1在事务里是看不到的，除非退出事务后，才能看到session2提交的数据

session1

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

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

mysql> select * from dy_test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

session2

mysql> update dy_test1 set id =10 where name ='a';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from dy_test1;
+------+------+
| id   | name |
+------+------+
|   10 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

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

session1

mysql> select * from dy_test1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.01 sec)

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

mysql> select * from dy_test1;
+------+------+
| id   | name |
+------+------+
|   10 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

ORACLE默认隔离级别RC

同上PG测试的一样，session2 update并提交，session1在事务里能查到session2提交的数据
如果为serializable，session1始终看到的最初的记录

关于事务隔离级别

• oracle 支持read_commit,serializable
• mysql 支持repeatable read,read_commit,serializable,read_uncommit
• PG 支持read_commit,repeatable read,serializable,read_uncommit

异常现象

幻读——事务A读到了事务B提交的数据（mysql使用间隙锁解决）（做了insert）

脏读——读取到另一个事务未提交的数据

不可重复读——在同一个事务中，前后两次读取的数据不一致（做了update）