Can I use row-level locks to sequence READ COMMITTED transactions?

From: Matthew Woodcraft <mattheww(at)chiark(dot)greenend(dot)org(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Can I use row-level locks to sequence READ COMMITTED transactions?
Date: 2002-07-21 14:05:14
Message-ID: 20020721140514.GA8353@golux.invalid
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

If I use a READ COMMITTED transaction, the documentation says that a
query in my transaction may see changes which were committed by other
transactions after my transaction started.

My question is, is it guaranteed that a SELECT in my transaction will
see changes previously committed by other transactions, or is it only a
possibility?

By 'previously committed', I mean 'committed by a transaction which held
a row-level lock which my transaction has since obtained'.

For example, if I run the following transaction many times
simultaneously,

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT 1 FROM walls WHERE wall_id = 3 FOR UPDATE;

INSERT INTO bottles (
wall_id,
number_seen)
VALUES (
3,
(SELECT COUNT(*) FROM bottles WHERE wall = 3)
);
COMMIT;

can I end up with two rows in the 'bottles' table with the same value
for 'seen'? Or do I have a guarantee that after one instance of the
transaction has released the 'SELECT FOR UPDATE' lock, any other
instance will see the data that it has committed?

-M-

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ralph Graulich 2002-07-21 14:10:30 Re: max relations in a single database
Previous Message Tom Lane 2002-07-21 13:56:46 Re: max relations in a single database