Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I think his argument is that since the two transactions (as a whole)
> should be serialized, he shouldn't get the same max(node_order) in both
> since in either order of serialization of the two transactions you can't
> get 5 from both selects (one should return 6).
Right, that's a fair point.
> The problem with this is that it's probably pretty unimplementable,
Yeah. This shows the difference between what we do and true
serialization. An academic would tell you that true serialization
requires predicate locking --- that is, as soon as transaction T1 has
done a "SELECT ... WHERE foo", then concurrent transaction T2 must wait
for T1 if it tries to insert *or remove* a row that would satisfy foo.
Unfortunately this cure is much worse than the disease. Aside from the
sheer overhead of enforcing it, it leads to deadlocks. For example,
T1 T2
SELECT max(node_order)+1...
SELECT max(node_order)+1...
INSERT ...
at this point T1's insert blocks, because it would violate the predicate
of T2's already-performed SELECT.
INSERT...
And now T2 is blocked by T1 --- deadlock. So now you know why predicate
locking is an academic solution and not used by real databases :-(
But probably the shortest answer to Ryan is that what the database sees
is a unique-index violation, so that's what it reports. Deducing that
this condition can only have arisen because of concurrent behavior would
take an impractical amount of knowledge, both of what your own
transaction is doing and of what other transactions are doing.
regards, tom lane
