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.
