详解：Postgresql&Oracle&MySQL的ACID差异

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

=========================================================================
2.--oracle的ACID实现
==默认隔离级别为 read_commited

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

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

=========================================================================
3.--mysql的ACID实现
==默认隔离级别为 read_repeatable

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

1.innoDB才有redolog。
2.事务提交后binlog一次性写入,redolog在事务进行中持续写入,默认是事务提交时刷盘,保证原子性和持久性(innodb_flush_log_at_trx_commit可改)。
3.写操作隔离用锁实现。
1).锁的分类按照功能:
读锁(共享锁),写锁(排他锁);
2).按照范围:表级,行级;
3).意向锁(表级),间隙锁(防止幻读,保证一个事务中两次查询获得的数据一致)。

4.读和写的隔离,用MVCC实现(靠每行上的隐藏列和undo实现)。
1).快照读,历史记录从undo中获取。
2).当前读,insert、update、delete获取的是最新版本数据。

=========================================================================
4.--postgresql的ACID实现
==默认隔离级别为 read_commited。

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

--MVCC实现原理
1).数据文件中存放同一逻辑行的多个行版本(称为Tuple)。
2).每个行版本的头部记录创建以及删除该行版本的事务的ID(分别称为xmin和xmax)。
3).每个事务的状态(运行中,中止或提交)记录在pg_clog文件中。
4).根据上面的数据并运用一定的规则每个事务只会看到一个特定的行版本。

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

=========================================================================
5.--隔离级别测试不可重复读

=========================================================================
5.1--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)

=========================================================================
5.2--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)

=========================================================================
5.3--ORACLE默认隔离级别RC
同上PG测试的一样,session2 update并提交,session1在事务里能查到session2提交的数据。
如果为serializable,session1始终看到的最初的记录。

=========================================================================
6.--关于事务隔离级别
oracle 支持read_commit,serializable。
mysql 支持repeatable read,read_commit,serializable,read_uncommit。
PG 支持read_commit,repeatable read,serializable,read_uncommit。

=========================================================================
7.--异常现象
幻读——事务A读到了事务B提交的数据(mysql使用间隙锁解决)(做了insert)。
脏读——读取到另一个事务未提交的数据。
不可重复读——在同一个事务中,前后两次读取的数据不一致(做了update)。