Re: MySQL's locking schemes due for an upgrade?

[Heikki Tuuri]

Jeremy,

Jeremy Cole wrote:
> Hi Heikki, All,
> 
> Isn't it time that MySQL's naive locking scheme was cleaned up?
> 
> Based on this bug: http://bugs.mysql.com/bug.php?id=16229
> 
> 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.

this particular bug, using full explicit table locks in triggers, was 
due to MySQL setting the flag thd->in_lock_tables = TRUE in the 
processing of a trigger (and also in a stored procedure). The root cause 
may be that thd->in_lock_tables is not documented in sql_class.h, and 
nobody knows what the flag actually means.

InnoDB needs to decide in ::store_lock():

1) whether to use a consistent read or an S-locking read;
2) whether to set a table lock; currently, this is only needed in LOCK 
TABLES, TRUNCATE, DISCARD/IMPORT TABLESPACE.

> It seems like as new features are added, more "stupid" mistakes like 
> this will occur, if the current locking scheme is kept.  It also seems 
> that storage engines downgrading locks on a whim may introduce more 
> serious bugs in the future.

I agree. The downgrade/upgrade rules should be clearly documented. The 
role of thd->in_lock_tables should be clarified.

> Regards,
> 
> Jeremy

Regards,

Heikki