4 ms·

What Write Skew Looks Like

89 points by foldU 8y ago

dang 8y ago
Url changed from https://www.cockroachlabs.com/blog/what-write-skew-looks-like/ https://www.cockroachlabs.com/blog/what-write-skew-looks-lik..., which points to this.
- zokier 8y ago
  Pictures are now 404 for me @justinjaffray.com, so while the crdb link might not be canonical it right now is bit more useful.
  - foldU 8y ago
    Sorry, that was my fault! Should be fixed now.
jchrisa 8y ago
If you are interested in a similar discussion of consistency levels, write skew, and how FaunaDB allows indexes to opt-in to serializability, this blog post covers a lot of ground, but brings up index write skew toward the end: https://blog.fauna.com/acid-transactions-in-a-globally-distributed-database https://blog.fauna.com/acid-transactions-in-a-globally-distr...
ztorkelson 8y ago
Well written and detailed comparison of snapshot and serializable isolation levels. From the article: A more concrete reason than “nothing else makes sense” is a question of local vs. global reasoning. If a set of transactions must maintain some kind of invariant within the database (for instance, the sum of some set of fields is always greater than zero). In a database that guarantees serializability, it’s sufficient to verify that every individual transaction maintains this invariant on its own. With anything less than serializability, including Snapshot, one must consider the interactions between every transaction to ensure said invariants are upheld. This is a significant increase in the amount of work that must be done (though in reality, I think the situation is that people simply don’t do it), a point made by Alan Fekete in this talk[1] on isolation. This bears repeating. In any non-trivial transactional application, the combination of concurrency and non-serializable transaction isolation levels is a serious bug farm. Most app/service backends fall for this trap. Unfortunately, most OLTP database systems have incredibly poor implementations of the serializable isolation level. Postgres is a notable exception, and it's nice to see some newer database systems--like CockroachDB--making progress in this area. [1] https://www.youtube.com/watch?v=IP-S_RHlsEQ https://www.youtube.com/watch?v=IP-S_RHlsEQ (Thanks for the link; I hadn't seen this talk before.)
- arjunnarayan 8y ago
  > Postgres is a notable exception Did you have good experiences with postgres' serializable mode? When I tried to do some TPC-C benchmarking with postgres set explicitly in serializable mode, it would fall over almost instantly (read: not able to get beyond ~100 warehouses). I'd love to read anything you have to say about getting good performance out of Postgres in serializable mode, because I was unable to find this promised land myself.
  - drkp 8y ago
    I have some experience with that specific question, although it was a few years ago... Do you remember what conflict was causing things to fall over? In general, the usual important parameters to tweak are: - max_pred_locks_per_transaction may need to be increased; otherwise locks will switch to coarse granularity to save room in the lock table - for tables that fit in memory, the planner may choose a sequential scan even when an index scan is available, which can be faster but creates more conflicts on a serializable workload. Increasing cpu_tuple_cost should avoid that (or even just enable_seqscan=off to force indexes whenever available)
  - ztorkelson 8y ago
    I actually don't have much hands-on experience with Postgres, so I can't speak to how it performs in practice. My comment was highlighting that Postgres' implementation of SSI is at least a better starting point than most other purely pessimistic implementations of the serializable isolation level. It does not surprise me, however, to hear that there are still performance deficiencies in practice. For example, the Postgres implementation is imprecise, which will result in spurious transaction aborts (those will presumably be retried, but it comes at a cost to latency and throughput). And even though reads are optimistic (which will perform better than pessimistic concurrency control, if contention rates are sufficiently low), maintaining transaction dependency metadata can still have a non-negligible cost. I also think Postgres suffers from other more dubious choices--like a lack of clustered indexes--which can exacerbate things. My work in this area has focused on revisiting various architectural and implementation design decisions which have contributed to the current state of affairs. I think the relational data model is generally the right choice, but most relational databases have some godawful characteristics around performance, scalability, reliability, and programmability which limit their efficacy in practice.
sriram_malhar 8y ago
I like Jim Gray's simple explanation (as always) of the difference. A database has two white marbles and two black marbles. Transaction T1: Update all white to black. Transaction T2: Update all black to white. A serializable database will ensure that the transactions happen in some serial order. At the end of performing them one after another, either all marbles are white or all black. In snapshot isolation, T1 updates only the white marbles, T2 updates only the black marbles. So they are not seen as conflicting. The end result is that the database contains 2 black (formerly white) and 2 white marbles (formerly black),because the two transactions are working off a snapshot and not conflicting.

