The syntax for the COMMIT statement is:
COMMIT [WORK] [COMMENT text];
The Commit statement commits all changes for the current session. Once a commit is issued, other users will be able to see your changes.
COMMIT
Default Behavior
Alter commit behavior for the system
Alter commit behavior for the session
Complete a transaction
Comment Commit
Write Force
Manually force commitment of in-doubt distributed transactions
Write Batch
Buffer redo
Write Immediate
Initiate immediate LWGR action
Write NoWait
Commit to return before the redo is persistent in the redo log
Write Wait
Commit will not return until the corresponding redo is persistent in the online redo log
COMMIT WORK WRITE IMMEDIATE WAIT;
ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
ALTER SESSION SET COMMIT_WRITE = NOWAIT;
ALTER SYSTEM SET COMMIT_WRITE = NOWAIT;
ALTER SESSION SET COMMIT_WRITE = NOWAIT;
COMMIT [WORK]
show autocommit
testcol NUMBER);
INSERT INTO t0 (testcol) VALUES (1);
INSERT INTO t0 (testcol) VALUES (2);
COMMIT WORK;
COMMIT COMMENT <comment_string_255_char>;
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;
COMMIT WRITE FORCE <string>, <integer>;
INSERT INTO t (testcol) VALUES (2);
COMMIT WRITE FORCE;
COMMIT WRITE <WAIT | NOWAIT> BATCH;
INSERT INTO t (testcol) VALUES (3);
COMMIT WRITE WAIT BATCH;
COMMIT WRITE <WAIT | NOWAIT> IMMEDIATE;
INSERT INTO t (testcol) VALUES (4);
COMMIT WRITE WAIT IMMEDIATE;
COMMIT WRITE NOWAIT
INSERT INTO t (testcol) VALUES (5);
COMMIT WRITE NOWAIT;
COMMIT WRITE WAIT
INSERT INTO t (testcol) VALUES (6);
COMMIT WRITE WAIT;
ROLLBACK
SAVEPOINT
Return to a previous point in the transaction
SAVEPOINT <savepoint id>
The rollback short-cut 'roll' does not work with savepoint
testcol NUMBER);
i INTEGER := 3;
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);
*/
ROLLBACK TO SAVEPOINT A;
END testblock;
/
i INTEGER := 3;
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);
ROLLBACK TO SAVEPOINT A;
END testblock;
/
SET TRANSACTION
Isolation Levels
Naming Transactions
Read Only Transactions
SET TRANSACTION ISOLATION LEVEL <SERIALIZABLE | READ [COMMITED]>
[NAME <string>];
TBD
SET TRANSACTION NAME <string>;
set transaction name 'UW_TRANS';
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:
Demos
Dynamic Savepoints
Estimate Transaction Rates
testcol NUMBER);
CREATE OR REPLACE PROCEDURE spdemo (sp_in VARCHAR2) IS
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;
END spdemo;
/
exec spdemo('B');
exec spdemo('C');
SELECT sequence#, first_change#, next_change#,
next_change#-first_change# AS chg_cnt,
first_time, next_time, (next_time-first_time)*1440 ASchg_min,
(next_change#-first_change#)/((next_time-first_time)*1440) AS chgs_per_min
FROM gv$archived_log
ORDER BY sequence#;
In Oracle 10g Release 2 the COMMIT command has been enhanced with the WRITE clause to give a degree of control over the way redo information is written to the redo logs during the commit operation. This can improve performance, but it should only be used for processes that meet the following criteria:
They result in large numbers of transactions that require redo log writes.
Data loss can be tolerated in the event of an instance crash during the process.
Waiting for redo log writes is a significant part of the waits associated with the process.
The available options for the COMMIT command and the WRITE clause are displayed below.
COMMIT; COMMIT WRITE WAIT; COMMIT WRITE NOWAIT; COMMIT WRITE BATCH; COMMIT WRITE IMMEDIATE;
The meanings of the WRITE clause values are listed below.
IMMEDIATE - The commit "prods" the LGWR process by sending a message, so that the redo is written imemdiately to the redo logs.
BATCH - The writes to the redo logs are buffered.
WAIT - The commit command is synchronous. It doesn't return until the relevant redo information is written to the online redo log.
NOWAIT - The commit command is asynchronous. It can return before the relevant redo information is written to the online redo log.
The action associated with the regular COMMIT command is defined by the COMMIT_WRITE parameter, which accepts a comma-separated list of values.
COMMIT_WRITE = '{IMMEDIATE | BATCH},{WAIT |NOWAIT}'
The COMMIT_WRITE parameter can be specified at instance or session level using the ALTER SYSTEM and ALTER SESSION commands respectively.
ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='WAIT'; ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='NOWAIT'; ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE'; ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH'; ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,WAIT'; ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='BATCH,NOWAIT'; ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,WAIT'; ALTER [SYSTEM | SESSION] SET COMMIT_WRITE='IMMEDIATE,NOWAIT';
The default actions for the COMMIT_WRITE parameter and WRITE clause are the same, although at the time of writing the COMMIT_WRITE documentation incorrectly says they are not, so refer to the COMMITdocumentations, which says:
"If you specify neither WAIT nor NOWAIT, then WAIT is the default. If you specify neither IMMEDIATE nor BATCH, then IMMEDIATE is the default."
The following code examples show the enhanced commit processing in action. First we define a table for the code to populate.
CREATE TABLE commit_test ( id NUMBER(10), description VARCHAR2(50), CONSTRAINT commit_test_pk PRIMARY KEY (id) );
Next we see the variations of the WRITE clause in action. The code truncates the table and measures the time taken to populate it with a commit for each insert. This process is repeated for each variant of theWRITE clause. All the times are measured in hundredths of a second.
SET SERVEROUTPUT ON DECLARE PROCEDURE do_loop (p_type IN VARCHAR2) AS l_start NUMBER; l_loops NUMBER := 1000; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test'; l_start := DBMS_UTILITY.get_time; FOR i IN 1 .. l_loops LOOP INSERT INTO commit_test (id, description) VALUES (i, 'Description for ' || i); CASE p_type WHEN 'WAIT' THEN COMMIT WRITE WAIT; WHEN 'NOWAIT' THEN COMMIT WRITE NOWAIT; WHEN 'BATCH' THEN COMMIT WRITE BATCH; WHEN 'IMMEDIATE' THEN COMMIT WRITE IMMEDIATE; END CASE; END LOOP; DBMS_OUTPUT.put_line(RPAD('COMMIT WRITE ' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start)); END; BEGIN do_loop('WAIT'); do_loop('NOWAIT'); do_loop('BATCH'); do_loop('IMMEDIATE'); END; / COMMIT WRITE WAIT : 129 COMMIT WRITE NOWAIT : 86 COMMIT WRITE BATCH : 128 COMMIT WRITE IMMEDIATE : 128 PL/SQL procedure successfully completed. SQL>
Next we see the variations of the COMMIT_WRITE parameter in action. This example follows the format of the previous example, but the COMMIT_WRITE parameter is altered for each run and a standard commit is issued.
SET SERVEROUTPUT ON DECLARE PROCEDURE do_loop (p_type IN VARCHAR2) AS l_start NUMBER; l_loops NUMBER := 1000; BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET COMMIT_WRITE=''' || p_type || ''''; EXECUTE IMMEDIATE 'TRUNCATE TABLE commit_test'; l_start := DBMS_UTILITY.get_time; FOR i IN 1 .. l_loops LOOP INSERT INTO commit_test (id, description) VALUES (i, 'Description for ' || i); COMMIT; END LOOP; DBMS_OUTPUT.put_line(RPAD('COMMIT_WRITE=' || p_type, 30) || ': ' || (DBMS_UTILITY.get_time - l_start)); END; BEGIN do_loop('WAIT'); do_loop('NOWAIT'); do_loop('BATCH'); do_loop('IMMEDIATE'); do_loop('BATCH,WAIT'); do_loop('BATCH,NOWAIT'); do_loop('IMMEDIATE,WAIT'); do_loop('IMMEDIATE,NOWAIT'); END; / COMMIT_WRITE=WAIT : 141 COMMIT_WRITE=NOWAIT : 90 COMMIT_WRITE=BATCH : 78 COMMIT_WRITE=IMMEDIATE : 94 COMMIT_WRITE=BATCH,WAIT : 139 COMMIT_WRITE=BATCH,NOWAIT : 78 COMMIT_WRITE=IMMEDIATE,WAIT : 133 COMMIT_WRITE=IMMEDIATE,NOWAIT : 87 PL/SQL procedure successfully completed. SQL>
One of the primary tests for DBMS reliability is what's known as the ACID test. ACID-compliant systems are as close as you can get to guaranteed not to lose your data. Essentially, as long as your database files are intact, you are guaranteed that your data is consistent. This is not true for non-ACID compliant systems. Non-ACID-compliant systems are vulnerable to data inconsistency, and generally aren't taken seriously for any application where data integrity is important. Now, in 10gR2, Oracle offers us the option to break its ACID compliance.
ACID stands for Atomicity, Consistency, Isolation and Durability.
Atomicity, the first property, doesn't refer to the tiny particles that make up matter, but to the other meaning of the word "atomic": indivisible. Atomicity is a short-hand way of saying that when a transaction that changes the data occurs, either the whole transaction occurs, or none of the transaction occurs. In other words, each transaction is "atomic".
Atomicity is implemented in Oracle (and most other DBMSs) via commit-rollback. If I issue several update statements as part of a transaction, then commit that transaction, I am guaranteed that all of that transaction has gone through. Oracle does not return from a COMMIT statement until the redo log changes corresponding to that commit have been physically written to disk, not just to buffer. Similarly, if I issue a ROLLBACK, then none of the transaction goes through.
Durability, the final property in the ACID test, means that any changes made by committed transactions are persistent: they are permanent, and if a failure occurs, the information in the database will reflect all transactions committed before the failure. A durable DBMS must be atomic, but not all atomic DBMSs are durable.
In Oracle 10gR2, you can now choose to break this fourth property. Oracle's new asynchronous commit feature essentially allows you to trade durability for speed on a transaction-by-transaction basis. You can choose to break ACID compliance in this way by default, at the instance level; or you can choose to break it at the individual transaction level.
By default, Oracle's commits are durable. Oracle writes your changes to disk and doesn't return control of the session to you until it's done. The normal commit process is as follows:
User begins transaction.
While user is issuing DML, Oracle generates redo entries corresponding to the data changes. These redo entries are buffered in memory while the transaction is occurring.
When the user issues a COMMIT, Oracle immediately writes this buffered redo to disk, along with redo for the commit. Ie, a disk I/O is forced.
Oracle does not return from the commit until the redo has been completely written to disk (to the online redo log).
There are two important aspects to note here: the redo information is written to disk immediately, and the session waits for the process to complete before returning.
Oracle now lets you change both of these aspects. You can let the log writer write the redo information to disk in its own time, instead of immediately; and you can have the commit return to you before it's completed, instead of waiting.
You can change Oracle's commit behavior at the statement level by simply specifying one or both of the new options above with your commit. Use the new WRITE clause of the commit statement for this purpose. For example:
COMMIT WRITE BATCH NOWAIT ;
will commit your transaction without forcing a disk I/O, and will return control to you without waiting for the commit operation to complete.
The full syntax of the new WRITE clause is:
COMMIT [WRITE [IMMEDIATE | BATCH] [WAIT | NOWAIT] ]
By default, if no WRITE clause is specified, a naked COMMIT is equivalent to
COMMIT WRITE IMMEDIATE WAIT;
You can even change the default behaviour of COMMIT at the instance level, so that the default behaviour of a naked COMMIT statement is not COMMIT WRITE IMMEDIATE WAIT. A new initialization parameter, COMMIT_WRITE, lets you set default commit immediacy to IMMEDIATE or BATCH, and default commit wait to WAIT or NOWAIT:
COMMIT_WRITE='{ IMMEDIATE | BATCH } , { WAIT | NOWAIT }'
Compromising the "D" in your database's ACID compliance gets you one thing: speed. A normal commit forces a disk I/O and does not return until the commit is complete. You'll save some time if you batch your commits (not forcing the disk I/O) and don't wait until the commits actually complete. If you don't actually care if the data you're writing is permanent, but you do care that your writes be rapid, then you might find this feature useful.
It's worth noting that this new feature is not the only way to reduce the amount of time your application spends committing data. A properly designed application should normally batch its commits by design, ie. your application should not be continually committing small amounts of data. If your application is continually issuing small commits, you should look at your code to see if this design is really necessary, or if you might be able to batch your commits in the code. For example, you might elect to save web-entered data to the database at the end of several screens instead of at each screen. Or, if your commits are taking a long time because of complex indexes and triggers, you might consider saving entered data to an intermediate, raw-data table and batching writes from that table to the schema tables, rather than writing directly to the schema tables while the user waits on the commit.
The obvious danger of asynchronous commits is that your DBMS is no longer ACID complient. Specifically, in using an asynchronous commit, you have traded durability for speed. An asynchronous commit returns before the data has actually been written to disk, so if the database crashes before some buffered redo is written to the logs, or a file I/O problem prevents the log writer from writing buffered redo to a non-multiplexed log, then the commit is lost.
There is also a danger which applies only to Real Applications Clusters. The COMMIT_WRITE initialization parameter is documented as being able to hold different values for different instances in a RAC. This could be useful if you have services defined on the cluster and have one service for which durability is not important. However, I would adviseextreme caution, because if COMMIT_WRITE has different values for different instances in your cluster, load balancing or failover of your services could have unexpected and disastrous results.
Besides the above dangers, there's one potentially serious drawback to this new feature. As far as I can determine, there is no initialization parameter to let the DBA disable asynchronous commit. Nor does there seem to a way to keep any given database user from executing an asynchronous commit.
This could be a serious data reliability issue for systems that have knowledgeable ad-hoc users modifying data directly. Any user can issue an asynchronous commit for their work, which means that work could potentially disappear in case of system failure -- a real problem if outside processes assume the work has gone through. Fortunately, this kind of setup, with ad-hoc users directly issuing SQL against the database as part of the business workflow, is vanishingly rare today.
There's some evidence that PL/SQL has been using asynchronous commits in a limited way before 10gR2. href="http://www.oracledba.co.uk/tips/lgwr_dilemma.htm">This article by Connor McDonald, at oracledba.co.uk, provides a case study against a pre-10gR2 database in which a series of successive commits within a loop appear to be batched. What prevents this from
breaking durability, suggests McDonald, seems to be that only the PL/SQL code block knows how many commits are supposed to have been executed at any given point -- as long as PL/SQL waits for the last commit to fully complete before the PL/SQL block returns, durability is, for all intents and purposes, preserved.