005.DB2:MySql:PG的事务不同

在事务期间出现数据操作异常（Exception）时，若不rollback，
DB2，MySQL，PostgreSQL的提交或回滚情况不同。

@史荣久 / 2012-06-21 / CC-BY-SA-3.0

1. 结论

在事务期间出现数据操作异常（Exception）时，若不rollback，
且继续进行后续操作，最后commit的情况下：

DB2,异常SQL无效，正常SQL生效，
类似于autocommit的执行效果。

MySQL，同DB2。但对异常SQL的处理机制不同，
如超长时插入截断后数据等。

PostgreSQL，事务直接失败，相当于rollback了。

2. 对象

DB2 v9.5.0.2
PostgreSQL 9.0.4
MySQL 5.0.24-max
MySQL 5.5.24

3. 脚本

-- drop
DROP TABLE TEST_TA
GO
-- create
CREATE TABLE  TEST_TA (
  ID INT NOT NULL ,
  NM CHAR(1)  ,
  PRIMARY KEY (ID) )
ENGINE = INNODB
GO

-- insert
INSERT INTO TEST_TA (ID,NM) VALUES(1,'1')  -- I1
GO
INSERT INTO TEST_TA (ID,NM) VALUES(2,'12') -- I2
GO
INSERT INTO TEST_TA (ID,NM) VALUES(3,'1')  -- I3
GO
INSERT INTO TEST_TA (ID,NM) VALUES(4,'1')  -- I4
GO

-- select
SELECT * FROM TEST_TA 
GO

-- delete
DELETE FROM TEST_TA 
GO

4. 方法

使用JDBC 对各数据库执行 SQL:create. (AutoCommit=true)
开启事务，执行 SQL:insert,吞掉I2异常，
继续执行I3，I4，commit。(AutoCommit=false)
察看结果，select，delete。(AutoCommit=true)

过程中，I2的字段超长后，得到一下信息。

MySQL：

Data truncation: Data truncated 
for column 'NM' at row 1  

DB2:

SQLCODE: -433, SQLSTATE: 22001, SQLERRMC: 12  
Message: Value "12" is too long. 

PostgreSQL：

value too long for type character(1)
current transaction is aborted, commands 
ignored until end of transaction block

执行select的结果，DB2:3,MySQL:4,PG:0

5. 用途

在需要有效数据统一提交,无效数据排除的情况下,需要细心的处理异常.

比如字段超长的这个无效数据混杂在有效数据之中,

对DB2,只需要最后commit就可以了.

对MySQL,则需要先一次排查出所有无效数据,再rollback, 最后把有效数据进行commit.

而PG,则需要循环排查无效数据和rollback,最后把有效数据进行commit.

题图：我们的电脑，从大学到工作，也游戏也生活，也赛扬也AMD，还有大头显示器。