mysql和postgresql数据库中ACID的使用区别

ACID，是指数据库管理系统（DBMS）在写入或更新资料的过程中，为保证事务（transaction）是正确可靠的，所必须具备的四个特性：原子性（atomicity，或称不可分割性）、一致性（consistency）、隔离性（isolation，又称独立性）、持久性（durability）。

• Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

今天在群里看到一个问题，mysql竟然不支持原子性和一致性？带着这个问题，做了以下小测试

对于比较熟悉的postgresql数据库,其ACID我是无怀疑的，测试如下

postgres=# create table tmp (id int primary key,name varchar(100));
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into tmp values(1,'test1');
INSERT 0 1
postgres=# insert into tmp values(1,'test2');
ERROR:  duplicate key value violates unique constraint "tmp_pkey"
DETAIL:  Key (id)=(1) already exists.
postgres=# select * from tmp;
ERROR:  current transaction is aborted, commands ignored until end of transaction block
postgres=# commit;
ROLLBACK
postgres=# select * from tmp;
 id | name 
----+------
(0 rows)

在postgresql数据库中，对于一个事务内，插入的两条数据，其中有一条因为主键重复导致错误了，因为ACID原则，即使commit了，事务内的所有操作都将会回滚。

在mysql数据库中进行同样的测试

mysql> create table tmp (id int primary key,name varchar(100));
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into tmp values(1,'test1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tmp values(1,'test2');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into tmp values(3,'test3');
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from tmp;
+----+-------+
| id | name  |
+----+-------+
|  1 | test1 |
|  3 | test3 |
+----+-------+
2 rows in set (0.00 sec)

在mysql数据库中，在一个事务中插入了两条记录，第一条成功，第二条因为约束失败。根据事务的原子性，整个事务要么整个成功，要么整个失败（最终一条都没有插入）。结果MySQL的默认表现竟然是允许部分成功的事务提交，也就是事务没有原子性，没有原子性就没有一致性，如果这个事务是一笔转账（先扣再加），因为某些原因失败，那这里的帐就做不平了。

那么到底mysql支不支持ACID呢？答案是支持的，但需要捕获到错误时，需要手工提交rollback，测试如下

mysql> delete from tmp;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into tmp values(1,'test1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tmp values(1,'test2');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into tmp values(1,'test3');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tmp;
Empty set (0.00 sec)

  通过上面两个mysql的例子可以看到，在一个事务中，最后执行commit的话，会提交事务中成功的操作，失败的操作就不执行，这就违反了原子性和一致了。想要实现ACID，只能捕获执行中的语句有无报错，如果有错误的话，就执行rollback，没有错误的话就进行commit来实现ACID。