Bug #46663 Table locked in EXCLUSIVE MODE is still accessible from other connections
Submitted: 12 Aug 2009 2:26 Modified: 4 Apr 2011 19:03
Reporter: Elena Stepanova Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.4.4-alpha OS:Any
Assigned to: CPU Architecture:Any

[12 Aug 2009 2:26] Elena Stepanova
Description:
If a connection locks an InnoDB table using IN EXCLUSIVE MODE syntax, other connections can still read from the table (although not write into it).

How to repeat:
--source include/have_innodb.inc

USE test;

--disable_warnings
DROP TABLE IF EXISTS excl_lock_test;
--enable_warnings
CREATE TABLE excl_lock_test ( i INT ) ENGINE = InnoDB;
INSERT INTO excl_lock_test VALUES (1);

connect(dml,localhost,root,,test);

connection default;

SET AUTOCOMMIT = 0;
LOCK TABLES excl_lock_test IN EXCLUSIVE MODE;

connection dml;

SELECT * FROM excl_lock_test;
[3 Sep 2009 14:01] Ingo Strüwing
Since transactional locking is entirely handled within the storage engine, I suggest to assign it to InnoDB.
[23 Sep 2009 9:25] Konstantin Osipov
I agree with Ingo's assessment. We have no mechanism to prevent SELECT from going through in the server. Should be resolved within InnoDB.
[14 Oct 2009 13:26] Mikhail Izioumtchenko
Elena, does it happen with 5.1? It looks like a feature of consistent read in fact. Possible workarounds: use stricter txn isolation lever, use SELECT ... LOCK IN SHARED MODE.
[14 Oct 2009 14:31] Elena Stepanova
Hi Michael,

LOCK TABLES ... IN [EXCLUSIVE|SHARE] MODE syntax does not exist in 5.1 (not in the 'new' 5.4, for that matter -- i think it's in 6.0-codebase at the moment).

I'm also not quite sure about the workarounds you mentioned:

- the provided scenario uses default txn isolation level, which is REPEATABLE-READ; if you suggest SERIALIZABLE, it works the same way.

- SELECT ... LOCK IN SHARED MODE legitimately allows read access from other connections, so how it can be used to get an exclusive lock on a table?
[14 Oct 2009 14:55] Mikhail Izioumtchenko
Thanks Elena. If 6.0, it goes to Calvin,but I have a couple of questions still, below. 5.1 does have LOCK TABLE, but apparently not the EXCLUSIVE clause, section 12.5.4 in the manual
12.4.5. LOCK TABLES and UNLOCK TABLES Syntax
LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] ...
lock_type:
READ [LOCAL]
| [LOW_PRIORITY] WRITE
UNLOCK TABLES.
Elena, another procedural thing, could you verify what happens when you use a MyISAM table, or falcon, to make sure it is an InnoDB bug?
[14 Oct 2009 16:17] Konstantin Osipov
Michael, this feature does not exist in MyISAM or Falcon, it was added for InnoDB specifically.
For MyISAM transactional locks are downgraded to non-transactional ones.
[14 Oct 2009 16:44] Elena Stepanova
Michael,

I expect the transaction LOCK TABLE will eventually make it to one of 'earlier' trees, it's just that currently it is located in a tree i mentioned.

Yes, LOCK TABLES .... READ|WRITE still exists and it works all right in regard to the shared access, but it is different from the new LOCK TABLES ... IN EXCLUSIVE|SHARE MODE logic.

MyISAM is a non-transactional engine, so the transactional locks are converted into standard READ|WRITE locks with a warning and then work as expected from the conventional locks (or, if sql_mode is set to STRICT*, an attempt to acquire a transactional lock returns an error).

Falcon does not exist in the 6.0-codebase tree, but as of the current falcon tree, transactional locks work for it the same way as for MyISAM -- I suppose it means that Falcon does not support them (yet).
[15 Dec 2010 14:14] Ingo Strüwing
This belongs to WL#3561 (transactional LOCK TABLE), which is in no server version yet.
[4 Apr 2011 19:03] Dmitry Lenev
Since was bug was reported against feature tree for WL#3561 "Transactional LOCK TABLES" and this worklog got cancelled I am closing this bug as "Won't fix".