Oracle Transactions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Exceptions
Error Code Reason
ORA-01453 SET TRANSACTION must be first statement of transaction
ORA-02179 valid options: ISOLATION LEVEL { SERIALIZABLE | READ COMMITTED }
ORA-08178 illegal SERIALIZABLE clause specified for user INTERNAL
Object Privileges INSERT, UPDATE, DELETE, and/or MERGE
System Privileges
CREATE SESSION    
Topics
 
COMMIT
Default Behavior COMMIT WORK WRITE IMMEDIATE WAIT;
Alter commit behavior for the system ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
ALTER SYSTEM SET COMMIT_WRITE = WAIT;
Alter commit behavior for the session ALTER SESSION SET COMMIT_WRITE = NOWAIT;
ALTER SESSION SET COMMIT_WRITE = WAIT;
Complete a transaction COMMIT [WORK];
show autocommit

CREATE TABLE t0 (
testcol  NUMBER);

INSERT INTO t0 (testcol) VALUES (1);

COMMIT;

INSERT INTO t0 (testcol) VALUES (2);

COMMIT WORK;
Comment Commit COMMIT COMMENT <comment_string_255_char>;
CREATE TABLE t (
testcol NUMBER(2));

INSERT INTO t (testcol) VALUES (1);

COMMIT COMMENT 'Committing a test record';

-- if in-doubt distributed transaction
desc dba_2pc_pending

set linesize 121

SELECT local_tran_id, global_tran_id, state, mixed, advice, tran_comment
FROM dba_2pc_pending;
Write Force

Manually force commitment of in-doubt distributed transactions
COMMIT WRITE FORCE <string>, <integer>;
INSERT INTO t (testcol) VALUES (2);

COMMIT WRITE FORCE;
Write Batch

Buffer redo
COMMIT WRITE <WAIT | NOWAIT> BATCH;
INSERT INTO t (testcol) VALUES (3);

COMMIT WRITE WAIT BATCH;
Write Immediate

Initiate immediate LWGR action
COMMIT WRITE <WAIT | NOWAIT> IMMEDIATE;
INSERT INTO t (testcol) VALUES (4);

COMMIT WRITE WAIT IMMEDIATE;
Write NoWait

Commit to return before the redo is persistent in the redo log
COMMIT WRITE NOWAIT;
INSERT INTO t (testcol) VALUES (5);

COMMIT WRITE NOWAIT;
Write Wait

Commit will not return until the corresponding redo is persistent in the online redo log
COMMIT WRITE WAIT;
INSERT INTO t (testcol) VALUES (6);

COMMIT WRITE WAIT;
 
ROLLBACK
Undo a transaction ROLLBACK [WORK];
SELECT * FROM t0;

INSERT INTO t0 (testcol) VALUES (3);

SELECT * FROM t0;

ROLLBACK;

SELECT * FROM t0;

INSERT INTO t0 (testcol) VALUES (4);

SELECT * FROM t0;

ROLLBACK WORK;

SELECT * FROM t0;
 
SAVEPOINT
Create a SAVEPOINT SAVEPOINT <savepoint id>;
See SAVEPOINT ROLLBACK Demo Immediately Below
ROLLBACK to a SAVEPOINT ROLLBACK [WORK] [TO SAVEPOINT <savepoint_name>];
CREATE TABLE t1 (
testcol  NUMBER);

DECLARE
 i INTEGER := 3;
BEGIN
 INSERT INTO t1 (testcol) VALUES (10/i);

  SAVEPOINT A;

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);
/*
  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);
*/

  COMMIT;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    ROLLBACK TO SAVEPOINT A;
    COMMIT;
END testblock;
/

SELECT * FROM t1;

TRUNCATE TABLE t1;

DECLARE
 i INTEGER := 3;
BEGIN
 INSERT INTO t1 (testcol) VALUES (10/i);

  SAVEPOINT A;

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  i := i-1;
  INSERT INTO t1 (testcol) VALUES (10/i);

  COMMIT;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    ROLLBACK TO SAVEPOINT A;
    COMMIT;
