On Jan6, 2014, at 20:41 , AK <alk...@gmail.com> wrote: > If two transactions both read and write, I can easily reproduce the > following: "could not serialize access due to read/write dependencies among > transactions". However, the 9.3 documentation says that "When relying on > Serializable transactions to prevent anomalies, it is important that any > data read from a permanent user table not be considered valid until the > transaction which read it has successfully committed. This is true even for > read-only transactions". > > I cannot have a read-only transaction fail because of serialization > anomalies. Can someone show me a working example please?
A read-only transaction will abort due to a serialization failure if observes a state of the database which doesn't exist in any serial transaction schedule. Here's an example (default isolation level is assumed to be serializable, of course) W1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; W1: UPDATE t SET count=count+1 WHERE id=1; -- (*2) W1: SELECT data FROM t WHERE id=2; -- (*1) W2: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; W2: UPDATE t SET count=count+1 WHERE id=2; -- (*1, *2) W2: COMMIT; R : START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY; R : SELECT data FROM t WHERE id IN (1,2); -- (*2) W1: COMMIT; -- R will now report a serialization error! Due to (*1), W1 must execute before W2 in any serial schedule, since W1 reads record 2 which is later modified by W2. Due to (*2), R must execute after W2 but before W1 since it reads record 2 previously modified by W2 and record 1 later modified by W1. (Note that W1 hasn't committed at time R acquires its snapshot) The dependencies induced by (*1) or (*2) alone are satisfyable by a serial schedule, but both together aren't - if W1 must execute before W2 as required by (*1), then surely every transaction that runs after W2 in such a schedule also runs after W1, thus contradicting (*2). Now since (*1) alone isn't contradictory, committing W1 succeeds. That leaves only the last line, the COMMIT of R, to fail, which it does. The gist of this example is that whether the state observed by R exists in any serial transaction schedule or not is only certain after all concurrent read-write transactions (W1 and W2) have committed. You can avoid the error above by specifying DEFERRABLE in R's START TRANSACTION command. The session will then acquire a snapshot and wait for all possibly interfering read-write transactions to commit. If the snapshot turns out to be observable in some serial schedule, the session will continue, otherwise the database will acquire a new snapshot and wait again. Thus, once the START TRANSACTION with the DEFERRABLE flag has committed, you can be sure that the transaction won't later be aborted due to a serialization error. BTW, since this is a question about how to use postgres rather than how to extend it, it actually belongs on pgsql-general, not on the hackers list. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers