0
$\begingroup$

In the 2004 paper "A Read-Only Transaction Anomaly Under Snapshot Isolation" by Alan Fekete et al. (which can be found here and here) the authors state:

We also note that any execution of T1 and T2 (with arbitrary parameter values) without T3 present will always act serializably.

I have tested this claim inside PostgreSQL, like this

create table variables (
    name char(1) not null,
    value integer not null
);
insert into variables values ('x', 0), ('y', 0);

T2: begin
T2: set transaction isolation level serializable;
T2: select * from variables where name = 'x'
T2: select * from variables where name = 'y'
T1: begin
T1: set transaction isolation level serializable;
T1: select * from variables where name = 'y'
T1: update variables set value = 20 where name = 'y'
T1: commit
T2: update variables set value = -11 where name = 'x'
Connection reset. Reconnect (Y/n):

but as shown, when executing the T2's update, this transaction is aborted (I know this because the client, in the case pgcli, disconnected), which means that the schedule was not serializable.

What is happening here? Is the authors claim wrong? Is it a bug inside PostgreSQL? Or most probably, I made some mistake I am not aware of?

Thanks

Improve this question
$\endgroup$
5
  • $\begingroup$ First, snapshot isolation does not mean serializable. There is a variant of snapshot isolation which is serializable, Serializable Snapshot Isolation (SSI), and is the default implementation of SERIALIZABLE transactions for Postgres since 9.1. This was introduced in 2005 by Fekete et al. Many databases don't use SSI and behave differently. MSSQL simply blocks one of the transactions until the other completes. Second, you seem to be under a misapprehension that databases are obligated to allow any serializable execution, that they can only abort an error-free run if it is not serializable.(1/2) $\endgroup$
    – Derek Elkins left SE
    Jun 9, 2018 at 19:12
  • 1
    $\begingroup$ Databases are free to abort any transaction whenever they feel like it. That a database aborts a transaction tells you nothing about whether it was part of a non-serializable execution. That a database disallows a certain execution doesn't mean it is not serializable. VoltDB, for example, literally executes transactions sequentially. This means there are many serializable executions that VoltDB disallows. VoltDB also deeply leverages the freedom to abort transactions by aborting any transaction whose data is not already in memory. (2/2) $\endgroup$
    – Derek Elkins left SE
    Jun 9, 2018 at 19:12
  • 1
    $\begingroup$ @DerekElkins You should respond with an answer if you think you have an answer, and comment only if you want to clarify or not something, but do not have an answer. If you think you gave an answer, please put in that format so I can accept. $\endgroup$
    – mljrg
    Jun 9, 2018 at 19:28
  • $\begingroup$ @DerekElkins Yep, I though that databases were obligated to accept any serializable execution. So this poses another question, why do they reject such executions, but that's another topic. (1/2) $\endgroup$
    – mljrg
    Jun 9, 2018 at 19:40
  • $\begingroup$ @DerekElkins "That a database disallows a certain execution doesn't mean it is not serializable." So can you tell me why the previous execution is serializable? I see there a dependency that T2 must be serialized before T1 (because T2 reads y before T1 writes y), but then since T1 commits first then T1 must be serialized before T2, hence T2 must abort because both are not serializable. That's my doubt, and why I questioned. What am I ignoring here? Is it possible for a transaction to be committed BEFORE another that was committed previously? (2/2) $\endgroup$
    – mljrg
    Jun 9, 2018 at 19:40

1 Answer 1

Reset to default
0
$\begingroup$

The claim in the paper is true (I never doubt about it, but just wanted to know why) because only the dependencies between the reads and writes of concurrent transactions determine if these are serializable, but not their commits (nor their aborts, obviously).

In the example above, extracted from the paper's claim, the fact that PostgreSQL aborts T2 has nothing to do with serializability between T1 and T2, but only with some design decision in PostgreSQL that I ignore (if someone knows I, please write a comment to this answer).

Improve this answer
$\endgroup$
1
  • $\begingroup$ @DerekElkins Can you tell us why does PostgreSQL aborts T2 above? Thanks. $\endgroup$
    – mljrg
    Jun 10, 2018 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.