Hello. On Sat 2002-12-07 at 10:36:00 -0600, [EMAIL PROTECTED] wrote: [...] > >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. > > Because I'm not using transactions explicitly.
Ah. Okay. Is there a special reason for this? > And the whole point of setting the thread id is because if thread 1 > select's 5k records for update I'm not sure that thread 2 won't > select the same 5k records. As I said above, FOR UPDATE already garantues exclusive use. The second thread would block on the write lock. > I've read the docs forwards and backwards and some things just > aren't clear to me. If a table has 10k records and thread 1 selects > the first 5k in update mode, when thread 2 executes a microsecond > after thread 1 will it get the same 5k records? No, the second one will block when it tries to select any record which is already selected (and tehrefore locked) by the first thread. In the worst case, both threads get some rows locked. Since both selects want all the rows and some are already locked by the other thread, neither can have all. In this case one of both queries will fail and automatically be rolled back (and unlock the rows). See http://www.mysql.com/doc/en/InnoDB_Deadlock_detection.html. Btw, you already have this possibility with your current assumptions (when the second thread starts locking records before the first one did its update). But it is not a real problem, because both queries would be the same in your case, they will - even though that is not garantueed - pratically get the same execution path and therefore the second thread will block on the very first record, and therefore be unable to "steal" any records from thread one. > Will they run concurrently and pick records within the whole 10k set > so that at the end all 10k are selected but each 5k subset is not > contiguous? No. The query asked for all records (ignore the LIMIT for a moment). It would be a bug to not return all of them in each query. MySQL knows nothing about the fact that both your threads want to do the same thing with the records. > Or will thread 1 get the first 5k records and thread 2 > will get the second 5k records? Same as above. You asked for all. That some of them maybe locked is no excuse for not returning all of them. > >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." > > But not read locked. If 2 select for update's are executed against the > same record set, will 1 succeed and 1 fail? Ah, that's the misconception. A write lock is stronger than a read lock, it implies the garantuees of a read lock. In other words: a read lock forbids changes (by others) but allows other readers and therefore is also called "shared lock". A write lock forbids anything (by others), and therefore is also "exclusive lock". These properties are explained quite clearly for the LOCK TABLES command: http://www.mysql.com/doc/en/LOCK_TABLES.html And the fact that FOR UPDATE indeed sets exclusive locks is mentioned here http://www.mysql.com/doc/en/InnoDB_locking_reads.html > Thanks for your reply and information. You are welcome. > The problem is that I'm conservative as hell when it comes to coding > and must be absolutely sure things will work the way they should. > Otherwise, I'll choose the inefficient route just because I know > it's safe. Yeah, rather safe than sorry. I am with you. :-) 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
