Tony’s Oracle Tips

August 4, 2011

‘log file sync’ and ‘log file parallel write’ – part 3

Filed under: ACID — tonyhasler @ 5:25 pm

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.
About these ads

23 Comments »

  1. Hi Tony,

    could you elaborate a bit on the following part:

    > Locking will prevent either inserted row being visible to outside sessions until the redo is actually on disk

    Why should locking prevent the first inserted row from being visible if the mode is NOWAIT BATCH inside the PL/SQL block as outlined? Shouldn’t the transaction be visible immediately after the COMMIT but independently from any redo being written or not? The locks should be released immediately by the COMMIT I would assume.

    Randolf

    Comment by Randolf Geist — August 5, 2011 @ 12:34 pm | Reply

    • Randolph,

      I think I understand how log writers are supposed to work. I wrote the log writer for the VMS operating system and was required to learn the computer science before doing so. I started writing this reply based on this knowledge but then decided to run a couple of tests just to be sure. What I discovered shook me to the bone!

      I suspended my 11.2 log writer with ‘kill -STOP’. I then inserted a row in one SQL*PLUS session and committed it with the default WAIT behaviour.

      As you might expect the SQL*PLUS session hung.

      To my horror, when I selected from the table from a second SQL*PLUS session, the row appeared!

      So you are right to correct me. However, the Oracle logic is just plain wrong. It seems that Oracle transactions are ACI not ACID and there is no way to code around this.

      If someone can point out the flaw in my logic I would be relieved to post a correction.

      Comment by tonyhasler — August 5, 2011 @ 2:18 pm | Reply

      • One idea: When the block was flushed out by DBWR because LGWR was dead, then we would get back the D in ACID. If you could repeat the test case, you would need to pull the plug (kill all oracle processes) after the SQL*Plus session hung. Then, if after the restart of the DB the row would be still there, everything would be fine, I think.

        Comment by Marcus Mönnig — August 5, 2011 @ 3:10 pm

      • Marcus,

        DBWR cannot write any block that hasn’t been written out to the redo log. This is called “write ahead logging” and this is something that Oracle definitely adheres to. I have tested this and committed transactions visible to outside sessions definitely become uncommitted after a crash if LGWR is hung.

        Comment by tonyhasler — August 5, 2011 @ 5:46 pm

      • Tony,

        I can understand that you find the result of your particular test using the WAIT option questionable, but I was specifically referring to the NOWAIT case – so how would you expect Oracle to treat the NOWAIT case with regards to locking and redo writing?

        Randolf

        Comment by Randolf Geist — August 5, 2011 @ 3:36 pm

      • Randolph,

        What I would expect is that unlocking, block cleanout and all that should all be done after the commit has been hardened on disk, regardless of whether the committing application waits for that to happen or not. It turns out that the behaviour is, indeed, independent of whether the application waits or not but that behaviour is that unlocking, block cleanout and all that occurs before the commit is hardened on disk. I certainly did not expect that. Today, I have seen transactions visible to the world become uncommittted after a crash.

        I haven’t tried this on other versions of the database. I am going to try over the weekend. I still can’t believe my eyes.

        Comment by tonyhasler — August 5, 2011 @ 5:53 pm

      • I now tried this myself on Windows. When I killed the LGWR thread, I immediatley get an “PMON: terminating instance due to error 470″ (ORA-470 is “LGWR process terminated with error”) and the inserted row is not visible after a restart.

        However when I suspend the LGWR thread, then commit and then kill the Oracle process, I see the same behaviour as Tony saw. The row is visible from another session after suspending LGWR and committing and it is still there after a restart. I agree that this is disturbing.

        Comment by Marcus Mönnig — August 6, 2011 @ 8:10 am

      • Marcus,

        I don’t know if you made a typo. but it is not disturbing to note that when a transaction is committed before a crash that it remains commited after a crash. I too have done a test on Windows (10.2.0.3) and when I 1) suspended LGWR, 2) inserted and committed a row 3) viewed the inserted row from another session 4) killed the LGWR thread and 5) restarted the service and instance I found that the row disappeared.

        The 11gR2 database concepts manual states in chapter 10:

        “Changes made by the transaction are permanent and visible to other users only after a transaction commits.”

        This means that other users should not see the effects of a transaction until they are permanent. This seems never to have been the case in the Oracle database.

        The TPC-C benchmark specification also states that:

        “The tested system must guarantee durability: the ability to preserve the effects of committed transactions and insure database consistency after recovery from any one of the failures listed in Clause 3.5.3″

        I begin to wonder if this means that Oracle’s benchmark results can now be challenged.

        Comment by tonyhasler — August 6, 2011 @ 12:51 pm

    • Yes, sorry, that was a typo. The row is NOT there after the restart. For the record, here is the test case that I just did again:

      1.) CREATE TABLE MMTEST_REDO (a number);
      2.) Suspend LGWR thread (lookup ThreadID from V$PROCESS and suspended in ProcessExplorer)
      3.) Session 1: Insert a row and commit ( insert into MMTEST_REDO values(1); commit;) -> Session hangs
      4.) Session 2: Query if the row is there ( SELECT * FROM MMTEST_REDO;) -> Yes!
      5.) Kill Oracle.exe process in ProcessExplorer
      6.) Start instance (by starting the Oracle Windows service)
      7.) Query if the row is there ( SELECT * FROM MMTEST_REDO;) -> No!

      The fact that the row is there in point could be explained by some weird failover commit technique in case LGWR doesn’t work, e.g. through buffer flushes by DBWR. But not having the row after 7.) is mind boggling. I personally don’t care to much about TPC benchmarks and the compative implications, but more about the ACID principles.

      Then again, though this looks simple and plain wrong, I have been wrong so many times, I still have the feeling that there is some acceptable explanation…

      Do you plan to open an Oracle SR for this? Otherwise I’d love to…

      Comment by Marcus Mönnig — August 6, 2011 @ 1:43 pm | Reply

      • “….4.) Session 2: Query if the row is there ( SELECT * FROM MMTEST_REDO;) -> Yes! …”

        That’s what is called a “dirty read”, correct ?

        Comment by Sokrates — August 20, 2011 @ 7:02 pm

      • Terminology in this discussion is difficult. If you consider the transaction committed (because it is visible) then no. If you consider the transaction uncomitted (because it isn’t durable) then yes. This is a corollary to the question of whether this is a violation of the D for durability in ACID (committed transaction not yet durable) or the I for isolation (uncommitted transaction visible).

        Comment by tonyhasler — August 21, 2011 @ 12:25 pm

  2. Your rewrite of how pl/sql and commits work needs a little more work.

    The pl/sql has no way of knowing which commit is “the last commit” – so you need to take that expression out of the description. A better statement would be: if a database call includes a commit then Oracle doesn’t issue a redo synch write until the call is about to complete. In your case this means it will only consider the need for a redo synch write at the end of the pl/sql block.

    I am curious about the “log file sync” even when specifying NOWAIT. Oracle is deterministic, so the presence of the wait is not an arbitrary event – my first guess would be that it relates to some sys-recursive SQL (e.g. space management).

    Your comment:”Locking will prevent either inserted row … ”

    The first row inserted will be visible to other sessions the moment you issue the first commit;
    The second row inserted will be visible to other sessions the moment you issue the second commit;
    However, neither transaction is guaranteed to be durable – that’s the effect of the pl/sql “optimisation” where a commit behaves a bit like “commit write immediate nowait”.

    Your example with the utl_file() call is not conclusive because you cite the “extra log file sync” wait. Until you can explain why it could appear (or why it’s extra) you can’t draw any conclusions about your experiment. The manuals may say that the simple “commit;” behaves like like “commit write batch nowait” – but it’s not true. If falls somewhere between “immediate nowait” and “batch nowait”. The commit will post lgwr to write (if it’s not already writing), but will not wait for it to complete.

    My guess,then, is that your anomaly of sometimes waiting on an “extra” log file sync is probably a timing thing and a “not missing” log file sync. Sometimes your session says “now is the moment to sync my log to disc”, and prepares to issue a post to lgwr. This starts with a get on the redo writing latch and a check on the start of free space – at which point your session sometimes finds that the log writer has already written that bit of buffer so it doesn’t issue a call, and you don’t see a wait. You might try tracing a few runs of your test to see where the log file sync appears.

    Your observation about being able to see a committed transaction before lgwr has written it to discis correct. It’s fairly obvious with hindsight – but it hadn’t occurred to me to think about it before. The order of action is:
    copy redo record into log buffer
    apply change to data blocks
    decide whether or not to call lgwr
    One of the reasons for calling lgwr is that the redo record is a commit record (or, if you’re on 10g, contains a commit change vector).
    But this clearly means other sessions can see the committed transaction before it’s durable.
    The only good thing about this is the window of opportunity for inconsistency is very small – you have to query the data between the commit and the moment that lgwr crashes, and lgwr doesn’t often crash.

    I hope this gets through as readable text – I’ve had a lot of trouble posting it.

    Comment by Jonathan Lewis — August 13, 2011 @ 10:10 pm | Reply

    • Jonathan,

      >> Your rewrite of how pl/sql and commits work needs a little more work.
      >>
      >> The pl/sql has no way of knowing which commit is “the last commit” – so you need to take that expression out of the description. A better statement would be: if a database call includes a commit then Oracle doesn’t issue a redo synch write until the call is about to complete. In your case this means it will only consider the need for a redo synch write at the end of the pl/sql block.
      >>

      Let me expand on this a bit.

      There are three basic types of commit statement in PL/SQL.

      – COMMIT WORK WRITE NOWAIT
      – COMMIT WORK WRITE WAIT
      – COMMIT

      There are other variances with BATCH and IMMEDIATE but let us for forget those for now.

      If the last COMMIT in an anonymous PL/SQL block (or more generally a PL/SQL invocation – let me abbreviate to PLSQI) is “COMMIT WORK WRITE WAIT” then the wait occurs at the time of the COMMIT and there is no additional wait at the end of the PLSQI. If the last COMMIT in a PLSQI is COMMIT WORK WRITE NOWAIT then there is no wait at the time of the COMMIT and no wait at the end of the PL/SQL block. Only if the last COMMIT in a PLSQI is of the default variety does a wait occur at the end of the PLSQI. In particular, if you issue two commits in a PLSQI, the first with the default behaviour and the second with NOWAIT there is no wait at any point. On the other hand, if the order of the two transactions is reversed (NOWAIT followed by default behaviour) then there is a wait.

      What I assume happens is that each commit statement in PL/SQL sets a flag indicating whether a wait should occur at the end of the PLSQI and that this flag is overwritten by any subsequent commit.

      >> I am curious about the “log file sync” even when specifying NOWAIT. Oracle is deterministic, so the presence of the wait is not an arbitrary event – my first guess would be that it relates to some sys-recursive SQL (e.g. space management).
      >>

      I have tried to investigate this using trace. Sometimes the extra waits occur at the time the COMMIT statement is issued in PL/SQL and are associated with that commit’s cursor. Sometimes the waits occur as part of an insert and are associated with the PL/SQL block. I can only guess that this is some kind of buffer exhaustion behaviour.

      >> Your comment:”Locking will prevent either inserted row … ”
      >>
      >> The first row inserted will be visible to other sessions the moment
      >> you issue the first commit; The second row inserted will be visible
      >> to other sessions the moment you issue the second commit; However, neither transaction is guaranteed to be durable – that’s the effect of the pl/sql “optimisation” where a commit behaves a bit like “commit write immediate nowait”.
      >>

      Agreed. This was an error on my part and I have amended my blog to reflect this. However, this is a reflection of a fundamental issue as discussed in other comments in this blog and in your own recent blog on the same topic. The PL/SQL optimisation and the NOWAIT flag correctly only affect the behaviour of the calling session. The visibiility to other sessions is unaffected. However, the visibility to other sessions is not what you would expect after learning about transactions in books!

      >> Your example with the utl_file() call is not conclusive because you cite the “extra log file sync” wait. Until you can explain why it could appear (or why it’s extra) you can’t draw any conclusions about your experiment. The manuals may say that the simple “commit;” behaves like like “commit write batch nowait” – but it’s not true. If falls somewhere between “immediate nowait” and “batch nowait”. The commit will post lgwr to write (if it’s not already writing), but will not wait for it to complete.
      >>

      As discussed above, the trace doesn’t fully explain the extra waits. However, they do not occur at the time of the utl_file call so I suggest that it is conclusive evidence that utl_file does not trigger a wait.

      >> Your observation about being able to see a committed transaction before lgwr has written it to disc is correct. It’s fairly obvious with hindsight – but it hadn’t occurred to me to think about it before. The order of action is:
      >> copy redo record into log buffer
      >> apply change to data blocks
      >> decide whether or not to call lgwr
      >> One of the reasons for calling lgwr is that the redo record is a commit record (or, if you’re on 10g, contains a commit change vector).
      >> But this clearly means other sessions can see the committed transaction before it’s durable.

      It seems that we have two commit points: The first is the point when the latch protecting the update of the slot in the undo segment is released (making the transaction visible to the rest of the world) and the second when the redo is written to disk (making the transaction durable).

      >> The only good thing about this is the window of opportunity for inconsistency is very small – you have to query the data between the commit and the moment that lgwr crashes, and lgwr doesn’t often crash.
      >>

      When seeing these words, some readers might think you have underestimated the sevrity of the issue. However, your own blog on this topic makes it clear that you do not.

      Every book and paper that I have ever read on transaction processing has stressed the need to ensure that the result of one transaction is not visible to another until made durable. This is surely what Andreas Reuter had in mind when he defined ACID. Though I have not read the mighty tomb he wrote with Jim Gray, I have found this quote that is also in line with Oracle’s own documentation:

      “..the transaction manager records this fact in the log, informing each resource manager that the transaction is complete. At this point the resource managers can release the locks and perform any other operations needed to complete the transaction.”

      I will expand on the severity issue is a separate blog.

      Comment by tonyhasler — August 21, 2011 @ 2:17 pm | Reply

      • > What I assume happens is that each commit statement in PL/SQL sets a flag indicating whether a wait should occur at the end of the PLSQI and that this flag is overwritten by any subsequent commit

        I think you could well be right; although it’s not safe to assume that the absence of a “log file sync” is proof that the log has not been written. Consider the following fragment:

        
        begin
                update t1 set n1 = n1 where rownum = 1;
                commit;
                dbms_lock.sleep(10);
        end;
        /
        

        The commit will trigger a post to the log writer to write (unless it’s already writing).
        The sleep is sufficiently long that the log writer will have written the current content of the log buffer to disc and adjusted the log buffer’s start of free space accordingly.
        At the end of the block the session will not wait on a log file sync because it can check the start of free space in the log buffer and see that the necessary redo log has already been written.
        As far as I can tell the session will always record a “redo synch write” in its session stats at this point even if it didn’t have to wait on a log file sync.

        Your hypothesis about the effect of the last commit in a pl/sql block being a “commit write nowait” seems to be correct, though. When I ran a quick test there was no “redo synch write”, which is suggestive; however, it’s always possible that the final statement gave the log writer time to write so that the session didn’t need to synch and that the “nowait” stopped the session from incrementing the counter. To cross-check, though, I suspended lgwr before repeating the test, and the test ran to completion – which it could not have done if the session had been checking how far lgwr had got with the previous commit.

        Comment by Jonathan Lewis — August 22, 2011 @ 10:27 am

  3. […] the comments to a recent blog posting about log file syncs, Tony Hasler has produced a stunning observation about Oracle and ACID, in […]

    Pingback by Redo « Oracle Scratchpad — August 19, 2011 @ 2:58 am | Reply

  4. […] the comments to a recent blog posting about log file syncs, Tony Hasler has produced a stunning observation about Oracle and ACID, in […]

    Pingback by Redo « Ukrainian Oracle User Group — August 19, 2011 @ 3:28 pm | Reply

  5. great article !!

    you write
    “…Until I started to prepare for writing this blog I had always assumed that whenever … 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….”

    I don’t think that the PL/SQL engine does know enough to do this easily – how could he know that package has been called which informed “the outside” ( outside the PL/SQL Virtual Machine) of a commit ?
    There are tons of ways to do this, not only UTL_FILE or UTL_HTTP.
    There could have been a java Method inside the database been called which has transported this bit of information.
    There could DBMS_PIPE has been used to transfer this information…

    What do you mean by “he PL/SQL engine knows enough to do this easily” ?

    I was aware of this problem before reading your article, however, I wasn’t aware of the fact, that you could force a synchronous commit via
    COMMIT WORK WRITE WAIT
    in PL/SQL ( you probably could also do that via “execute immediate ‘commit'; ” ? )

    I know think, it is the responsiblity of the developer to handle that correct

    Comment by Sokrates — August 20, 2011 @ 7:01 pm | Reply

    • Sokrates,

      “I don’t think that the PL/SQL engine does know enough to do this easily”

      My thought was that any package invocation would cause a wait unless it was to one of a set of a set of standard packages that were marked by an internal flag as “safe not to wait”.

      “you probably could also do that via “execute immediate ‘commit’; ”

      I had the same thought but it isn’t so. The above statement is equivalent to invoking commit directly from PL/SQL!

      Comment by tonyhasler — August 21, 2011 @ 1:02 pm | Reply

      • “…My thought was that any package invocation would cause a wait unless it was to one of a set of a set of standard packages that were marked by an internal flag as “safe not to wait”. ….”

        or (even better in my eyes): they could add a new marker keyword (SAFE_NOT_TO_WAIT for example) to PL/SQL which any procedure/function could be declared as ( like DETERMINISTIC )
        But then, it would still be the developer’s responsibility to handle this

        “…The above statement is equivalent to invoking commit directly from PL/SQL! …”
        Good to know, thanks for that !

        Comment by Sokrates — August 21, 2011 @ 7:48 pm

  6. I wonder how long back this anomaly dates – 9.2, 9.0, 8.1, 8.0, 7.x?

    Comment by Oleksandr Alesinskyy — February 17, 2012 @ 10:11 am | Reply

    • I would have to assume that something as fundamental as this goes back to the first version of Oracle. Certainly Jonathan Lewis claims to have been teaching this mechanism for the last 12 years blissfully unaware of the impliations (according to his latest book)

      Comment by tonyhasler — February 20, 2012 @ 11:00 pm | Reply

      • I would not be so sure about Oracle versions prior to 8i – there where quit a lot of commit “optimizations” in 8i 10g timeframe (all that batch, nowait, … – firstly mechanismus and then keywords). BTW, last 12 years corresponds exactly to 8i+.

        Comment by Oleksandr Alesinskyy — February 21, 2012 @ 7:03 am

  7. […] then use COMMIT WRITE IMMEDIATE NOWAIT. A good explanation of the difference in behaviour is at: http://tonyhasler.wordpress.com/2011/08/04/log-file-sync-and-log-file-parallel-write-part-3/ But I was not satisfied yet. I wanted to know the time when an insert/update happened by the user […]

    Pingback by Difference between insert/update time and commit write time. | Dreamer — July 20, 2013 @ 3:11 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 45 other followers

%d bloggers like this: