List:MySQL on Win32« Previous MessageNext Message »
From:Guy Gordon Date:September 11 2009 11:01pm
Subject:Reads locked along with Insert Low_Priority
View as plain text  
I have a puzzle.  I have a large table with non-changing data, and a smaller
table with calculated data.  Call them ItemsData, and ItemsCalc.  Both tables
are MyIsam, with primary key ItemID.  

Every record in ItemsData *should* have a corresponding ItemsCalc record with
the same ItemID.  I check this with

Select ItemID from ItemsData
Left Join ItemsCalc on CItemID=ItemID 
where CItemID is Null

This is a slow query, but it should only read-lock both tables.  Show
ProcessList;

Status        	 SQL Query
-----------------	--------------------
Sending data	Select ItemID from ItemsData Left Join ItemsCalc...
Sending data	Select * from Items where ItemID=...

The first line is the slow query given above.  The second line i a process
reading from ItemsData (to create ItemsCacl). 

Now, lets run the program that adds new records to ItemsData.  It does so using
Low_Priority, so reads should jump ahead of it in the queue.

Status        	 SQL Query
-----------------	--------------------
Sending data	Select ItemID from ItemsData Left Join ItemsCalc...
Locked		Select * from ItemsData where ItemID=...
Locked		Insert Low_Priority into ItemsData (...

Why is the second process now locked?  

From the manual: 
"If you use the LOW_PRIORITY keyword, execution of the INSERT is delayed until
no other clients are reading from the table. This includes other clients that
began reading while existing clients are reading, and while the INSERT
LOW_PRIORITY statement is waiting. "


Thread
Reads locked along with Insert Low_PriorityGuy Gordon12 Sep