Hi Arjen! Just a quick clarification to ensure we are discussing the same thing: the slave deadlock problem I described is not helped by two-phase locking. The problem is that there¹s no ordering relation on updates across concurrent transactions, which enables out-of-order locking.
This is really a broken serialization issue. The thing that makes it ugly is it is very difficult to predict in advance. Once you hit it replication is broken in a way that will tend to require reprovisioning to fix, since you will have a mess from interleaved transactions as described by Baron in another post on this thread. Moreover, I think deadlock problems can also arise in single statement transactions. All you need is for two concurrent statements to contend for the same lock(s). In this case you can get a situation on the slave where T2 holds a lock required by T1 which in turn must commit for T2 to release the lock, etc. It comes from trying to follow the original commit order, which introduces a second lock outside the database This is a distributed deadlock which cannot be detected easily without using timeouts. Cheers, Robert On 5/7/09 7:09 PM PDT, "Arjen Lentz" <[email protected]> wrote: > Hi Robert, > > On 08/05/2009, at 11:39 AM, Robert Hodges wrote: >> This is an interesting idea and one that we noodled about for quite >> some >> time while working on Sequoia and other clustering software. >> However, I >> think it will not be so easy to implement. The problem is that while >> recording the "beginning" commit number can help you sort out views >> of the >> data, it does not help with ordering locks and will lead to >> deadlocks on the >> slave. The problem becomes apparent when you allow multi-statement >> transaction. > > Well, on the good side MySQL and InnoDB adhere to the two-phase > locking protocol inside transactions, that is: once a lock has been > released, no other lock can be acquired within the same transaction. > This is simply done by not having an unlock syntax at all, all locks > are released at commit/rollback. > So, deadlocks are reduced because of this compared to some other RDBMS > (who choose possible performance over deadlock risk in that case) > Not all RDBMS are the same, so a tool that supports more than one > RDBMS has a smaller set of choices to work with. > -- Robert Hodges, CTO, Continuent, Inc. Email: [email protected] Mobile: +1-510-501-3728 Skype: hodgesrm _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
