Description:
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.
