Hi, all. I'm doing the following on MySQL 4.0.18: LOCK TABLES b WRITE, a READ LOCAL;
SELECT a.id FROM a LEFT JOIN b ON b.id = a.id WHERE a.type = 'foo' AND a.action = 'T' AND b.status IS NULL; UPDATE b SET status = 'Q' WHERE id IN ([list of ids from SELECT]); UNLOCK TABLES; Then I loop through the ids, performing various actions and updating b with the result. There's obviously something wrong here, because now that our volume has ramped up, I'm seeing what looks to be a locking issue. After the UNLOCK, I do a sanity check, comparing the number of ids from the SELECT to the number of rows matched in the UPDATE. The SELECT sometimes returns more rows than the UPDATE, leading me to believe that I need a WRITE lock on a (I thought the WHERE clause view of "b.status" solved the issue, but obviously not). This is a logging application, and a sees a ton of INSERTs, so I obviously want to avoid WRITE locking it if possible. Is this The Right Way To Do It, or can I somehow combine the SELECT and the update into a single step, returning a list of ids? I can't use subselects, and I can't imagine the table replacement mentioned in the Cookbook (pp.647-648) is a good candidate in this scenario. ____________________________________________________________ Eamon Daly NextWave Media Group LLC Tel: 1 773 975-1115 Fax: 1 773 913-0970 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
