RE: About multiple access to a table

Two simultaneous UPDATEs on the same row will cause the second
transaction to block until the first transaction either commits or
aborts when the TRANSACTION ISOLATION LEVEL is READ COMMITTED. The
second transaction will then perform the update. Two simultaneous
UPDATEs on the same row will cause the second transaction to block
until the first transaction either commits or aborts when the
TRANSACTION ISOLATION LEVEL is SERIALIZABLE as well, *except* that if
the first transaction COMMITs, the second transaction will ABORT; if
the first transaction ABORTs (ROLLBACK), the second transaction will
COMMIT.

You can achieve similar automatic locking of rows for SELECTs by
using SELECT ... FOR UPDATE:

See:

http://www.postgresql.org/idocs/index.php?mvcc.html

for details. With regard to INSERTs, the second transaction will not
see the inserted record until the first transaction commits in READ
COMMITTED, but that doesn't stop phantom and non-repeatable reads. To
prevent the second transaction from seeing INSERTs performed by the
first transaction, regardless of whether or not the first transaction
COMMITs, you must use SERIALIZABLE transaction isolation level. Of
course, you must be using BEGIN/END around these DML statements.

Hope that helps,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Jose Manuel Lorenzo Lopez
[SMTP:jose-manuel.lorenzo-lopez@conti.de]
Sent:    Friday, June 01, 2001 7:14 AM
To:    pgsql-general@postgresql.org
Subject:    [GENERAL] About multiple access to a table

Hello community,

I have a doubt about multiple access to a table in my database.
When two people are accessing the same table for insert or update,
is the affected row locked or am I supposed to lock this row explicit
in my application???

Any help will by welcome! :)

Best Regards / Un saludo / Mit freundlichen Gru?en / Cordiali saluti

Jose Manuel Lorenzo Lopez