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#; |