Bug #26241 Blackhole tables don't honor table locks
Submitted: 9 Feb 2007 22:07 Modified: 27 May 2007 19:17
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.1.15 OS:Linux (Linux, Mac OS X)
Assigned to: Antony Curtis CPU Architecture:Any

[9 Feb 2007 22:07] Giuseppe Maxia 
Description:
A blackhole table is not affected by table lock statements.

An exception to the above behavior is when a blackhole table has a trigger that accesses another table. In that case, the lock is honored. If the trigger does not access any table, the lock has no effect.

This bug can lead to data loss/alteration in a replicated environment where a BlackHole table is in the master and a normal storage table in in the slave.
 
Example scenario: 

# session 1 (master)
--------------------

CREATE TABLE t1 (i int) engine = blackhole;
CREATE TABLE t2 (i int) engine = blackhole;

# session 2 (slave)
# ----------------
ALTER TABLE t1 engine = myisam;
ALTER TABLE t2 engine = myisam;

# session 1 (master)
--------------------
LOCK TABLE t1 WRITE, t2 WRITE;
insert into t1 values (1);

# session 3 (master)
--------------------
insert into t1 values (2);

# with a normal storage table, this command would hang
# until the tables are unlocked
# with a blackhole table, it just goes through

# session 1 (master)
--------------------
insert into t2 select * from t1;
unlock tables;

# session 2 (slave)
--------------------
select count(*) from t2;

# returns 2, instead of 1

How to repeat:
see attached test case files

Suggested fix:
make the blackhole tables honor the locks.
Possible workaround to enforce locking: add a trigger to the blackhole table
[9 Feb 2007 22:08] Giuseppe Maxia 
test case for bug #26241

Attachment: rpl_bh_lock.tar.gz (application/gzip, text), 1.11 KiB.
[23 Apr 2007 15:15] gehaijiang gehaijiang 
please resolve this problem.  thanks you!
[23 Apr 2007 15:51] Antony Curtis 
Problem is caused by BLACKHOLE tables not sharing a per-table THR_LOCK structure. Solution is to implement shared structure as in other storage engines.
[28 Apr 2007 7:32] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25659

ChangeSet@1.2489, 2007-04-28 00:31:44-07:00, acurtis@xiphis.org +5 -0
  Bug#26241
    "Blackhole tables don't honor table locks"
    Implement neccessary shared lock structure for table locks.
    Imported test case created by Giuseppe Maxia
[1 May 2007 18:27] Brian Aker 
The store_lock() implementation in the patch is for table level locks, please re-implement for row level locks (see code in Archive or Innodb as examples).
[2 May 2007 16:57] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25920

ChangeSet@1.2489, 2007-05-02 09:57:16-07:00, acurtis@xiphis.org +5 -0
  Bug#26241
    "Blackhole tables don't honor table locks"
    Implement neccessary shared lock structure for table locks.
    Imported test case created by Giuseppe Maxia
[9 May 2007 0:16] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26343

ChangeSet@1.2512, 2007-05-08 17:16:34-07:00, acurtis@xiphis.org +2 -0
  Bug#26241
    "Blackhole tables don't honor table locks"
    Implement neccessary shared lock structure for table locks.
    Imported test case created by Giuseppe Maxia
[9 May 2007 16:31] Antony Curtis 
Pushed to 5.1-engines
[24 May 2007 7:05] Bugs System 
Pushed into 5.1.19-beta
[27 May 2007 19:17] Paul DuBois 
Noted in 5.1.19 changelog.

The BLACKHOLE storage engine now supports LOCK TABLES and UNLOCK
TABLES.