Обсуждение: How to reproduce serialization failure for a read only transaction.
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? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Jan6, 2014, at 20:41 , AK <alkuzo@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
Hi Florian,
can you explain why do you state that "default isolation level is assumed to
be
serializable, of course", when you explicitly specify isolation level for
every session - why should he default matter at all?
When I am trying to reproduce the scenario which you have posted, I am
observing different results. Here is my full scenario:
Session 1. Setting up:
CREATE TABLE cars( license_plate VARCHAR NOT NULL, reserved_by VARCHAR NULL
);
INSERT INTO cars(license_plate)
VALUES ('SUPRUSR'),('MIDLYPH');
Session 2: W1
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE cars SET reserved_by = 'Julia' WHERE license_plate = 'SUPRUSR' AND reserved_by IS NULL;
SELECT * FROM Cars
WHERE license_plate IN('SUPRUSR','MIDLYPH');
Session 3: W2
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE cars SET reserved_by = 'Ryan' WHERE license_plate = 'MIDLYPH' AND reserved_by IS NULL;
COMMIT;
Session 4: R
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY;
SELECT * FROM Cars
WHERE license_plate IN('SUPRUSR','MIDLYPH');
Session 2: W1
COMMIT;
ERROR: could not serialize access due to read/write dependencies among
transactions
What am I doing wrong?
Thank you for your help!
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569p5785597.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Jan6, 2014, at 23:28 , AK <alkuzo@gmail.com> wrote: > can you explain why do you state that "default isolation level is assumed to > be > serializable, of course", when you explicitly specify isolation level for > every session - why should he default matter at all? Sorry, that was a leftover - I initially wrote just START TRANSACTION with specifying an isolation level. > > When I am trying to reproduce the scenario which you have posted, I am > observing different results. Hm, yeah, I missed two things. First, dependency tracking can produce false positives, i.e. assume that dependencies exist between transactions which are actually independent. In my example, postgres fails to realize that W2 can be executed after W1, unless it uses an index scan for the UPDATE in W2. You can avoid that either by creating an index on the id column, and forcing W2 to use that by setting enable_seqscan to off, or by creating two tables t1 and t2 instead of one table t with two records (You'll have to modify the SELECT to scan both tables too). Second, since R executes it's SELECT before W1 commits, postgres is already aware that R poses a problem when W1 commits, and it chooses to cancel W1 instead of R. To avoid that, R needs to do the SELECT after W1 committed. Yet still force R to acquire a snapshot *before* that commit (without that, there's no serialization failure since R than simply executes after W1 and W2), you'll need to do e.g. SELECT 1 after R's START TRANSACTION command. I think the following should work (or, rather, fail) CREATE TABLE t (id INT PRIMARY KEY, count INT); INSERT INTO t (id, count) SELECT i, 0 FROM generate_series(1,2); W1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; W1: UPDATE t SET count=count+1 WHERE id=1; W1: SELECT count FROM t WHERE id=2; W2: SET enable_seqscan=off; W2: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; W2: UPDATE t SET count=count+1 WHERE id=2; W2: COMMIT; R : START TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY; R : SELECT 1; W1: COMMIT; R : SELECT data FROM t WHERE id IN (1,2); -- Should fail best regards, Florian Pflug
On 1/6/14, 5:27 PM, Florian Pflug wrote:> On Jan6, 2014, at 23:28 , AK <alkuzo@gmail.com> wrote:> First, dependency trackingcan produce false positives, i.e. assume that> dependencies exist between transactions which are actually independent.>In my example, postgres fails to realize that W2 can be executed after W1,> unless it uses an index scan forthe UPDATE in W2. You can avoid that either> by creating an index on the id column, and forcing W2 to use that by setting>enable_seqscan to off, or by creating two tables t1 and t2 instead of one> table t with two records (You'll haveto modify the SELECT to scan both tables> too).>> Second, since R executes it's SELECT before W1 commits, postgres isalready> aware that R poses a problem when W1 commits, and it chooses to cancel W1> instead of R. To avoid that, R needsto do the SELECT after W1 committed.> Yet still force R to acquire a snapshot *before* that commit (without that,> there'sno serialization failure since R than simply executes after W1 and> W2), you'll need to do e.g. SELECT 1 after R'sSTART TRANSACTION command.>> I think the following should work (or, rather, fail) This email and the previous one are an awesome bit of information, can we add it to the docs somehow? Even if it's just dumpingthe emails into a wiki page and referencing it? -- Jim C. Nasby, Data Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
This worked for me - thank you so much! The SELECT did fail. Also I cannot reproduce a scenario when "applications must not depend on results read during a transaction that later aborted;". In this example the SELECT itself has failed. Can you show an example where a SELECT completes, but the COMMIT blows up? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569p5785618.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Mon, Jan 06, 2014 at 05:14:12PM -0800, AK wrote: > Also I cannot reproduce a scenario when "applications must not depend on > results read during a transaction that later aborted;". In this example the > SELECT itself has failed. > Can you show an example where a SELECT completes, but the COMMIT blows up? Actually, no, not for a read-only transaction. It happens that the final serialization failure check executed on COMMIT only affects read/write transactions, not read-only ones. That's a pretty specific implementation detail, though, so I wouldn't necessarily rely on it... Here's an example of why applications must not depend on results read during a transaction that later aborted: W2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE W2: UPDATE t SET count=1 WHERE id=1; W1: BEGIN TRANSACTION ISOLATIONLEVEL SERIALIZABLE W1: SELECT * FROM t WHERE id=1; W2: COMMIT; R : BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLEREAD ONLY R : SELECT * FROM t; R : COMMIT;! W1: UPDATE t SET count=1 WHERE id=2; W1: COMMIT; If you try this, it'll cause a serialization failure on the line marked with a '!'. W1 saw (1,0) in the table, so W1 appears to have executed before W2. But R saw both (1,1) and (2,0) in the table, and that has to be a consistent snapshot of the database state, meaning W2 appears to have executed before W1. That's an inconsistency, so something has to be rolled back. This particular anomaly requires all three of the transactions, and so it can't be detected until W1 does its UPDATE. Postgres detects the conflict at that point and rolls back W1. So what does this have to do with relying on the results of read-only transactions that abort? Well, what if you had instead had R ROLLBACK instead of COMMIT -- maybe because you expected ROLLBACK and COMMIT to be equivalent for transactions that don't modify the database, or maybe because something else caused the transaction to abort? When W1 does its update, it will be checked for serialization failures, but aborted transactions are (intentionally) not included in those checks. W1 is therefore allowed to commit; the apparent serial order of execution is W1 followed by W2, and the results of the aborted transaction R aren't consistent with that. Dan -- Dan R. K. Ports UW CSE http://drkp.net/
AK <alkuzo@gmail.com> wrote: > I cannot have a read-only transaction fail because of > serialization anomalies. Can someone show me a working example > please? A common case is a read-only transaction reading a closed batch without seeing all of its entries. http://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Jan7, 2014, at 00:38 , Jim Nasby <jim@nasby.net> wrote: > This email and the previous one are an awesome bit of information, > can we add it to the docs somehow? Even if it's just dumping the > emails into a wiki page and referencing it? Most of what I wrote there can be found in README-SSE, I think, under "Apparent Serial Order of Execution", "Heap locking" and "Index AM implementations". I guess it'd be nice if we explained these things in the docs somewhere, though I'm not sure what level of detail would be appropriate. Maybe a good compromise would be to explain dependency graphs, but skip over the different kinds of dependencies (ww, rw, wr). Instead we could say that whenever a transaction *does see* another transaction's modifications it must appear after that transaction in any serial schedule, and whenever a transaction *might see* another transaction's modifications but doesn't due to begin/commit ordering it must appear before that transaction. best regards, Florian Pflug
Dan Ports <drkp@csail.mit.edu> wrote: > On Mon, Jan 06, 2014 at 05:14:12PM -0800, AK wrote: > If you try this, it'll cause a serialization failure on the line > marked with a '!'. W1 saw (1,0) in the table, so W1 appears to > have executed before W2. But R saw both (1,1) and (2,0) in the > table, and that has to be a consistent snapshot of the database > state, meaning W2 appears to have executed before W1. That's an > inconsistency, so something has to be rolled back. This > particular anomaly requires all three of the transactions, and so > it can't be detected until W1 does its UPDATE. Postgres detects > the conflict at that point and rolls back W1. Yeah, neither of the provided examples rolled back the read only transaction itself; the read only transaction caused a situation where something needed to be rolled back, but since we try to roll back a transaction which has a good chance of succeeding on retry, the read only transaction is not usually a good candidate. I created a new example on the Wiki page where the read only transaction itself must be rolled back because both of the other transactions involved have already committed: https://wiki.postgresql.org/wiki/SSI#Rollover Regarding other questions on the thread: I have no objections to moving the Wiki examples into the docs, but it seemed like a lot to include, and I'm not sure where it belongs. Ideas? Regarding the different results AK got, I set default_transaction_isolation = 'serializable' on my connections before running these for two reasons. (1) It keeps the examples more concise. (2) I think most people using serializable transactions in PostgreSQL set the default and don't set the transaction isolation level on each transaction, since (unlike strategies which rely on blocking, like S2PL) all transactions must be participating in the stricter isolation level for it to be reliable. In fact, given the performance benefits of declaring transactions READ ONLY when possible, I have seen shops that make *that* a default, too, and override it for transactions which need to write. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
AK <alkuzo@gmail.com> wrote:
> Session 1. Setting up:
>
> CREATE TABLE cars(
> license_plate VARCHAR NOT NULL,
> reserved_by VARCHAR NULL
> );
> INSERT INTO cars(license_plate)
> VALUES ('SUPRUSR'),('MIDLYPH');
>
> Session 2: W1
>
> BEGIN ISOLATION LEVEL SERIALIZABLE;
>
> UPDATE cars SET reserved_by = 'Julia'
> WHERE license_plate = 'SUPRUSR'
> AND reserved_by IS NULL;
>
> SELECT * FROM Cars
> WHERE license_plate IN('SUPRUSR','MIDLYPH');
>
> Session 3: W2
>
> BEGIN ISOLATION LEVEL SERIALIZABLE;
>
> UPDATE cars SET reserved_by = 'Ryan'
> WHERE license_plate = 'MIDLYPH'
> AND reserved_by IS NULL;
>
> COMMIT;
>
> Session 4: R
>
> BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY;
>
> SELECT * FROM Cars
> WHERE license_plate IN('SUPRUSR','MIDLYPH');
>
> Session 2: W1
>
> COMMIT;
>
> ERROR: could not serialize access due to read/write dependencies
> among transactions
>
> What am I doing wrong?
Even without the read only transaction the W1 and W2 transactions
are a classic case of write skew. It looks like it might actually
be benign, since neither transaction is updating license_plate, but
serializable logic works at the row level, not the column level.
After both transactions update the table there is write skew which
must be resolved by cancelling one of the transactions. The first
to commit "wins" and the other one will be cancelled when it
attempts to run its next statement, which may or may not be a
COMMIT.
If, for purposes of demonstration, you add a unique index on
license_plate and set enable_seqscan = off, you eliminate the
simple write skew and get into more complex ways of breaking
things. With that tweak you can run all of those transactions if
W1 skips the SELECT. You can let W1 do the SELECT as long as you
don't run R. The problem is that the SELECT in W1 sees the work of
W1 but not W2 and the SELECT in R sees the work of W2 but not W1.
We can't allow that.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Regarding this: >> So what does this have to do with relying on the results of read-only transactions that abort? Well, what if you had instead had R ROLLBACK instead of COMMIT -- maybe because you expected ROLLBACK and COMMIT to be equivalent for transactions that don't modify the database, or maybe because something else caused the transaction to abort? When W1 does its update, it will be checked for serialization failures, but aborted transactions are (intentionally) not included in those checks. W1 is therefore allowed to commit; the apparent serial order of execution is W1 followed by W2, and the results of the aborted transaction R aren't consistent with that. << So if I am reading the data and then commit, I should be always fine, correct? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569p5785757.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
AK <alkuzo@gmail.com> wrote: > So if I am reading the data and then commit, I should be always > fine, correct? If a serializable transaction successfully commits, that means that all data read within that transaction can be trusted. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Jan7, 2014, at 20:11 , Kevin Grittner <kgrittn@ymail.com> wrote: > Yeah, neither of the provided examples rolled back the read only > transaction itself; Actually, the fixed version [1] of my example does. [1] http://www.postgresql.org/message-id/8721AAD3-7A3A-4576-B10E-F2CBD1E5337A@phlo.org best regards, Florian Pflug
Florian Pflug <fgp@phlo.org> wrote: > On Jan7, 2014, at 20:11 , Kevin Grittner <kgrittn@ymail.com> wrote: >> Yeah, neither of the provided examples rolled back the read only >> transaction itself; > > Actually, the fixed version [1] of my example does. > > [1] http://www.postgresql.org/message-id/8721AAD3-7A3A-4576-B10E-F2CBD1E5337A@phlo.org Due to my lame email provider, that post didn't show for me until I had already replied. :-( You had already showed an example almost exactly like what I described in my post. I tweaked it a bit more for the Wiki page to show more clearly why SSI has to care about what the writing transaction reads. For all the database engine knows, what was read contributed to whether the application allowed it to successfully commit. By using the value from the SELECT in the UPDATE it is easier to see why it matters, although it needs to be considered either way. In other words, we seem to be in full agreement, just using different language to describe it. :-) -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company