Re: lock mode in DELETE

merino silva wrote:
> Hi all,
>
> I'm really bothered about the locking mode of the
> DELETE command on the Postgresql 7.3.4. It is blocking
> the table even from reading data.
>
> Is that a feature or a bug ?
>
> thanks,
> Merino
>
> __________________________________
> Do you Yahoo!?
> Get better spam protection with Yahoo! Mail.
> http://antispam.yahoo.com/tools
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

DELETE uses a so called ROW EXCLUSIVE LOCK.

This kind of locks conflicts with the SHARE, SHARE ROW EXCLUSIVE,
EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

The commands UPDATE, DELETE, and INSERT acquire this lock mode on the
target table (in addition to ACCESS SHARE locks on any other referenced
tables). In general, this lock mode will be acquired by any command that
modifies the data in a table.

More information can be found here:

http://www.postgresql.org/docs/current/static/explicit-locking.html

The PostgreSQL transaction system works really well so you need not
worry about bugs ;).

ROW EXCLUSIVE does not conflict with ACCESS SHARE which is used for reading.
In other words: A transaction can read data while it is deleted (=
declared invalid so that no other transaction can see it).
Why? Because you don't know if the data is REALLY obsolete until you commit.

Cheers,

Hans

--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at