Bug #27234 INSERT into Merge Table implicitly locks all underlying tables

• Submitted: 17 Mar 2007 4:48
• Modified: 21 Mar 2007 8:56
• Reporter: Venu Anuganti
• Status: Verified
• Category: MySQL Server: Merge storage engine
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: bfsm_2007_04_05, lock, merge, read, write

[17 Mar 2007 4:48] Venu Anuganti

Description:
Looks like Merge table locks some tables; when they are not supposed to be. 

How to repeat:
Repro:

Session 1:
----------
1. Create two simple MyISAM tables like this

use test;
drop table if exists t1;
drop table if exists t2;

create table t1(c1 int) type=MyISAM;
create table t2(c1 int) type=MyISAM;

2. Create a merge table on above two tables with insert_method=last

create table t(a int) type=merge union(t1,t2) INSERT_METHOD=LAST;

3. Populate the table t1 with lets say 1million rows 

4. Type the following update on session-1 and make sure session-2 is ready by this time; so that you can issue insert at the same time

UPDATE T1 SET C1=999;

Session 2:
----------
1. Open a connection to server
2. Type the following insert statement

use test;
INSERT INTO t VALUES(888);

Now; execute the Update first and then immediately followed by insert when update is executing; you can see that the INSERT statement is blocked till update is completed.

Suggested fix:
The update is doing directly on t1; so no need to lock any tables related merge table 't'. When insert is done on merge table 't'; as INSERT_METHOD=LAST; it should insert into table 't2'; so no need to make INSERT to block until update is completed which is updating 't1'.

So, acquring the right lock is the key.

[17 Mar 2007 5:04] Venu Anuganti

script to populate table data for update

Attachment: merge.sql (application/octet-stream, text), 187.15 KiB.

[20 Mar 2007 17:57] Todd Farmer

Verified on 4.1.22, 5.0.37 and 5.1.16-beta.

Used following script:

- In first session --
CREATE DATABASE IF NOT EXISTS bug27234;
USE bug27234;
DROP TABLE IF EXISTS m;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;

CREATE TABLE t1 (c1 INT) ENGINE = MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE = MyISAM;
CREATE TABLE m (c1 INT) ENGINE = MERGE UNION(t1, t2) INSERT_METHOD = LAST;

LOCK TABLE t1 READ;

-- In second session --
USE bug27234;
INSERT INTO m VALUES (1);  # Will hang waiting for lock on t1, even though data is inserted to t2

-- Back in first session --
UNLOCK TABLES;  # Allows insert in second session to complete
SELECT * FROM t2;  # Verifies that data was added to t2 and not t1