Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why CockroachDB doesn't use EvalPlanQual

cockroachlabs.com

Post reply on HN
Print view View original

31–40 of 63 posts

Re: Why CockroachDB doesn't use EvalPlanQual

#31

Post by michae2 »

CGamesPlay wrote: post #18

How does the CockroachDB approach not deadlock? Surely retrying could encounter a situation where two competing UPDATE will lock rows in different order, and no amount of retrying will unlock the required rows, right?

It's a good question. For simple UPDATEs, CockroachDB always executes in a deterministic, serial order and so it's likely the rows will be locked in the same order by any competing updates. (This can be confirmed by looking at the query plans.) Complex UPDATEs using joins and subqueries will need explicit ORDER BY to always lock in the same order.

If an UPDATE has to retry halfway through, locks are held across the retry to help the system make progress. But as you point out, this could cause lock acquisition to happen in an unexpected order if new rows qualify during a retry. So far we haven't run into this, but we might need to provide an option for an UPDATE to drop locks on retry if deadlock turns into a bigger problem than livelock. It depends on the workload.

Re: Why CockroachDB doesn't use EvalPlanQual

#33

Post by masklinn »

erhaetherth wrote: post #8

I'm having trouble with the example given. If `UPDATE player SET level = 'AA' WHERE team = 'Gophers';` is executed before the player swap, then why should "Stonebreaker" be upgraded to "AA"? I'd be pretty mad at my database if I sent those 2 queries in sequence and my DB decided to re-order them. The sleep actually really complicates things here. I understand some queries run slower than others and the sleep is a use…

> If `UPDATE player SET level = 'AA' WHERE team = 'Gophers';` is executed before the player swap, then why should "Stonebreaker" be upgraded to "AA"? I'd be pretty mad at my database if I sent those 2 queries in sequence and my DB decided to re-order them.

It’s easy to miss but in the swap query the levels also get swapped. Because — and it’s harder to miss but easy to skip over — given what constraint 2 says the level is actually a team level, not a player level.

So in a seqcst view, either the team’s players get upgraded to AA then the players’ levels get swapped during the exchange, or the players get exchanged then the team’s players get upgraded.

In both sequences you end up with Stonebaker as an AA gopher and Lamport as an A dolphin.

Re: Why CockroachDB doesn't use EvalPlanQual

#34

Post by npstr »

masklinn wrote: post #33
erhaetherth wrote: post #8

I'm having trouble with the example given. If `UPDATE player SET level = 'AA' WHERE team = 'Gophers';` is executed before the player swap, then why should "Stonebreaker" be upgraded to "AA"? I'd be pretty mad at my database if I sent those 2 queries in sequence and my DB decided to re-order them. The sleep actually really complicates things here. I understand some queries run slower than others and the sleep is a use…

> If `UPDATE player SET level = 'AA' WHERE team = 'Gophers';` is executed before the player swap, then why should "Stonebreaker" be upgraded to "AA"? I'd be pretty mad at my database if I sent those 2 queries in sequence and my DB decided to re-order them. It’s easy to miss but in the swap query the levels also get swapped. Because — and it’s harder to miss but easy to skip over — given what constraint 2 says the lev…

> given what constraint 2 says the level is actually a team level, not a player level.

Then it's bad unnormalized data design that is the problem here. If that is a team level, it should be in the team table, not the player table.

Re: Why CockroachDB doesn't use EvalPlanQual

#36

Post by temac »

michae2 wrote: post #31
CGamesPlay wrote: post #18

How does the CockroachDB approach not deadlock? Surely retrying could encounter a situation where two competing UPDATE will lock rows in different order, and no amount of retrying will unlock the required rows, right?

It's a good question. For simple UPDATEs, CockroachDB always executes in a deterministic, serial order and so it's likely the rows will be locked in the same order by any competing updates. (This can be confirmed by looking at the query plans.) Complex UPDATEs using joins and subqueries will need explicit ORDER BY to always lock in the same order. If an UPDATE has to retry halfway through, locks are held across the r…

> So far we haven't run into this, but we might need to provide an option for an UPDATE to drop locks on retry if deadlock turns into a bigger problem than livelock.

I'm not sure what you mean by that: the design can deadlock but you just have not seen it happening yet?

Edit: oh i see in a comment bellow that deadlocks are detected and abort the transaction.

Re: Why CockroachDB doesn't use EvalPlanQual

#37

Post by masklinn »

npstr wrote: post #34
masklinn wrote: post #33

Earlier quoted context omitted.

> If `UPDATE player SET level = 'AA' WHERE team = 'Gophers';` is executed before the player swap, then why should "Stonebreaker" be upgraded to "AA"? I'd be pretty mad at my database if I sent those 2 queries in sequence and my DB decided to re-order them. It’s easy to miss but in the swap query the levels also get swapped. Because — and it’s harder to miss but easy to skip over — given what constraint 2 says the lev…

> given what constraint 2 says the level is actually a team level, not a player level. Then it's bad unnormalized data design that is the problem here. If that is a team level, it should be in the team table, not the player table.

> Then it's bad unnormalized data design that is the problem here.

That the table is not normalised makes the example somewhat confusing but it does not actually affect the issue being demonstrated. And denormalisation is a fact of data modelling.

Re: Why CockroachDB doesn't use EvalPlanQual

#38

Post by npstr »

masklinn wrote: post #37
npstr wrote: post #34

Earlier quoted context omitted.

> given what constraint 2 says the level is actually a team level, not a player level. Then it's bad unnormalized data design that is the problem here. If that is a team level, it should be in the team table, not the player table.

> Then it's bad unnormalized data design that is the problem here. That the table is not normalised makes the example somewhat confusing but it does not actually affect the issue being demonstrated. And denormalisation is a fact of data modelling.

Why not simply use an example that isn't confusing? Many developers, and especially academics, love wasting effort and time on solving issues they made up but that have no real life examples. When using examples that have trivial alternative solutions, it does not help me as a reader to distinguish whether this is a real problem, or something made up.

Re: Why CockroachDB doesn't use EvalPlanQual

#39

Post by paulryanrogers »

wbl wrote: post #25
foota wrote: post #2

It's often talked about how new sql databases offer better scalability than standard SQL databases, but I think it's maybe sometimes underappreciated how (some, not all) of them are also much simpler in terms of their consistency models. I'd speculate this is because postgres and friends try to eek out every bit of single node performance (which helps with single row throughout and overall throughout, which is obviou…

Read committed is explicitly asking for hard mode. If you want a simple life stick with Serializable as always. It took years before people found anomalies in Repeatable Read in Postgres. This stuff is hard even for world class researchers.

How does one get serializable in a multi-writer system without a lot more locking and having to retry at app layer?
Post reply on HN