On Mon, 2009-01-26 at 11:34 -0600, Kevin Grittner wrote:
> READ COMMITTED is not supposed to be able to view the work of a
> concurrent transactions as PARTLY applied and PARTLY committed, which
> is what's happening here. If one statement in a READ COMMITTED
> transaction sees the uncommitted view of the data and the next
> statement sees the committed view, that's compliant. It may not
> surprise someone who is intimately familiar with PostgreSQL internals
> for a single SELECT statement to see PART of a transactions work, but
> it would surprise most users, and is certainly not compliant with the
> standard.
See 13.2.1:
http://www.postgresql.org/docs/8.3/static/transaction-iso.html
That explanation seems to be the behavior I would expect from UPDATE in
read committed mode. Perhaps I'm just used to PostgreSQL -- what do
other database systems do?
And what does the standard say? I can't find anything in the standard
that handles UPDATEs differently, so that seems to support your
position.
After the concurrent transaction commits, you basically have three
options:1. Get a new snapshot, and re-run the entire query to find new rows
that might match the search condition that were committed between the
time you took the original snapshot for UPDATE and the time that the
concurrent transaction committed.2. Blindly proceed with the original snapshot. This would mean that two
concurrent updates like "set i = i+1" might both see the same value,
let's say 5, and both update it to 6, and then commit. However, a serial
execution would produce 7.3. Find the latest version of the same tuples you found from the
original snapshot in the original search, and if they still match the
search condition, update based on the new version. This is what
PostgreSQL currently does.
I don't think this is PostgreSQL-specific, I think non-MVCC database
systems face this same choice (although the terminology would be
different).
#3 has a certain intuition about it (i.e. "produces the expected result
most of the time in simple cases"), but in my opinion, that intuition
only holds for UPDATE, it doesn't hold for SELECT.
Regards,Jeff Davis
