CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Transactions
Version 11.1
 
COMMIT
Default Behavior COMMIT WORK WRITE IMMEDIATE WAIT;
Alter commit behavior for the system ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
ALTER SESSION SET COMMIT_WRITE = NOWAIT;
Alter commit behavior for the session ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
ALTER SESSION SET COMMIT_WRITE = NOWAIT;

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] [TO SAVEPOINT <savepoint_name>]
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

Return to a previous point in the transaction
SAVEPOINT <savepoint id>

The rollback short-cut 'roll' does not work with savepoint
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 [COMMITED]>
[NAME <string>];
TBD

Naming Transactions
SET TRANSACTION NAME <string>;
set transaction name 'UW_TRANS';

Read Only Transactions
SET TRANSACTION READ <ONLY | WRITE>
set transaction read only;

commit;

set transaction read only;

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;
 
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 Rates 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
Update
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [42 users online]    © 2010 psoug.org