In the third and final part of this series on ‘log file sync’ and ‘log file parallel write’ (see here for part 1 and part 2) I want to talk about the difference between a commit statement issued from PL/SQL and a commit statement issued from SQL.
When I first wrote this blog the intention was to discuss the few circumstances when a COMMIT from a PL/SQL might impact ACID properties and to explain why 99% of the time it didn’t. At the time I wrote this blog the assumption was that ACID properties could confidently be assigend to standard COMMIT statements executed from SQL (e.g. SQL*PLUS, JDBC, etc.). As you will see in the comments to this blog this is far from the case. I have annoted the false assumption in bold.
The first thing to get your head round is that PL/SQL language statements are different from SQL statements. When you issue a COMMIT statement from SQL*PLUS, from Java, C, or almost all other languages you need to refer to the “SQL Language Reference” manual for syntax and semantics. When you issue a COMMIT statement (or one of the other seven SQL statements with direct PL/SQL equivalents) from PL/SQL, however, you need to look in “PL/SQL Language Reference” manual for syntax and semantics. Sure, they do very similar things but there are subtle differences.
In the 11.2 SQL Language Reference manual, you will see this statement:
If you omit [the write clause], then the behavior of the commit operation is controlled by the COMMIT_LOGGING and COMMIT_WAIT initialization parameters, if they have been set.
The COMMIT_LOGGING and COMMIT_WAIT parameters replace the COMMIT_WRITE parameter used in previous releases. Unfortunately, the documentation for COMMIT_WRITE is at best incomplete and at worst wrong. To see what the default semantics are we need to look at the documentation for the deprecated COMMIT_WRITE parameter. Here we can see:
If this parameter is not explicitly specified, then database commit behaviour defaults to writing commit records to disk before control is returned to the client.
We also see this line:
Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.
This is poor English but obviously sounds scary. Most programmers would and should avoid using NOWAIT in their programs or overriding the default session or system initialisation settings. However, let us look at the documentation in the PL/SQL reference manual:
The default PL/SQL commit behaviour for nondistributed transactions is BATCH NOWAIT if the COMMIT_LOGGING and COMMIT_WAIT database initialization parameters have not been set.
Whoa! Suddenly the behaviour that you were warned might cause a violation of the durability of durability (that is what it says isn’t it?) is now the default behaviour! Why is something that is so dangerous from SQL suddenly safe from PL/SQL and why?
Well, as we shall see it isn’t always safe. But to understand what is safe and what is not safe let me try to expand on the scary documentation from the SQL language reference manual. Consider this piece of pseudo-code from some non-PL/SQL program:
do some DML; commit; put_on_screen('Transaction complete') ;
Imagine that the commit returned immediately without waiting for the data to be recorded on disk. You might tell the user that the transaction committed and then the database might crash causing all the DML to be lost. You have misled the user. What if, however, the ‘user’ was another computer system or database?
get a message from a remote system; do some DML; commit; send confirmation of receipt of message to remote system;
Well, a human might (or might not) realise that there was a crash around the time his message was displayed saying that his transaction completed but a remote computer system will not be so paranoid. It is highly unlikely that such a remote application would retransmit a message if it receives an explicit positive acknowledgement to the first transmission.
So this is why the default behaviour for SQL is to wait until the transaction details are recorded in the redo log on disk; it is to ensure that if the application relays the fact that the transaction is committed to a third party (human or computer) that the transaction isn’t later discovered to be ‘uncommitted’ so to speak. This is the “durability” part of ACID.
Now that we have that sorted out let us turn our attention to PL/SQL. First of all, the documentation here is “incomplete”. Let me try and expand:
- For all but the last commit statement executed before PL/SQL returns control to the caller the default PL/SQL commit behaviour for nondistributed transactions is BATCH NOWAIT if the COMMIT_LOGGING and COMMIT_WAIT database initialization parameters have not been set.
- The default behaviour for the last commit statement executed before PL/SQL returns control to the caller is to trigger a wait at the point control is returned to the caller.
- Sometimes you will experience a ‘log file sync’ wait event even if you explicitly or implicitly specify NOWAIT.
Ok, so the semantics are not only apparently dangerous but also complicated. Why?
Well let us take an example PL/SQL block and add some measurements of ‘log file sync’wait events. Let us begin by creating a test table:
CREATE TABLE T1 (dummy varchar2(100)) ;
Now our test:
SELECT total_waits FROM v$session_event WHERE sid = USERENV ('SID') AND event = 'log file sync'; BEGIN INSERT INTO t1 (dummy) VALUES ('Transaction 1'); COMMIT; INSERT INTO t1 (dummy) VALUES ('Transaction 2'); COMMIT; END; SELECT total_waits FROM v$session_event WHERE sid = USERENV ('SID') AND event = 'log file sync';
We see only one ‘log file sync’. This is the one triggered at the end of the block by the second commit. The first commit triggered no wait. However, no risk of a consistency problem arises. This is because:
- Locking will prevent either inserted row being visible to outside sessions until the redo is actually on disk and
The statement above is how databases in general are supposed to work. Oracle does not work this way, however: commits from PL/SQL or SQL are visible to other sessions before they are actually on disk. See the comments below for more discussion.
- The second transaction will have a higher SCN than the first and in the event of a crash Oracle will ensure that if the first transaction needs to be rolled back the second will be also.
This can be a bit tricky to get your head round but it is a legitimate optimisation that I believe (historians are welcome to correct me) was first introduced in IMS Fastpath many decades ago and has been copied by Oracle and other database systems subsequently.
When I have explained this to other DBAs and programmers over the years I have known when the point has been understood when I get the question:
“Yes, but what if I communicate the commit outside of the PL/SQL block by some means other than updating the database”?
This question indicates a grasp of the key concept. Unfortunately, I have always answered this question incorrectly! Let us get one easy case out of the way first:
BEGIN INSERT INTO t1 (dummy) VALUES ('Transaction 1'); COMMIT; DBMS_OUTPUT.put_line ('Transaction committed'); END;
This is actually safe because the results of DBMS_OUTPUT.PUT_LINE are invisible until after the PL/SQL block returns. So if the supposedly committed transaction is subsequently rolled back nobody will ever have known that the commit statement returned to the PL/SQL block in the first place.
But what about this:
SELECT total_waits FROM v$session_event WHERE sid = USERENV ('SID') AND event = 'log file sync'; DECLARE filehandler UTL_FILE.file_type; BEGIN INSERT INTO t1 (dummy) VALUES ('Transaction 1'); COMMIT; INSERT INTO t1@dblink (dummy) VALUES ('Transaction 2'); COMMIT; filehandler := UTL_FILE.fopen ( 'DATA_PUMP_DIR', 'test_file.txt', 'W' ); UTL_FILE.putf ( filehandler, 'A transaction committed\n' ); UTL_FILE.fclose (filehandler); INSERT INTO t1 (dummy) VALUES ('Transaction 3'); COMMIT; END; SELECT total_waits FROM v$session_event WHERE sid = USERENV ('SID') AND event = 'log file sync';
You might have to run this a couple of times as there is sometimes an ‘extra’ ‘log file sync’ wait event but most of the time there will only be one wait for the entire PL/SQL block. The use of a database link doesn’t mean there is a distributed transaction because there are no changes to the local database in the transaction. If the local database crashes neither the update to the text file nor the update to the remote database will be “rolled back” so an inconsistency can arise after all.
Until I started to prepare for writing this blog I had always assumed that whenever a remote database was updated or a package such as UTL_FILE or UTL_HTTP was called then a ‘log file sync’ would occur at that point. This is such a fundamental requirement of database consistency, it never occurred to me to check. Not only that, but the PL/SQL engine knows enough to do this easily. However, even in 11.2 this is not the case. You can protect against these inconsistencies by changing your PL/SQL COMMIT statements (the last being optional) to ‘COMMIT WORK WRITE WAIT’ statements.
So when is it safe to use COMMIT WORK WRITE NOWAIT (either explicitly or not)?
- If the only thing you do between ‘commit number one’ and ‘commit number two’ is issue queries and/or updates to the local database it is safe for ‘commit number one’ to use NOWAIT. This is true both for PL/SQL and SQL.
- If you commit a transaction and then your program terminates completely then it is safe to use NOWAIT. This is true for both SQL and PL/SQL.
- If you commit a transaction and then ‘communicate with the outside world’ then you should wait (explicitly in PL/SQL) if you care about consistency.