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 snapsh
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
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] 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
>
>-Original Message-
>From: [EMAIL PROTECTED]
>[mailto:[EMAIL PROTECTED] 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 erro
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 s
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 no
"Albe Laurenz" <[EMAIL PROTECTED]> 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.
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 ta
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
> 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 su
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 er
11 matches
Mail list logo
