Well as usual this turned out to be fairly simple - once the reason was found.
Staring at the process list until this occured again revealed the following situation: 1. An RSS subscription to an infrequently used list results in a very heavy query (worst case almost 200 seconds), that has to do a full table scan of approximately 1 million rows + grouping and sorting (http://www.spurl.net/discover/hot.php will give you the idea). It obtains a readlock for the whole time. 2. During this time somebody tries to add to, or edit one of the two tables involved in the query mentioned in (1). It tries to obtain a write lock, but has to wait "it's turn", i.e. for (1) to complete. 3. As the write lock in (2) has higher priority than any subsequent query. All following queries have to wait for (1) AND (2) to finish before they can read from the table. Fairly basic stuff and explained well in the MySQL documentation here: http://dev.mysql.com/doc/mysql/en/Table_locking.html (compare the first bulleted list to points 1-3 above). There are several ways to work around this - in my case a more clever use of caching was the way to go. Other, and more SQL-esque methods are explained in the "Table locking" chapter, linked above. Despite the simple solution, I decided to post this anyway if it would be of help to anyone else having similar problems. Thanks to Victor and Ian for the tips! Best, -hjalmar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
