edb_stmt_level_tx v17
About statement level transaction isolation
In Oracle, when a runtime error occurs in a SQL command, all the updates on the database caused by that single command are rolled back. This is called statement level transaction isolation. For example, if a single UPDATE
command successfully updates five rows but an attempt to update a sixth row results in an exception, the updates to all six rows made by this UPDATE
command are rolled back. The effects of prior SQL commands that weren't yet committed or rolled back are pending until a COMMIT
or ROLLBACK
command is executed.
In PostgreSQL, if an exception occurs while executing a SQL command, all the updates on the database since the start of the transaction are rolled back. In addition, the transaction is left in an aborted state, and either a COMMIT
or ROLLBACK
command must be issued before another transaction can start.
If edb_stmt_level_tx
is set to TRUE
, then an exception doesn't automatically roll back prior uncommitted database updates, emulating the Oracle behavior. If edb_stmt_level_tx
is set to FALSE
, then an exception rolls back uncommitted database updates.
Note
Use edb_stmt_level_tx
set to TRUE
only when necessary, as this setting can have a negative performance impact.
Example
The following example run in PSQL shows that when edb_stmt_level_tx
is FALSE
, the abort of the second INSERT
command also rolls back the first INSERT
command. In PSQL, the command \set AUTOCOMMIT off
must be issued, otherwise every statement commits automatically, which defeats the purpose of showing the effect of edb_stmt_level_tx
.
\set AUTOCOMMIT off SET edb_stmt_level_tx TO off; INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40); INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00); ERROR: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk" DETAIL: Key (deptno)=(0) is not present in table "dept". COMMIT; SELECT empno, ename, deptno FROM emp WHERE empno > 9000;
empno | ename | deptno -------+-------+-------- (0 rows)
In the following example, with edb_stmt_level_tx
set to TRUE
, the first INSERT
command wasn't rolled back after the error on the second INSERT
command. At this point, the first INSERT
command can either be committed or rolled back.
\set AUTOCOMMIT off SET edb_stmt_level_tx TO on; INSERT INTO emp (empno,ename,deptno) VALUES (9001, 'JONES', 40); INSERT INTO emp (empno,ename,deptno) VALUES (9002, 'JONES', 00); ERROR: insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk" DETAIL: Key (deptno)=(0) is not present in table "dept". SELECT empno, ename, deptno FROM emp WHERE empno > 9000;
empno | ename | deptno -------+-------+-------- 9001 | JONES | 40 (1 row) COMMIT;
You can issue a ROLLBACK
command instead of the COMMIT
command. In that case, the insertion of employee number 9001
is rolled back as well.