SEO

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?






---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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
>
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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).
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailtogsql-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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailtogsql-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]


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

> 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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly