Jeremy Cole <jcole@stripped> wrote on 03/31/2006 12:20:17 AM:
> 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
>
>
I like Jeremy's "bottom up" approach better. MySQL requests a lock, the
engine responds with what's available. To use a more physical analogy:
it's similar to getting electrical work done. Depending on how the
building is wired, you can either isolate just the one circuit or you have
to drop power to an entire floor or to the entire building. All the
electrician (MySQL) needs to know is that the particular piece to be
repaired is safe for use. How that happens is up to the building
supervisor (the storage engine).
I agree that there needs to be a request-response API designed so that
locking is no longer handled at the scheduling level but rather from
within the individual engines. I can imagine 3 sizes of locks: row locks,
page locks, and table locks. I can also imagine 4 basic types of locks:
read only (not really a lock as much as a lack of other locks), repeatable
reads, updatable reads, direct updates (I am sure the DB theologists have
more but that covers the majority of what I do)
The execution scheduler would be able to queue and distribute queries
across all engines in such a way that deadlocks were highly unlikely if it
knew which sizes of locks were being held where.
Just my two cents.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