END testblock;
/

SELECT * FROM t1;
 
SET TRANSACTION
Isolation Levels SET TRANSACTION ISOLATION LEVEL <SERIALIZABLE | READ COMMITTED> [NAME <string>];
set transaction isolation level SERIALIZABLE;

set transaction isolation level READ COMMITTED;
Read Only Transactions SET TRANSACTION READ <ONLY | WRITE> [NAME <string>];
set transaction read only;

commit;

set transaction read only name 'UWTRANS';

UPDATE servers
SET srvr_id = 501
WHERE srvr_id = 900;

rollback;

set transaction read write;

UPDATE servers
SET srvr_id = 501
WHERE srvr_id = 900;

commit;
Transaction Naming SET TRANSACTION NAME <string>;
set transaction name 'UW_TRANS';
 
Distributed Transaction Inconsistent States
The following refers to use of the DBMS_TRANSACTION built-in package:

Before automatic recovery runs, the transaction may show up in DBA_2PC_PENDING as state "collecting", "committed", or "prepared". If the DBA has forced an in-doubt transaction to have a particular result by using "commit force" or "rollback force", then states "forced commit" or "forced rollback" may also appear. Automatic recovery will normally delete entries in any of these states. The only exception is when recovery finds a forced transaction which is in a state inconsistent with other sites in the transaction; in this case, the entry will be left in the table and the MIXED column will have a value 'yes'.

However, under certain conditions, it may not be possible for automatic recovery to run. For example, a remote database may have been permanently lost. Even if it is recreated, it will get a new database id, so that recovery cannot identify it (a possible symptom is ORA-02062). In this case, the DBA may use the procedure purge_lost_db_entry to clean up the entries in any state other than "prepared". The DBA does not need to be in any particular hurry to resolve these entries, since they will not be holding any database resources.

-- The following table indicates what the various states indicate about
-- the transaction and what the DBA actions should be:
State Column Global Transaction State Local Transaction State Normal DBA Action Alternative DBA Action
collecting rolled back rolled back none purge_lost_db_entry (1)
committed committed committed none purge_lost_db_entry (1)
prepared unknown prepared none force commit or rollback
forced commit unknown committed none purge_lost_db_entry (1)
forced rollback unknown rolled back none purge_lost_db_entry (1)
forced commit mixed mixed committed (2)
forced rollback mixed mixed rolled back (2)
(1) Use only if significant reconfiguration has occurred so that automatic recovery cannot resolve the transaction. Examples are total loss of the remote database, reconfiguration in software resulting in loss of two-phase commit capability, or loss of information from an external transaction coordinator such as a TP Monitor.
(2) Examine and take any manual action to remove inconsistencies, then use the procedure purge_mixed.
 
Demos
Dynamic Savepoints CREATE TABLE t2 (
testcol  NUMBER);

CREATE OR REPLACE PROCEDURE spdemo (sp_in VARCHAR2) IS

BEGIN
  INSERT INTO t2 (testcol) VALUES (1);
  SAVEPOINT A;
  INSERT INTO t2 (testcol) VALUES (2);
  SAVEPOINT B;
  INSERT INTO t2 (testcol) VALUES (3);
  SAVEPOINT C;
  INSERT INTO t2 (testcol) VALUES (4);

  EXECUTE IMMEDIATE 'ROLLBACK TO SAVEPOINT ' || sp_in;
  COMMIT;
END spdemo;
/

exec spdemo('B');

SELECT * FROM t2;

TRUNCATE TABLE t2;

exec spdemo('C');

SELECT * FROM t2;
Estimate Transaction Rate SELECT sequence#, first_change#, next_change#,
(next_change#-first_change#) AS chg_cnt,
first_time, next_time, ((next_time-first_time)*1440) AS chg_min,
((next_change#-first_change#)/((next_time-first_time)*1440)) AS chgs_per_min
FROM gv$archived_log
ORDER BY sequence#;

Related Topics
DBMS_TRANSACTION
DBMS_XA
Delete
Insert
Merge
Update

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved