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