MySQL Bugs: #10602: LOAD INDEX INTO CACHE deadlocks

Bug #10602	LOAD INDEX INTO CACHE deadlocks
Submitted:	12 May 2005 16:02	Modified:	3 Jun 2005 23:37
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1.12	OS:
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
LOAD INDEX INTO CACHE deadlocks server access to the target table if other connections are reading from the table at the time.  Changing mysql_preload_keys() to acquire TL_WRITE, or simply wrapping LOAD INDEX INTO CACHE in LOCK/FLUSH statements, appears to circumvent the problem.

This is possibly related to bug #10224.

How to repeat:
CREATE TABLE t1 ( a VARCHAR(100), FULLTEXT (a) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES ( 'TEST DATA'), ('GIBBERISH'),('NONSENSE'),('RANDOM FLUFF');

shell 1:
while ((1)); do mysql test -e "LOAD INDEX INTO CACHE t1"; done

shell 2:
while ((1)); do mysql test -e "SELECT * FROM t1 WHERE MATCH (a) AGAINST ('FLUFF')"; done

This locks up eventually.  I have some other c++ cases which lock up more quickly but those should not be necessary.

Does not appear to lock with:

shell 1:
while ((1)); do mysql test -e "LOCK TABLE t1 WRITE; FLUSH TABLE t1; LOAD INDEX INTO CACHE t1; UNLOCK TABLES"; done

shell 2:
while ((1)); do mysql test -e "SELECT * FROM t1 WHERE MATCH (a) AGAINST ('FLUFF')"; done

Suggested fix:
n/a

Bug database automatic reference of changesets seems to be broken again.
bk commit - 4.1 tree (ingo:1.2283) BUG#10602
Date: Fri, 27 May 2005 16:02:32 +0200

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/internals/25326

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/internals/25445

Pushed to 4.1.13 and 5.0.7.

Noted in 4.1.13, 5.0.7 changelogs.