Hello Jeremy,
* Jeremy Cole <jcole@stripped> [06/03/01 00:41]:
> 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.
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".
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.
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.
--
Konstantin Osipov, Software Developer
MySQL AB, www.mysql.com
