Chapter 7 Transaction behavior

Most of the transaction behavior are exactly same, however the below stuff is not.

7.1 Handled Statement Failure.

create table t (a int primary key, b int); begin; insert into t values(1,1); insert into t values(1, 1); commit;

Oracle : commit can succeed. t has 1 row after that.

PostgreSQL: commit failed due to the 2nd insert failed. so t has 0 row.

7.2 DML with Subquery

Case 1:

``` create table dml(a int, b int); insert into dml values(1, 1), (2,2);

-- session 1: begin; delete from dml where a in (select min(a) from dml);

--session 2:
delete from dml where a in (select min(a) from dml);

-- session 1:
commit; ```

In Oracle: 1 row deleted in sess 2. so 0 rows in the dml at last.

In PG : 0 rows are deleted in sess 2, so 1 rows in the dml at last.

Oracle probably detects the min(a) is changed and rollback/rerun the statement.

The same reason can cause the below difference as well.

``` create table su (a int, b int); insert into su values(1, 1);

  • session 1: begin; update su set b = 2 where b = 1;

  • sess 2: select * from su where a in (select a from su where b = 1) for update;

  • sess 1: commit; ```

In oracle, 0 row is selected. In PostgreSQL, 1 row (1, 2) is selected.

A best practice would be never use subquery in DML & SLEECT ... FOR UPDATE. Even in Oracle, the behavior is inconsistent as well. Oracle between 11.2.0.1 and 11.2.0.3 probably behavior same as Postgres, but other versions not.