4 ms·

How are "serializable" transactions in PostgreSQL different from optimistic concurrency control? The docs say: > In fact, this isolation level works exactly t

by dap 10y ago

How are "serializable" transactions in PostgreSQL different from optimistic concurrency control? The docs say: > In fact, this isolation level works exactly the same as Repeatable Read except that it monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions. This monitoring does not introduce any blocking beyond that present in repeatable read, but there is some overhead to the monitoring, and detection of the conditions which could cause a serialization anomaly will trigger a serialization failure. As far as I can tell, this doesn't make transactions any more serializable; it just fails them if they wouldn't have been serializable anyway. And then clients typically retry. That sounds just like OCC. Like OCC, I'd expect that under any kind of contention, this could lead to very large numbers of failures and retries. It's not quite livelock, since at least one client will always make forward progress, but close to it.

dexwiz 10y ago
It sounds like if the result is the same if the transactions are reordered, then the second transaction is not aborted. Simple version numbers will always fail if the version number is incremented.
- altendo 10y ago
  They will behave similarly in similar situations. There's four scenarios for 2 transactions: 1) Transaction 1 starts and commits before Transaction 2 starts. 2) Transaction 1 starts; Transaction 2 starts; Transaction 1 commits; Transaction 2 attempts to commit. 3) Transaction 2 starts; Transaction 1 starts; Transaction 2 commits; Transaction 1 attempts to commit. 4) Transaction 2 starts and commits before Transaction 1 starts. 1+4 and 2+3 are the same. In 1+4, the transactions are serialized - they are wholly independent of each other, so the second transaction will always see the updated version, so there's no worries about failing on that. In 2+3, the version number increments when the first transaction commits. For optimistic concurrency control, the version number will have changed, which is detected prior to updating. (In the OP's example, the query is an UPDATE balance WHERE version = X, and since the version is X+1, zero rows get updated.) For the SERIALIZABILITY concern, it will fail because internally the version has changed for the given row ("version" referring to the metadata Postgres keeps to track which rows are current), kicking it back to the user with an error. I don't think there's a scenario where reordering lets a SERIALIZE'd transaction succeed but optimistic locking fails. They are effectively - as far as the methodology used - the same thing (with some differences that the OP mentions). EDIT: minor readability things
altendo 10y ago
One major difference is that optimistic concurrency control is handled by the calling application and not in the database. Your app should handle the error in either case, but in the case of OCC you end up doing the bookkeeping yourself, which may be preferable (lower overhead for Postgres to handle the query for instance).
- altendo 10y ago
  derp. OP has a small statement on the differences: > Unlike SERIALIZABLE isolation, it works even in autocommit mode or if the statements are in separate transactions. For this reason it’s often a good choice for web applications that might have very long user “think time” pauses or where clients might just vanish mid-session, as it doesn’t need long-running transactions that can cause performance problems.
ztorkelson 10y ago
Well, first: to properly implement OCC, you need to validate not just your write set (as implemented in some ORMs) but also your read set. Otherwise you're only addressing write-after-write anomalies, leaving write-after-read unaddressed. Second: there's the issue of precision to think about. Neither Postgres SSI nor OCC are perfectly precise; both may incorrectly reject transactions which would not have actually violated serializability. But Postgres SSI is an improvement because it allows certain classes of (provably non-anomalous) write-after-read interleavings to proceed. The SSI paper[1] is a good read on the subject, and even contains an interesting description of a 3-way serialization conflict involving (requiring(!)) a read-only transaction such that if the read-only transaction were omitted then there would be no conflict among the other two read-write transactions. [1]: https://drkp.net/papers/ssi-vldb12.pdf https://drkp.net/papers/ssi-vldb12.pdf

by dap 10y ago

dexwiz 10y ago

It sounds like if the result is the same if the transactions are reordered, then the second transaction is not aborted. Simple version numbers will always fail if the version number is incremented.

altendo 10y ago
They will behave similarly in similar situations. There's four scenarios for 2 transactions: 1) Transaction 1 starts and commits before Transaction 2 starts. 2) Transaction 1 starts; Transaction 2 starts; Transaction 1 commits; Transaction 2 attempts to commit. 3) Transaction 2 starts; Transaction 1 starts; Transaction 2 commits; Transaction 1 attempts to commit. 4) Transaction 2 starts and commits before Transaction 1 starts. 1+4 and 2+3 are the same. In 1+4, the transactions are serialized - they are wholly independent of each other, so the second transaction will always see the updated version, so there's no worries about failing on that. In 2+3, the version number increments when the first transaction commits. For optimistic concurrency control, the version number will have changed, which is detected prior to updating. (In the OP's example, the query is an UPDATE balance WHERE version = X, and since the version is X+1, zero rows get updated.) For the SERIALIZABILITY concern, it will fail because internally the version has changed for the given row ("version" referring to the metadata Postgres keeps to track which rows are current), kicking it back to the user with an error. I don't think there's a scenario where reordering lets a SERIALIZE'd transaction succeed but optimistic locking fails. They are effectively - as far as the methodology used - the same thing (with some differences that the OP mentions). EDIT: minor readability things

altendo 10y ago

One major difference is that optimistic concurrency control is handled by the calling application and not in the database. Your app should handle the error in either case, but in the case of OCC you end up doing the bookkeeping yourself, which may be preferable (lower overhead for Postgres to handle the query for instance).

altendo 10y ago
derp. OP has a small statement on the differences: > Unlike SERIALIZABLE isolation, it works even in autocommit mode or if the statements are in separate transactions. For this reason it’s often a good choice for web applications that might have very long user “think time” pauses or where clients might just vanish mid-session, as it doesn’t need long-running transactions that can cause performance problems.

ztorkelson 10y ago

Well, first: to properly implement OCC, you need to validate not just your write set (as implemented in some ORMs) but also your read set. Otherwise you're only addressing write-after-write anomalies, leaving write-after-read unaddressed. Second: there's the issue of precision to think about. Neither Postgres SSI nor OCC are perfectly precise; both may incorrectly reject transactions which would not have actually violated serializability. But Postgres SSI is an improvement because it allows certain classes of (provably non-anomalous) write-after-read interleavings to proceed. The SSI paper[1] is a good read on the subject, and even contains an interesting description of a 3-way serialization conflict involving (requiring(!)) a read-only transaction such that if the read-only transaction were omitted then there would be no conflict among the other two read-write transactions. [1]: https://drkp.net/papers/ssi-vldb12.pdf https://drkp.net/papers/ssi-vldb12.pdf