Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table Truncate and Locks

Table Truncate and Locks

From
"Chris Hoover"
Date:
What sort of lock does truncate require?  If it is not an access exclusive lock, what locks would block the truncate?

Thanks,

Chris

SELECT FOR UPDATE NOWAIT

From
"Mathias Laurent"
Date:
If I do :
Session 1:
decibel=# begin;
BEGIN
decibel=# select * from i where i=1 for update nowait;
i
---
1
(1 row)

decibel=# begin;
BEGIN
decibel=# select * from i where i=2 for update nowait;
i
---
2
(1 row)

Session 2 :

decibel=# select * from i where i=1 for update nowait;
ERROR:  could not obtain lock on row in relation "i"
decibel=# select * from i where i=3 for update nowait;
ERROR:  current transaction is aborted, commands ignored until end of
transaction block
decibel=#  ==> Why ? :'(

Why i can't do any "Select for update" (current transaction aborted) after
having receive a not obtain lock ???
Thank you for your answer !

_________________________________________________________________
Windows Live Messenger sur i-mode™ : dialoguez avec vos amis depuis votre
mobile comme sur PC ! http://mobile.live.fr/messenger/bouygues/

Re: SELECT FOR UPDATE NOWAIT

From
"Jim C. Nasby"
Date:
On Thu, Sep 28, 2006 at 03:26:36PM +0000, Mathias Laurent wrote:
> If I do :
> Session 1:
> decibel=# begin;

Does decibel have some meaning in some language other than english?

> BEGIN
> decibel=# select * from i where i=1 for update nowait;
> i
> ---
> 1
> (1 row)
>
> decibel=# begin;
> BEGIN
> decibel=# select * from i where i=2 for update nowait;
> i
> ---
> 2
> (1 row)
>
> Session 2 :
>
> decibel=# select * from i where i=1 for update nowait;
> ERROR:  could not obtain lock on row in relation "i"
> decibel=# select * from i where i=3 for update nowait;
> ERROR:  current transaction is aborted, commands ignored until end of
> transaction block
> decibel=#  ==> Why ? :'(
>
> Why i can't do any "Select for update" (current transaction aborted) after
> having receive a not obtain lock ???
> Thank you for your answer !

Because as soon as there is an error in a transaction, the entire
transaction is void. You have to rollback the transaction (or return to
a savepoint).
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)