Thread: How long does commit take?

I would like to know how long it will take until the effects of a
transaction is visible from outside after a commit statement. Or in
other words: I have the situation that i have to use data which is
inserted within a transaction immediately after the transactions is
committed, but sometimes the new data is not available a this moment (A
few moments later it is available). Is there any method to check if data
is ready to be used before 'selecting' it?
--
R.Hnat

Reinhard Hnat <hnat@logotronic.co.at> writes:
> I would like to know how long it will take until the effects of a
> transaction is visible from outside after a commit statement.

There is no delay.

> Or in
> other words: I have the situation that i have to use data which is
> inserted within a transaction immediately after the transactions is
> committed, but sometimes the new data is not available a this moment (A
> few moments later it is available). Is there any method to check if data
> is ready to be used before 'selecting' it?

I think you may be confused by the MVCC mechanism.  A SQL statement will
absolutely see everything that is committed as of the instant of the
"snapshot" it's using.  I think what you are describing is probably the
effect of having taken a snapshot just before the commit occurred.  See
http://www.postgresql.org/docs/8.2/static/mvcc.html

            regards, tom lane

On Thu, April 26, 2007 10:52 am, Tom Lane said:
> Reinhard Hnat <hnat@logotronic.co.at> writes:
>> I would like to know how long it will take until the effects of a
>> transaction is visible from outside after a commit statement.
>
> There is no delay.

You are telling me the database is always able to update all it's internal
records instantaneously?  I doubt that.  I'm sure there is a delay, the
length of which is dependent on the machine, table layout, other load on
the machine, etc.  (Basically, the commit will take a measurable amount of
time to execute.)  I'd expect it to be a few milliseconds in most cases,
but no more.

If you are running in to this, you probably should write in a sleep call
for a quarter of a second or so. (Well more than I would expect the commit
to take on most machines.)  If that amount of time is unworkable, than you
either need to have a custom solution of some sort built for your needs
(you are beyond what a database is designed to be able to handle) or you
should re-write your code so that the query is part of the same
transaction.

If the problem is that everything must always process in a certain order,
despite being from multiple workstations, I suggest you look into the
different locking modes available.  You can prevent access to that data
until the commit is completed.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

>> There is no delay.

> You are telling me the database is always able to update all it's internal
> records instantaneously?  I doubt that.  I'm sure there is a delay, the
> length of which is dependent on the machine, table layout, other load on
> the machine, etc.  (Basically, the commit will take a measurable amount of
> time to execute.)  I'd expect it to be a few milliseconds in most cases,
> but no more.

I'd believe that those few milliseconds occur before the database returns to
the client that the COMMIT completed successfully. It is available
immediately as soon at the COMMIT command is COMPLETED to the client, not as
soon as the COMMIT is STARTED.


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments

"Phillip Smith" <phillip.smith@weatherbeeta.com.au> writes:
> I'd believe that those few milliseconds occur before the database returns to
> the client that the COMMIT completed successfully. It is available
> immediately as soon at the COMMIT command is COMPLETED to the client, not as
> soon as the COMMIT is STARTED.

Actually, it's available at the instant the XID is removed from the
ProcArray and therefore stops appearing to be IN PROGRESS to other
transactions (not sooner, not later --- we have expended no little
effort to make sure that transaction commit appears atomic to onlooking
processes).  The report to the client that the commit is done will
happen some little while after that; there's some post-commit cleanup
involved, plus message transmission.

            regards, tom lane