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-
