Hi Konstantin,
>> Isn't it time that MySQL's naive locking scheme was cleaned up?
>> It seems like it would make more sense to reverse the locking procedures
>> in MySQL and their handling by the storage engines. As I understand it,
>> currently MySQL asks for a table lock in all cases and the storage
>> engine (InnoDB, in this case) may downgrade that to a row lock.
>>
>> Wouldn't it make far more sense for MySQL to ask for a row lock, and the
>> storage engine (MyISAM, in this case) to upgrade that to a page lock,
>> table lock, etc. as support is available? Basically MySQL should ask
>> the storage engine for the most granular lock possible, and the storage
>> engine should upgrade that lock if it is unable to lock with that
>> granularity.
>
> What benefits does this approach give us? With the current locking
> scheme, those storage engine that don't have more granular locks
> do not need to deal with the locking themselves (and with the
> deadlock problem in particular). For them MySQL will automatically
> take all the necesary locks in a sorted order, which avoids
> deadlocks.
>
> For more advanced storage engines a table level lock is a way to
> communicate down to the storage engine the nature of the operation
> that will be performed. These storage engines should not take the
> value of the lock literally, but instead downgrade the lock to the
> one that guarantees that the operation on hand will execute
> properly.
This doesn't make any sense, as far as I'm concerned. It means that
every storage engine must interpret the meaning of every one of MySQL's
operations, and acquire the appropriate locks. In reality it means that
every storage engine will have to understand properly all of the SQLCOM
values and be updated for every new statement that is added.
This seems to horribly break compartmentalization and modularity of
code, and introduce many silly bugs, like the one I referenced in my
email, with InnoDB not interpreting a new SQLCOM value properly and
really locking the entire table for a very simple operation.
> Different (advanced) storage engines have different locking
> schemes, sometimes vastly different, e.g. one storage engine may
> use optimistic locking and another a pessimistic one. So I
> think that asking for the most granular lock won't make all the
> engines play by the same rules, as they understand different
> things under term "most granular lock".
Yes, but that's OK. At least MySQL's intentions are properly passed on
to the storage engine, instead of MySQL passing on "lock table foo" and
the storage engine having to decide what MySQL *really* means.
> There is a number of issues with our table level locking, the main
> one is that it doesn't protect us against a deadlock in a
> cross-storage engine transaction. Another problem is in the
> deadlock avoidance algorithm that we use: we can't acquire new
> locks in ad-hoc manner, and this is a showstopper for support of
> things like Dynamic SQL or recursion in stored functions and
> triggers. We're thinking what we can do about it.
Agreed. What if MySQL passes on very specific list of the most granular
things it needs locked, and the storage engine passes back exactly what
it *really* locked? Then MySQL can handle deadlock detection on a
higher level.
> PS And thanks for bringing this up, we spend a great deal of
> time thinking how we should rework our locking scheme to better
> support the SQL standard and Cluster.
I think that the old way worked fine with simplistic storage engines,
but as the storage engines used are more advanced now, MySQL needs to
better communicate its intentions to the storage engine in a standard
way, rather than assuming that the storage engine will "break the rules"
of modularity and try to examine what exact statement is being run to
decide what to lock.
Regards,
Jeremy
--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
