MySQL Bugs: #46663: Table locked in EXCLUSIVE MODE is still accessible from other connections

Bug #46663	Table locked in EXCLUSIVE MODE is still accessible from other connections
Submitted:	12 Aug 4:26	Modified:	14 Oct 18:44
Reporter:	Elena Stepanova
Status:	Verified
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	5.4.4-alpha	OS:	Any
Assigned to:	Calvin Sun	Target Version:
Triage:	Triaged: D2 (Serious)

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;

Since transactional locking is entirely handled within the storage engine, I suggest to
assign it to InnoDB.

I agree with Ingo's assessment. We have no mechanism to prevent SELECT from going through
in the server. Should be resolved within InnoDB.

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.

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?

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?

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.

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