自斟自饮——3. 原子性操作

我们都知道，数据库的ACID，其中A就是Atom，原子性，也就是要么全部做完，要么全部不做。但你对这个原子性了解有多少呢？实际上，原子性应该分两个级别，语句级，以及事务级。

事务级的比较容易理解，begin work，然后开干，到最后commit还是rollback，看具体需要，这个就是事务级的原子性。这一点对于所有的现代数据库都应该没有任何异议的，我们理解起来也应该没有任何异议。

至于语句级的原子性，就是指在没有显式地启动事务。实际上，所有的数据库，除了oracle，默认都是自动提交，也就是，执行完一条普通SQL后，不需要显式地执行commit（除非你显式地begin work）。

下面的testing，是指没有显式地启动事务的前提下进行的。


（1）这个是Insert的情况

create temp table tmp_test1 (tid integer primary key); 
create temp table tmp_test2 (tid integer);

insert into tmp_test1 values (1); 

insert into tmp_test2 values (2); 
insert into tmp_test2 values (3); 
insert into tmp_test2 values (1); 
insert into tmp_test2 values (4);

目前为止，数据准备完毕。然后，

insert into tmp_test1 select * from tmp_test2;

这条SQL语句当然会出错，因为存在tid = 1的重复主键，但如果出错之后，你再去查tmp_test1，你觉得结果会是什么？还是只有一条数据？还是有三条？还是一条都没有？估计不会有四条吧？你应该不会以为是五条都齐全吧？你觉得在 Informix、oracle、PostgreSQL、MySQL的结果是不是一样的？


（2）这个是Update

先准备数据：

create temp table tmp_test1 (
   tid   integer primary key,
   tint  integer,
   tname char(10)
);

insert into tmp_test1 values (1, 0,  'A'); 
insert into tmp_test1 values (2, 1,  'A'); 
insert into tmp_test1 values (3, 5,  'A'); 
insert into tmp_test1 values (4, 10, 'A');
insert into tmp_test1 values (5, 5,  'B');

然后执行这一句update：

update tmp_test1 set tid = tint where tname = 'A';

这条SQL语句当然会出错，因为存在tid = 5的重复主键（第三条record），但如果出错之后，你再去查tmp_test1，你觉得这个表的内容会变成什么样子？你觉得在 Informix、oracle、PostgreSQL、MySQL的结果是不是一样的？


（3）下面这个是Java的

准备一个数据表，注意，这个不是临时表：

create table test1 (tid integer primary key); 

然后用Java插入数据（注意：我这里忽略了Connection的生成过程，作为资深的你应该可以明白）：

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test1 {
   
   public static void test1(Connection conn) throws SQLException {
       conn.setAutoCommit(true);    // 这里设置为自动提交
       String sql = "insert into test1 values (?)";
       PreparedStatement ps = conn.prepareStatement(sql);

       ps.setInt(1, 1);
       ps.addBatch();

       ps.setInt(1, 2);
       ps.addBatch();

       ps.setInt(1, 3);
       ps.addBatch();

       ps.setInt(1, 1);    // 注意：这里会出现唯一键，但还没有报异常
       ps.addBatch();

       ps.executeBatch();    // 注意：这里才会出现SQL异常
       ps.close();
   }
   
}

很明显，有两个1，所以重复主键，但你觉得在执行excuteBatch并出现异常后，数据库的表test1的数据是怎样的？你觉得在 Informix、oracle、PostgreSQL、MySQL的结果是不是一样的？




































实际上各个数据库对于事务级别的原子性的支持应该是大体一样的，就是要么全部完成，要么全部不完成。

但对于语句级别的原子性，不同的数据库会有不同的对待。对于Oracle、PostgreSQL、MySQL，它会保证语句级别也是原子性的。具体来说，对于一个SQL语句，要么全部完成，要么全部不完成，打个比方，我们在这两个数据库中用一条SQL把100w条数据插进一个表，当到了最后一条系统发现有问题，它不仅仅会不插入这一有问题的record，也会把之前的999999条数据都rollback回去。至于其他的数据库，SQL Server暂时没试过，Informix被证明不是语句级别的原子性。（SQL Server在默认情况下估计多半不是）

这里我附上mysql、pgsql和Informix的结果。

MySQL




PostgreSQL




Informix





甚至，这里有个更加极端的地方。我们都知道trigger这个东西，触发器。有一类触发器是在插入一条数据之前触发，（before insert）。假如，存在一个表格test1，它有一个before insert的trigger，会把一条log insert到另外一个表格test1_log里面。正常情况下，我们每插进一条数据到test1，应该就会有一条数据插进到test1_log。但假如insert到test1的数据有问题，例如唯一性重复，那之前在before insert trigger插进到test1_log的数据，到底还在不在？

这个问题其实也是语句级别原子性的问题，按照Oracle和PostgreSQL的标准，它会保证语句级别的原子性，所以test1_log的那条数据会被rollback。而informix还没有机会试过，但从之前的测试看应该是不会rollback的。

最后提提JDBC的executeBatch，实际上这个操作对于数据库来说也就是把SQL一句一句来执行，当然可能已经做了相应的优化（例如把“硬解释”的结果存储下来，以后每次都只进行“软解释”）。如果中途出错，很明显，哪一句出问题，那一句之前的都执行成功，之后都不成功，而那一句就要似乎数据库对于语句原子性的支持。

虽然上面说的都是自动提交的情况，但，即使是在显示地启动事务的情况下也一样会遇到这个问题。对待这种问题，Oracle和PostgreSQL会以一种类似潜套事务(Nested Transaction)的形式处理，就是说那条出错的语句会整条rollback，但对于这个事务是没有影响的。