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_Priority | Guy Gordon | 12 Sep |