Hello. On Fri 2002-12-06 at 10:28:23 -0600, [EMAIL PROTECTED] wrote: > Heikki and all, > I've got a quick question which may be more general sql related but > since I'm using innodb tables specifically for the row locking. > > The following process description is specifically designed to prevent > duplicates. > > My current process: > select * from run where job_id = 111 and thread_id = 0 limit 10000 for > update; > > update run set thread_id = 999 where job_id = 111 and thread_id = 0 and > ( rec_id = x or rec_id = x1 ... ); // rec_id pulled via loop of > previous select
Why do you repeat "job_id = 111 and thread_id = 0"? If you are using a transaction seperation level of at least REPEATABLE READ (which is the default), InnoDb assures that you always see the same rows within one transaction. Regardless, FOR UPDATE locks the records, which assures the values did not change meanwhile. Btw, rec_id IN (x1, x2, x3) may provide better performance (OR clauses are still problematic with MySQL v3.23) > select main.* from run,main where run.job_id = 111 and run.thread_id = > 999 and run.rec_id = main.rec_id; // this is saved off > > delete from run where job_id = 111 and thread_id = 999; I am irritated. Above you select only the 10.000 first rows and process them, but at the end, you delete all, not only max 10.000? > ---- end ---- > As you can see, it's quite a few queries to prevent collisions. Right > now the update and delete take the longest for obvious reasons. > > What I'm wondering is if I can do the following: > > select main.* from run,main where run.job_id = 111 and run.thread_id = 0 > and run.rec_id = main.rec_id limit 10000 for update; > > delete from run where job_id = 111 and (rec_id = x or rec_id = x2 ...); Why not? FOR UPDATE is explained on http://www.mysql.com/doc/en/SELECT.html as "If you are using FOR UPDATE on a storage engine with page/row locks, the examined rows will be write locked." Although the name sounds otherweise, FOR UPDATE is not only suited for updates. IMHO, the write lock should work for the DELETE as for the UPDATE. Or in other words: it is either good enough for both of your variants or for neither. The only thing you should consider is the fact that the second variant will lock records from table "main", which the first did not. HTH, Benjamin. -- [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
