I have a program that does this:
BEGIN;
SELECT mystring FROM mytable WHERE x = 3 AND y = 4 FOR UPDATE;
<my program takes mystring and appends to it>
UPDATE mytable SET mystring = '<appended string here>' WHERE x = 3 AND y
= 4;
END;
But the locking isn't working properly. I do something that should
cause 3 different threads to try and do that append, and the first one
goes through properly, but the second two append to the result of the
first on only, meaning that the third one didn't see the result of the
second's append.
I tried to make a simple example that reproduced this, but failed.
I'm guessing that the value of that the select is generated before the
row is locked, and thus each of my last two threads saw the table after
the first append, *then* one of them blocked because the other had
locked the table. It's the only thing I can figure.