Bug #1460 inserts to merge tables lock all underlying tables
Submitted: 1 Oct 2003 16:23 Modified: 2 Oct 2003 6:12
Reporter: [ name withheld ] Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:4.0.14 OS:FreeBSD (FreeBSD)
Assigned to: CPU Architecture:Any

[1 Oct 2003 16:23] [ name withheld ]
It appears that when doing updates to a merge table it obtains a lock on all the underlying tables instead of just the table to be updated.

How to repeat:
I have two merge table views
1. log merge on:
200301, 200302, 200303, 200304, 200305, 200306, 200307, 200308, 200309, 200310
2. log_old merge on:
200301, 200302, 200303, 200304, 200305, 200306, 200307, 200308, 200309

Both created with INSERT_METHOD=LAST when doing large batch processing using log_old ( selects returning 300K rows etc ) and doing inserts to log the inserts to log are blocked waiting State: Locked while a large select completes.
I tried changing log to just a merge on 200310 and this prevented the blocking.
The only conclusion I can make is that when an insert is attempted on log its actually obtaining a lock on each of the underlying tables and hence blocking on the long running read being done via log_old.

Suggested fix:
If my conclusion is correct changing inserts to only lock the underlying insert table would solve the problem.
N.B. this will not be the case for updates only inserts.
Another enhancement would be to define data partitions within merge tables which could be used to provide significant performance increases for both updates and selects.
[2 Oct 2003 6:12] Alexander Keremidarski
We don't have plans to implement this MERGE feature in short terms, but it can be considered for future releases. 

Probably 5.0 or above.