Thread: Lock table, Select for update and Serialization error
Hi, As per postgres docs, 'Select for update' is used to obtain row level locks where as 'lock table' is used to obtain table level locks. Under serializable isolation level, select for update gives error if rows selected have been modified concurrently. but 'lock table' does not give such error even though some of the rows in the table are modified by concurrent transaction. Is this the expected behavior?
> As per postgres docs, 'Select for update' is used to obtain row level > locks where as 'lock table' is used to obtain table level locks. > > Under serializable isolation level, select for update gives error if > rows selected have been modified concurrently. > but 'lock table' does not give such error even though some of the rows > in the table are modified by concurrent transaction. > > Is this the expected behavior? LOCK TABLE should never give you an error, except for a deadlock resolution error. LOCK TABLE will just wait until there is no lock on the table that is incompatible with the requested lock, then it will obtain the lock and return. LOCK TABLE does not modify tables or rows and so you cannot get a serialization error, which is only issued when you run serializable and try to modify a row that is newer than your transaction begin time. On the other hand, LOCK TABLE will not necessarily prevent you from subsequently receiving serialization errors if you do not request an exclusive lock on the table. Does that answer your questions? Yours, Laurenz Albe
Thanks Laurenz for quick reply. If this is the expected behavior then isn't 'Lock table' is just extra performance penalty and achieves nothing under serializable isolation level. The serializable isolation level in postgres is infact snapshot isolation. Suppose a transaction T is using 'lock table' on table A and then querying it. Here T will be blocked untill all conflicting locks on A are released. When there are no conflicting locks on A, T will go ahead and read data from the snapshot taken at the T's start. So, in short 'Lock Table' just delayed query of transaction T. > LOCK TABLE should never give you an error, except for a deadlock > resolution > error. > > LOCK TABLE will just wait until there is no lock on the table that is > incompatible with the requested lock, then it will obtain the lock and > return. > > LOCK TABLE does not modify tables or rows and so you cannot get a > serialization error, which is only issued when you run serializable > and try to modify a row that is newer than your transaction begin time. > > On the other hand, LOCK TABLE will not necessarily prevent you from > subsequently receiving serialization errors if you do not request > an exclusive lock on the table. > > Does that answer your questions? > > Yours, > Laurenz Albe > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > >
sudhir wrote: >> LOCK TABLE should never give you an error, except for a deadlock >> resolution error. >> >> LOCK TABLE will just wait until there is no lock on the table that is >> incompatible with the requested lock, then it will obtain the lock and >> return. >> >> LOCK TABLE does not modify tables or rows and so you cannot get a >> serialization error, which is only issued when you run serializable >> and try to modify a row that is newer than your transaction >> begin time. >> >> On the other hand, LOCK TABLE will not necessarily prevent you from >> subsequently receiving serialization errors if you do not request >> an exclusive lock on the table. > > If this is the expected behavior then isn't 'Lock table' is just extra > performance penalty and achieves nothing under serializable > isolation level. > > The serializable isolation level in postgres is infact snapshot isolation. > Suppose a transaction T is using 'lock table' on table A and then > querying it. > Here T will be blocked untill all conflicting locks on A are released. > When there are no conflicting locks on A, T will go ahead and read data > from the snapshot taken at the T's start. > > So, in short 'Lock Table' just delayed query of transaction T. I think that you still do not understand it completely. Consider these two cases: Case a) Session 1 starts a serializable transaction T. The first statement in transaction T will mark the time at which the 'snapshot' that you mention above is 'taken'. Let's call this time t1. At a time t2 > t1, Session 2 updates a row on table r. At t3 > t2, Session 1 tries to update the same row in table r. Session 1 will fail with a serialization error. Case b) Session 1 starts a serializable transaction T. The first statement in transaction T is 'LOCK TABLE r'. The statement returns at time t1 which is the 'snapshot' time for transaction T. At time t2 > t1, Session 2 tries to modify a row in table r. Session 2 will have to wait until transaction T is completed, because it cannot get a shared lock on the table. At any time > t1, Session 1 can update the same row in table r without receiving an error. You see, there is a difference. In case a) the serializable transaction will very likely fail if there are many concurrent changes on the table. In case b), the serializable transaction will always succeed, while all concurrent updates must wait. Does that make sense to you? Yours, Laurenz Albe
"Albe Laurenz" <all@adv.magwien.gv.at> writes:
> You see, there is a difference. In case a) the serializable transaction
> will very likely fail if there are many concurrent changes on the table.
> In case b), the serializable transaction will always succeed, while
> all concurrent updates must wait.
The critical point here is that LOCK TABLE commands at the start of a
serializable transaction are performed *before* taking the transaction's
snapshot (the snap happens at the first regular DML command). They not
only protect against post-snap changes as shown by Albe's example, but
against uncommitted changes that were made before transaction start
(by making the serializable xact wait until those changes are committed
or aborted before it takes its snap).
regards, tom lane
OK. In your example lock table command is used to avoid rollbacks due to concurrent transaction. So LOCK TABLE is useful in this situation. I have one last doubt: why there is difference between behavior of 'select for update' and 'lock table'. one causes serialization error and other does not. (even though both are variations of locking mechanism) case 1) T1# BEGIN -- snapshot taken T1# Set transaction isolation level serializable; T2# BEGIN -- snapshot taken T2# Set transaction isolation level serializable; T1# Update account set bal=bal-100 where accno=129; T2# lock table account; -- *blocked* T1# commit; T2# -- lock obtained case 2) T1# BEGIN -- snapshot taken T1# Set transaction isolation level serializable; T2# BEGIN -- snapshot taken T2# Set transaction isolation level serializable; T1# Update account set bal=bal-100 where accno=129; T2# select * from account where accno=129 for update; -- *blocked* T1# commit; T2# -- serialization error > Consider these two cases: > > Case a) > > Session 1 starts a serializable transaction T. > The first statement in transaction T will mark the time at which > the 'snapshot' that you mention above is 'taken'. Let's call this > time t1. > > At a time t2 > t1, Session 2 updates a row on table r. > > At t3 > t2, Session 1 tries to update the same row in table r. > Session 1 will fail with a serialization error. > > Case b) > > Session 1 starts a serializable transaction T. > The first statement in transaction T is 'LOCK TABLE r'. The statement > returns at time t1 which is the 'snapshot' time for transaction T. > > At time t2 > t1, Session 2 tries to modify a row in table r. > Session 2 will have to wait until transaction T is completed, because > it cannot get a shared lock on the table. > > At any time > t1, Session 1 can update the same row in table r > without receiving an error. > > > You see, there is a difference. In case a) the serializable transaction > will very likely fail if there are many concurrent changes on the table. > In case b), the serializable transaction will always succeed, while > all concurrent updates must wait. >
It is not necessary that LOCK TABLE will be the first statement. (assuming serializable isolation level is snapshot isolation in postgres) For serializable transaction, snapshot should be taken when the 'BEGIN' statement is executed, and not when LOCK TABLE succeeds. Hence, uncommitted changes should be invisible to serializable transaction. > The critical point here is that LOCK TABLE commands at the start of a > serializable transaction are performed *before* taking the transaction's > snapshot (the snap happens at the first regular DML command). They not > only protect against post-snap changes as shown by Albe's example, but > against uncommitted changes that were made before transaction start > (by making the serializable xact wait until those changes are committed > or aborted before it takes its snap). > >
>-----Original Message----- >From: pgsql-general-owner@postgresql.org >[mailto:pgsql-general-owner@postgresql.org] On Behalf Of sudhir >Sent: dinsdag 22 mei 2007 19:28 >To: Tom Lane >Cc: Albe Laurenz; pgsql-general@postgresql.org >Subject: Re: [GENERAL] Lock table, Select for update and >Serialization error [note: text reordered] >> The critical point here is that LOCK TABLE commands at the >start of a >> serializable transaction are performed *before* taking the >> transaction's snapshot (the snap happens at the first regular DML >> command). They not only protect against post-snap changes >as shown by >> Albe's example, but against uncommitted changes that were >made before >> transaction start (by making the serializable xact wait until those >> changes are committed or aborted before it takes its snap). [end reorder] >It is not necessary that LOCK TABLE will be the first statement. >(assuming serializable isolation level is snapshot isolation >in postgres) For serializable transaction, snapshot should be >taken when the 'BEGIN' >statement is executed, and not when LOCK TABLE succeeds. Tom is correct, the snapshot is taken at the first DML statement, NOT at transaction start (the "begin" statement). Test it yourself. Your 'should' be might be the expected behaviour, but its not the implemented behaviour. As Tom is point out, the LOCK TABLE as the first statement is to prevent serializable errors from happening. >Hence, uncommitted changes should be invisible to serializable >transaction. Uncommited changes are at all times only and only visible to the transaction that made those changes. No other transactions, of any isolation level, can see uncommited changes from other transactions. Remember, postgres uses the MVCC model. - Joris Dobbelsteen
>-----Original Message----- >From: pgsql-general-owner@postgresql.org >[mailto:pgsql-general-owner@postgresql.org] On Behalf Of sudhir >Sent: dinsdag 22 mei 2007 19:21 >To: Albe Laurenz >Cc: pgsql-general@postgresql.org >Subject: Re: [GENERAL] Lock table, Select for update and >Serialization error > >OK. In your example lock table command is used to avoid >rollbacks due to concurrent transaction. >So LOCK TABLE is useful in this situation. > >I have one last doubt: >why there is difference between behavior of 'select for >update' and 'lock table'. >one causes serialization error and other does not. >(even though both are variations of locking mechanism) The locking level is at a very different level and you have to see the implications of the diffent ways: The LOCK statement is to prevent other transactions from accessing the table. This is a high-level lock with very low overhead to take. The disadvantage is obviously the performance impact is has, as it is highly likely to block other transactions. The mechanism is very useful to get some guarentees about what will happen with the data in the table. This allows for synchronizing modification between different transactions. The select for update has two uses: 1) Ensure the data is current and remains so, for a small subset of a table. 2) Prevent deadlocks caused by lock escallation. What I didn't put explicitly is that select for update is to indicate that a tuple will be updated. For serializable it implies that the current version you see should be current. Obviously there is a common need for something with the concurrency benefit of "select for update", but with relaxed requirements. The postgres developers envisioned this and for this purpose use "select for share". The select for share only does: 1) Ensure the data is current and remains so, for a small subset of the table. Summarizing: * Lock table - High-level: executes fast, but concurrency problems. Guarentees about future changes. Select for update - Low-level, concurrent, ensures data validity and indicates its modified shortly. Select for share - Low-level, concurrent, ensures data validity. Hopefully this clears it up a bit. - Joris Dobbelsteen [snip]
> Summarizing: > * Lock table - High-level: executes fast, but concurrency problems. > Guarentees about future changes. > Select for update - Low-level, concurrent, ensures data validity and > indicates its modified shortly. > Select for share - Low-level, concurrent, ensures data validity. > > Hopefully this clears it up a bit. > Yeah, it does. Thanks for your help guys.
One last comment: sudhir wrote: > T1# BEGIN -- snapshot taken > T1# Set transaction isolation level serializable; > T2# BEGIN -- snapshot taken > T2# Set transaction isolation level serializable; > T1# Update account set bal=bal-100 where accno=129; The 'snapshot taken' are misplaced. The snapshots are actually taken when the first 'real' statement in the transaction is executed. In the case of T1, this would be at Update account set bal=bal-100 where accno=129; This is, as Tom Lane has pointed out, the reason why locking the table as first statement in a transaction will prevent serialization errors. Tom said, 'the first DML statement', but as far as I know a SELECT statement will also cause the snapshot to be taken. Yours, Laurenz Albe