89 points by foldU 8y ago

dang 8y ago

Url changed from https://www.cockroachlabs.com/blog/what-write-skew-looks-like/ https://www.cockroachlabs.com/blog/what-write-skew-looks-lik..., which points to this.

zokier 8y ago
Pictures are now 404 for me @justinjaffray.com, so while the crdb link might not be canonical it right now is bit more useful.
- foldU 8y ago
  Sorry, that was my fault! Should be fixed now.

jchrisa 8y ago

If you are interested in a similar discussion of consistency levels, write skew, and how FaunaDB allows indexes to opt-in to serializability, this blog post covers a lot of ground, but brings up index write skew toward the end: https://blog.fauna.com/acid-transactions-in-a-globally-distributed-database https://blog.fauna.com/acid-transactions-in-a-globally-distr...

ztorkelson 8y ago

Well written and detailed comparison of snapshot and serializable isolation levels. From the article: A more concrete reason than “nothing else makes sense” is a question of local vs. global reasoning. If a set of transactions must maintain some kind of invariant within the database (for instance, the sum of some set of fields is always greater than zero). In a database that guarantees serializability, it’s sufficient to verify that every individual transaction maintains this invariant on its own. With anything less than serializability, including Snapshot, one must consider the interactions between every transaction to ensure said invariants are upheld. This is a significant increase in the amount of work that must be done (though in reality, I think the situation is that people simply don’t do it), a point made by Alan Fekete in this talk[1] on isolation. This bears repeating. In any non-trivial transactional application, the combination of concurrency and non-serializable transaction isolation levels is a serious bug farm. Most app/service backends fall for this trap. Unfortunately, most OLTP database systems have incredibly poor implementations of the serializable isolation level. Postgres is a notable exception, and it's nice to see some newer database systems--like CockroachDB--making progress in this area. [1] https://www.youtube.com/watch?v=IP-S_RHlsEQ https://www.youtube.com/watch?v=IP-S_RHlsEQ (Thanks for the link; I hadn't seen this talk before.)

arjunnarayan 8y ago
> Postgres is a notable exception Did you have good experiences with postgres' serializable mode? When I tried to do some TPC-C benchmarking with postgres set explicitly in serializable mode, it would fall over almost instantly (read: not able to get beyond ~100 warehouses). I'd love to read anything you have to say about getting good performance out of Postgres in serializable mode, because I was unable to find this promised land myself.
- drkp 8y ago
  I have some experience with that specific question, although it was a few years ago... Do you remember what conflict was causing things to fall over? In general, the usual important parameters to tweak are: - max_pred_locks_per_transaction may need to be increased; otherwise locks will switch to coarse granularity to save room in the lock table - for tables that fit in memory, the planner may choose a sequential scan even when an index scan is available, which can be faster but creates more conflicts on a serializable workload. Increasing cpu_tuple_cost should avoid that (or even just enable_seqscan=off to force indexes whenever available)
- ztorkelson 8y ago
  I actually don't have much hands-on experience with Postgres, so I can't speak to how it performs in practice. My comment was highlighting that Postgres' implementation of SSI is at least a better starting point than most other purely pessimistic implementations of the serializable isolation level. It does not surprise me, however, to hear that there are still performance deficiencies in practice. For example, the Postgres implementation is imprecise, which will result in spurious transaction aborts (those will presumably be retried, but it comes at a cost to latency and throughput). And even though reads are optimistic (which will perform better than pessimistic concurrency control, if contention rates are sufficiently low), maintaining transaction dependency metadata can still have a non-negligible cost. I also think Postgres suffers from other more dubious choices--like a lack of clustered indexes--which can exacerbate things. My work in this area has focused on revisiting various architectural and implementation design decisions which have contributed to the current state of affairs. I think the relational data model is generally the right choice, but most relational databases have some godawful characteristics around performance, scalability, reliability, and programmability which limit their efficacy in practice.

sriram_malhar 8y ago

I like Jim Gray's simple explanation (as always) of the difference. A database has two white marbles and two black marbles. Transaction T1: Update all white to black. Transaction T2: Update all black to white. A serializable database will ensure that the transactions happen in some serial order. At the end of performing them one after another, either all marbles are white or all black. In snapshot isolation, T1 updates only the white marbles, T2 updates only the black marbles. So they are not seen as conflicting. The end result is that the database contains 2 black (formerly white) and 2 white marbles (formerly black),because the two transactions are working off a snapshot and not conflicting.