On 01/21/2011 03:25 AM, Florian Pflug wrote:
> The COMMIT order in the actual, concurrent, schedule doesn't not necessarily
> represent the order of the transaction in an equivalent serial schedule. Here's
> an example
>
> T1: BEGIN SERIALIZABLE; -- (Assume snapshot is set here)
> T1: UPDATE D1 ... ;
> T2: BEGIN SERIALIZABLE; -- (Assume snapshot is set here)
> T2: SELECT * FROM D1 ... ;
> T2: UPDATE D2 ... ;
> T1: COMMIT;
> T3: SELECT * FROM D1, D2;
> T2: COMMIT;
>
> Now, the COMMIT order is T1, T3, T2. Lets check if there is a equivalent
> serial schedule. In any such schedule
>
> T2 must run before T1 because T2 didn't see T1's changes to D1
> T3 must run after T1 because T3 did see T1's changes to D1
> T3 must run before T2 because T3 didn't see T2's changes to D2
>
> This is obviously impossible - if T3 runs before T2 and T2 runs before T1
> then T3 runs before T1, contradicting the second requirement. There is thus
> no equivalent serial schedule and we must abort of these transactions with
> a serialization error.
>
> Note that aborting T3 is sufficient, even though T3 is READ ONLY!. With T3 gone,
> an equivalent serial schedule is T2,T1!
Sorry for bothering all of you, but I just don't get this. What if T2
rolls back instead of committing? Then the snapshot of T3 would have
been valid, right? Now, for the snapshot of T3 it doesn't matter if T2
commits or if it doesn't, because it can't see the changes of T2 in any
case. Thus, it would seem that the snapshot is valid. On the other hand
I can't see anything wrong in the logic in your post. What am I missing?
I am feeling stupid...
At least for dumps I don't see how T2 can matter (assuming T3 is the
pg_dump's snapshot). Because if you reload from the dump, T2 never
happened in that dump. In the reloaded database it just did not exist at
all.
- Anssi