Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2
-
None
Description
The following sequence of statements
DROP TABLE IF EXISTS t1, mrg_t1; |
CREATE TABLE mrg_t1 (a INT) ENGINE=MyISAM; |
CREATE TABLE t1 (a INT) ENGINE=MRG_MYISAM UNION(mrg_t1) INSERT_METHOD=LAST; |
INSERT INTO t1 VALUES (1),(2); |
LOCK TABLE t1 READ; |
in 10.2 takes following metdata locks:
+-----------+----------------------+---------------+---------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+----------------------+---------------+---------------------+--------------+------------+
|
| 31 | MDL_SHARED_READ | NULL | Table metadata lock | test | mrg_t1 |
|
| 31 | MDL_SHARED_READ_ONLY | NULL | Table metadata lock | test | t1 |
|
+-----------+----------------------+---------------+---------------------+--------------+------------+
|
while on 10.1 it is
+-----------+-----------------+---------------+---------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-----------------+---------------+---------------------+--------------+------------+
|
| 7 | MDL_SHARED_READ | NULL | Table metadata lock | test | mrg_t1 |
|
| 7 | MDL_SHARED_READ | NULL | Table metadata lock | test | t1 |
|
+-----------+-----------------+---------------+---------------------+--------------+------------+
|
It causes differences in certain concurrent scenarios. For example, in the scenario below (it's just a crude test to reproduce, don't put it in the MTR regression suite!)
It uses 3 connections working with a MERGE table, which has one underlying MyISAM table.
First connection locks the table with READ lock.
Second connection executes UPDATE for this MERGE table. Update waits for a lock, as expected.
Third connection executes SELECT from this MERGE table with a low lock_wait_timeout value.
Before 10.2, this SELECT waits until UPDATE is finished, so it fails with the timeout.
In 10.2, the SELECT is executed immediately (and thus produces a different result from what one would expect – it returns the old contents of the table, before UPDATE). In other words, it works as SELECT HIGH_PRIORITY.
It seems only to be so for MERGE tables (maybe some other engines that I didn't check, but not, for example, for MyISAM), so it's not particularly critical, but at least it would be good to find out whether it's an intentional change in behavior, and whether it is indeed limited to exotic engines.
--enable_connect_log
|
|
|
SET lock_wait_timeout = 2; |
|
|
--connect (con1,localhost,root,,)
|
|
|
CREATE TABLE mrg_t1 (a INT) ENGINE=MyISAM; |
CREATE TABLE t1 (a INT) ENGINE=MRG_MYISAM UNION(mrg_t1) INSERT_METHOD=LAST; |
INSERT INTO t1 VALUES (1),(2); |
|
|
LOCK TABLE t1 READ; |
|
|
--connect (con0,localhost,root,,)
|
--send
|
UPDATE t1 SET a = 3; |
|
|
--connection default
|
--sleep 1
|
--error 0, ER_LOCK_WAIT_TIMEOUT
|
SELECT a FROM t1; |
if ($mysql_errno) |
{
|
--echo ########################################## |
--echo # Received ER_LOCK_WAIT_TIMEOUT FOR SELECT |
--echo ########################################## |
}
|
|
|
--connection con1
|
UNLOCK TABLES;
|
--disconnect con1
|
|
|
--connection con0
|
--reap
|
--disconnect con0
|
|
|
--connection default
|
|
|
DROP TABLE t1; |
DROP TABLE mrg_t1; |