Description:
This has been detected during analyze of Bug#38133 (Myisamlog test fails on Windows). The third patch (http://lists.mysql.com/commits/58606) contains a possible fix for the problem, but it was rejected for Bug#38133. For more information, you may study the review thread attached at that link.
IMHO the problem is that my_lock() behaves differently on Windows than on UNIX-like platforms. On Windows, a lock on a file does not necessarily replace a former lock by the same process. Namely it is possible to hold an exclusive lock (write lock) in parallel to a shared lock (read lock). On UNIX-like platforms a new lock on the same portion of a file replaces a former lock on the same portion of the file.
MyISAM code, in mi_lock_database(), clearly expects the behavior of UNIX-like platforms. When it changes the lock type on the file, say from write lock to read lock, it just requests a read lock on the file, expecting that the write lock is replaced and goes away.
How to repeat:
You need a -master.opt file with --external-locking.
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1);
# This takes a write lock through the first instance of the table and a
# read lock through the second instance. The write lock is propagated
# to my_lock(), but the read lock is skipped, because a stonger lock
# exists. On unlock, the write lock is removed first. On file level, the
# write lock is converted to a read lock, as the second instance is not
# unlocked yet. On Windows, this conversion turns into an additional
# lock, because a write lock and a read lock can co-exist on a file.
# Unlocking the second instance leads to the latest lock to be removed
# from the file. But the former lock, the write lock, still exists on
# Windows.
INSERT INTO t1 SELECT * FROM t1;
# This blocks on Windows, because it tries a write lock on the file,
# that is write locked already. It blocks, even though it's the same
# process taking the lock.
INSERT INTO t1 VALUES (2);
Suggested fix:
I suggest to stick with the valuable tradition of mysys to compensate differences of platforms. This could be done in my_lock() by removing an old lock with (void) UnlockFileEx() before taking a new one with LockFileEx(). See the link to my proposed patch for Bug#38133.
Perhaps Windows has a flag or a different set of functions to accomplish the same. After all, unlocking before (re-)locking could leave a short time frame with an unlocked file when the lock type changes, which could be a problem for co-working processes. But if there is no other way to solve it with Windows features, it is better to live with the race condition than to block after INSERT...SELECT.
Please note: If you fix the bug in my_lock(), or otherwise in mi_lock_database(), you may remove the code from mi_examine_log(), which tries to accomplish the same on the client side only. See later patches for Bug#38133.
