----- Original Message -----
> From: "Jay Pipes" <jaypi...@gmail.com>
> To: email@example.com
> Sent: Monday, February 9, 2015 9:36:45 PM
> Subject: Re: [openstack-dev] [all][oslo.db][nova] TL; DR Things everybody
> should know about Galera
> On 02/09/2015 03:10 PM, Clint Byrum wrote:
> > Excerpts from Jay Pipes's message of 2015-02-09 10:15:10 -0800:
> >> On 02/09/2015 01:02 PM, Attila Fazekas wrote:
> >>> I do not see why not to use `FOR UPDATE` even with multi-writer or
> >>> Is the retry/swap way really solves anything here.
> >> <snip>
> >>> Am I missed something ?
> >> Yes. Galera does not replicate the (internal to InnnoDB) row-level locks
> >> that are needed to support SELECT FOR UPDATE statements across multiple
> >> cluster nodes.
> >> https://groups.google.com/forum/#!msg/codership-team/Au1jVFKQv8o/QYV_Z_t5YAEJ
> > Attila acknowledged that. What Attila was saying was that by using it
> > with Galera, the box that is doing the FOR UPDATE locks will simply fail
> > upon commit because a conflicting commit has already happened and arrived
> > from the node that accepted the write. Further what Attila is saying is
> > that this means there is not such an obvious advantage to the CAS method,
> > since the rollback and the # updated rows == 0 are effectively equivalent
> > at this point, seeing as the prior commit has already arrived and thus
> > will not need to wait to fail certification and be rolled back.
> No, that is not correct. In the case of the CAS technique, the frequency
> of rollbacks due to certification failure is demonstrably less than when
> using SELECT FOR UPDATE and relying on the certification timeout error
> to signal a deadlock.
> > I am not entirely certain that is true though, as I think what will
> > happen in sequential order is:
> > writer1: UPDATE books SET genre = 'Scifi' WHERE genre = 'sciencefiction';
> > writer1: --> send in-progress update to cluster
> > writer2: SELECT FOR UPDATE books WHERE id=3;
> > writer1: COMMIT
> > writer1: --> try to certify commit in cluster
> > ** Here is where I stop knowing for sure what happens **
> > writer2: certifies writer1's transaction or blocks?
> It will certify writer1's transaction. It will only block another thread
> hitting writer2 requesting write locks or write-intent read locks on the
> same records.
> > writer2: UPDATE books SET genre = 'sciencefiction' WHERE id=3;
> > writer2: COMMIT --> One of them is rolled back.
The other transaction can be rolled back before you do an actual commit:
writer1: update test set val=42 where id=1;
writer2: update test set val=42 where id=1;
writer2: show variables;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting
As you can see 2th transaction failed without issuing a COMMIT after the 1th
You could write anything to mysql on writer2 at this point,
even invalid things returns with `Deadlock`.
> > So, at that point where I'm not sure (please some Galera expert tell
> > me):
> > If what happens is as I suggest, writer1's transaction is certified,
> > then that just means the lock sticks around blocking stuff on writer2,
> > but that the data is updated and it is certain that writer2's commit will
> > be rolled back. However, if it blocks waiting on the lock to resolve,
> > then I'm at a loss to determine which transaction would be rolled back,
> > but I am thinking that it makes sense that the transaction from writer2
> > would be rolled back, because the commit is later.
> That is correct. writer2's transaction would be rolled back. The
> difference is that the CAS method would NOT trigger a ROLLBACK. It would
> instead return 0 rows affected, because the UPDATE statement would
> instead look like this:
> UPDATE books SET genre = 'sciencefiction' WHERE id = 3 AND genre = 'SciFi';
> And the return of 0 rows affected would trigger a simple retry of the
> read and then update attempt on writer2 instead of dealing with ROLLBACK
> semantics on the transaction.
> Note that in the CAS method, the SELECT statement and the UPDATE are in
> completely different transactions. This is a very important thing to
> keep in mind.
> > All this to say that usually the reason for SELECT FOR UPDATE is not
> > to only do an update (the transactional semantics handle that), but
> > also to prevent the old row from being seen again, which, as Jay says,
> > it cannot do. So I believe you are both correct:
> > * Attila, yes I think you're right that CAS is not any more efficient
> > at replacing SELECT FOR UPDATE from a blocking standpoint.
> It is more efficient because there are far fewer ROLLBACKs of
> transactions occurring in the system.
> If you look at a slow query log (with a 0 slow query time) for a MySQL
> Galera server in a multi-write cluster during a run of Tempest or Rally,
> you will notice that the number of ROLLBACK statements is extraordinary.
> AFAICR, when Peter Boros and I benchmarked a Rally launch and delete 10K
> VM run, we saw nearly 11% of *total* queries executed against the server
> were ROLLBACKs. This, in my opinion, is the main reason that the CAS
> method will show as more efficient.
You need to finish even the read-only transaction somehow before letting other
task to get the connection. You need to issue either ROLLBACK or COMMIT;
Basically it will just release your snapshot view.
Usually connection managers/pools issuing ROLLBACKs to ensure connection
The CAS method requires to use an additional ROLLBACK.
writer1: SELECT id FROM test WHERE val is NULL limit 1; # returns with id=2
writer2: SELECT id FROM test WHERE val is NULL limit 1; # returns with id=2
writer2: UPDATE test SET val=42 WHERE id = 2 AND val is NULL; # (Rows matched:
1 Changed: 1 Warnings: 0)
writer1: UPDATE test SET val=42 WHERE id = 2 AND val is NULL; # (Rows matched:
0 Changed: 0 Warnings: 0)
# changed is 0, lets try to find another id
SELECT id FROM test WHERE val is NULL limit 1; # returns 2
Because the reads are repeatable, I get back the already used id=2.
How to get different id with the above query (without FOR UPDATE) ?
writer1: SELECT id FROM test WHERE val is NULL limit 1; # returns id=3.
> > * Jay, yes I think you're right that SELECT FOR UPDATE is not the right
> > thing to use to do such reads, because one is relying on locks that are
> > meaningless on a Galera cluster.
> > Where I think the CAS ends up being the preferred method for this sort
> > of thing is where one consideres that it won't hold a meaningless lock
> > while the transaction is completed and then rolled back.
> CAS is preferred because it is measurably faster and more
> obstruction-free than SELECT FOR UPDATE. A colleague of mine is almost
> ready to publish documentation showing a benchmark of this that shows
> nearly a 100% decrease in total amount of lock/wait time using CAS
> versus waiting for the coarser-level certification timeout to retry the
> transactions. As mentioned above, I believe this is due to the dramatic
> decrease in ROLLBACKs.
I tried different methods for doing similar ops,
The FOR UPDATE was slower when you forget to define the index like
After the index was in place the FOR UPDATE was one of fastest way,
the CAS way was comparable fast only when I used strange queries like bellow,
with 'read committed' isolation level.:
'SELECT id FROM (SELECT id FROM test WHERE used=0 LIMIT 10) AS T ORDER BY
RAND() LIMIT 1'
anyway, this kind of allocations many-many times faster compared
to how many vm's nova can schedule per sec.
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: openstack-dev-requ...@lists.openstack.org?subject:unsubscribe
OpenStack Development Mailing List (not for usage questions)