Open Stack

On Tue, Jun 16, 2015 at 5:17 PM, Kevin Benton <blak111 at gmail.com> wrote:
> There seems to be confusion on what causes deadlocks. Can one of you explain
> to me how an optimistic locking strategy (a.k.a. compare-and-swap)  results
> in deadlocks?
>
> Take the following example where two workers want to update a record:
>
> Worker1: "UPDATE items set value=newvalue1 where value=oldvalue"
> Worker2: "UPDATE items set value=newvalue2 where value=oldvalue"
>
> Then each worker checks the count of rows affected by the query. The one
> that modified 1 gets to proceed, the one that modified 0 must retry.

Here's my understanding:  In a Galera cluster, if the two are run in
parallel on different masters, then the second one gets a write
certification failure after believing that it had succeeded *and*
reading that 1 row was modified.  The transaction -- when it was all
prepared for commit -- is aborted because the server finds out from
the other masters that it doesn't really work.  This failure is
manifested as a deadlock error from the server that lost.  The code
must catch this "deadlock" error and retry the entire thing.

I just learned about Mike Bayer's DBFacade from this thread which will
apparently make the db behave as an active/passive for writes which
should clear this up.  This is new information to me.

I hope my understanding is sound and that it makes sense.

Carl