Skip to Main Content
  • Questions
  • Default column value vs commit write batch nowait. When actual value is assigned?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andrey.

Asked: April 06, 2017 - 1:56 pm UTC

Last updated: April 10, 2017 - 3:43 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi Tom,

In this section I'll explain how I come up with the question..

Under normal operation, our application do next actions:
1. call util.do_some_action, but not wait for response
2. do some network calls to remote system
3. receive response from util.do_some_action and mark operation as saved
4. receive response from step 2 and if operation is not marked as saved make a call to util.do_some_action.

If step 4 is completed ahead of step 3, then there would be 2 calls to util.do_some_action.
Application can handle it, but I noticed that sometimes both log entries in log_messages table have same create_dt.
This does not make sense as there was an obvious delay between calls.

question

Our application save logs into log_messages table and expectation is that create_dt field represent a time when we called util.save_log
But sometimes value for create_dt might be same for sequential calls with some delay between them.

I have a guess that COMMIT WRITE BATCH NOWAIT will buffer (batch!) transaction and assign value to create_dt at some later time.
Is this correct?







with LiveSQL Test Case:

and Connor said...

No, the CREATE_DT will be assigned at the time of the call (eg insert) not at the time of commit.

However, that doesn't mean you would *see* the entries at the same time if either had their commit delayed.

eg

SQL> create table t ( x date );

Table created.

SQL>
SQL> begin
  2    insert into t values (sysdate);
  3    commit;
  4    insert into t values (sysdate);
  5  end;
  6  /

PL/SQL procedure successfully completed.

--
-- From another session
--

SQL> select * from t;

X
-------------------
07/04/2017 13:32:20


So I dont see that second row until "later"...When session 1 commits, then I see this:

SQL> select * from t;

X
-------------------
07/04/2017 13:32:20
07/04/2017 13:32:20



so the date was applied at the same time, but the "appearance" of the row appears delayed (because it was uncommitted).

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

I have an answer, but I am not convinced by example

Andrey, April 07, 2017 - 4:02 pm UTC

Thank you for respond!

I'll try to merge your response with my question (I need default value and batch commit):

create table t(n number, d timestamp default systimestamp);

begin
  -- I do not call systimestamp directly:
  insert into t (n) values (1);
  commit write batch nowait;

  null; -- some action for 10-500 msec

  insert into t (n) values (2);
  commit write batch nowait;
end;

What I see as a result:
select count(*) from t;

n   d
--  -----------------------
1   2017-04-07 11:48:32.546
2   2017-04-07 11:48:32.546

select count(*) cnt from t
group by d;

CNT
---
2


I see that sometimes (1-10 cases for 10^7 operations) these two timestamps are the same. You are saying that my guess is wrong. But why timestamps match up to last millisecond?

Connor McDonald
April 10, 2017 - 3:43 am UTC

Sorry, I can't reproduce - my tests all come back with "consistent" timestamps, namely, if operations finish within one ms, then the timestamps align, otherwise they do not.

SQL> create table t(seq int, n number, d timestamp default systimestamp);

Table created.

SQL>
SQL> set serverout on
SQL> declare
  2    looper number := 10;
  3    f int;
  4    ts1 timestamp;
  5    ts2 timestamp;
  6  begin
  7  for x in 1 .. 10 loop
  8    insert into t (seq,n) values (x,1);
  9    ts1 := systimestamp;
 10    commit write batch nowait;
 11
 12    -- burn some time
 13    for i in 1 .. looper loop
 14       f := i;
 15    end loop;
 16
 17    insert into t (seq,n) values (x,2);
 18    commit write batch nowait;
 19    ts2 := systimestamp;
 20    looper := looper * 6;
 21
 22    dbms_output.put_line(x||' '||ts1||' '||ts2);
 23
 24  end loop;
 25  end;
 26  /
1 10-APR-17 11.39.18.144000 AM 10-APR-17 11.39.18.144000 AM
2 10-APR-17 11.39.18.144000 AM 10-APR-17 11.39.18.144000 AM
3 10-APR-17 11.39.18.144000 AM 10-APR-17 11.39.18.144000 AM
4 10-APR-17 11.39.18.144000 AM 10-APR-17 11.39.18.144000 AM
5 10-APR-17 11.39.18.144000 AM 10-APR-17 11.39.18.144000 AM
6 10-APR-17 11.39.18.144000 AM 10-APR-17 11.39.18.160000 AM
7 10-APR-17 11.39.18.160000 AM 10-APR-17 11.39.18.175000 AM
8 10-APR-17 11.39.18.175000 AM 10-APR-17 11.39.18.408000 AM
9 10-APR-17 11.39.18.408000 AM 10-APR-17 11.39.19.886000 AM
10 10-APR-17 11.39.19.886000 AM 10-APR-17 11.39.28.857000 AM

PL/SQL procedure successfully completed.

SQL>
SQL> select t1.seq, t1.d, t2.d
  2  from t t1, t t2
  3  where t1.seq = t2.seq
  4  and t1.n = 1 and t2.n = 2
  5  order by 1;

       SEQ D                                                                           D
---------- --------------------------------------------------------------------------- --------------------------------
         1 10-APR-17 11.39.18.144000 AM                                                10-APR-17 11.39.18.144000 AM
         2 10-APR-17 11.39.18.144000 AM                                                10-APR-17 11.39.18.144000 AM
         3 10-APR-17 11.39.18.144000 AM                                                10-APR-17 11.39.18.144000 AM
         4 10-APR-17 11.39.18.144000 AM                                                10-APR-17 11.39.18.144000 AM
         5 10-APR-17 11.39.18.144000 AM                                                10-APR-17 11.39.18.144000 AM
         6 10-APR-17 11.39.18.144000 AM                                                10-APR-17 11.39.18.160000 AM
         7 10-APR-17 11.39.18.160000 AM                                                10-APR-17 11.39.18.175000 AM
         8 10-APR-17 11.39.18.175000 AM                                                10-APR-17 11.39.18.408000 AM
         9 10-APR-17 11.39.18.408000 AM                                                10-APR-17 11.39.19.886000 AM
        10 10-APR-17 11.39.19.886000 AM                                                10-APR-17 11.39.28.857000 AM

10 rows selected.


Can you give us an example ?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library