[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-internals
Subject:    Re: MySQL's locking schemes due for an upgrade?
From:       Konstantin Osipov <konstantin () mysql ! com>
Date:       2006-03-31 16:48:59
Message-ID: 20060331164859.GO11138 () dragonfly ! local
[Download RAW message or body]

Hello Jeremy,

* Jeremy Cole <jcole@yahoo-inc.com> [06/03/31 19:43]:

> > 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.
> 
> 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.

I think I said something different: 

--cut
> > a table level lock is a way to communicate down to the storage
> > engine the nature of the operation that will be performed.
--end cut

The fact that ha_innodb.cc looks at thd->sql_command is a
shortcoming of the implementation but not of the design.
We should explicitly supply InnoDB with the missing information
using the handler API.

> > 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.

enum thr_lock_type has 12 different lock types, starting from
simple TL_READ and ending with TL_WRITE_ONLY. 
This is perhaps not satisfactory, but it's never as simple as
"LOCK TABLE foo".
Speaking of row-level locks: one approach doesn't replace another.
MySQL 3.23 had support for Gemini, and then in addition to
table-level locks we informed the storage engine about row-level
locks. We will return to this scheme to implement certain 5.2
features, but this is not a replacement for the table level
locking mechanism.

Finally, the nature of the bug you refer to is different -- as
Heikki rightfully noted, the problem is that in 5.0 the semantics
of thd->in_lock_tables was changed and this wasn't communicated in
time to the InnoDB team.

-- 
Konstantin Osipov, Software Developer
MySQL AB, www.mysql.com

-- 
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe:    http://lists.mysql.com/internals?unsub=mysql-internals@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]