Oracle Transactions
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 SAVEPOINT Distributed Transactions
ROLLBACK SET TRANSACTION Demos
 
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;
Transaction Rate Estimate SELECT sequence#, (next_change#-first_change#) AS chg_cnt,
round(((next_time-first_time)*1440),0) AS chg_min,
(round((next_change#-first_change#)/((next_time-first_time)*1440),0)) AS chgs_per_min
FROM gv$archived_log
ORDER BY sequence#;

 SEQUENCE#    CHG_CNT    CHG_MIN CHGS_PER_MIN
---------- ---------- ---------- ------------
        32     147610       1242          119
        33     101744         17         6129
        34      40690       1564           26
        35          3          0          180
        36          4          0           60
        37          3          0           45
        38      62689        836           75
        39      74131        980           76
        40      74522       1868           40
        41      65872       1508           44
        42     137454        421          326
        43      72489       2663           27
        44      82490       2185           38
        45      59292       1355           44
        46      60250       1440           42
        47      45102        765           59
        48      64710       1497           43
        49      67982       2058           33
        50      54687        780           70
        51      47715        734           65
        52      54627       1426           38
        53      74496       1454           51
        54      59095       2308           26
        55      48682       1163           42
        56      62343        834           75
        57      62811       2414           26
        58      37951        670           57
        59      38753       1960           20

Related Topics
DBMS_TRANSACTION
DBMS_XA
Delete
Insert
Merge
Update
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx