Mike, ----- Original Message ----- From: "Mike Gohlke" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, December 07, 2002 6:44 PM Subject: Re: Innodb row locking question
> > > Benjamin Pflugmann wrote: > > >Hello. > > > >On Fri 2002-12-06 at 10:28:23 -0600, [EMAIL PROTECTED] wrote: > > ... > Because I'm not using transactions explicitly. 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. > 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? yes. When MySQL or any ordinary database executes a SELECT, record locks do not affect the row set it is going to pick. Thus, it will NOT jump over locked records, but rather will stop to wait. ... > >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 The name FOR UPDATE comes from Oracle. A better name would be LOCK IN EXCLUSIVE MODE. ... > Thanks for your reply and information. 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. Some notes which may help: 1. I would like to clarify that InnoDB holds locks till the current transaction COMMIT or ROLLBACK. Thus, in the AUTOCOMMIT=1 mode it makes no sense to use FOR UPDATE. When MySQL ends a connection, it rolls back the current transaction. Thus, locks are never kept after the connection ended. 2. In a MySQL UPDATE statement you can also use the LIMIT clause. 3. In an UPDATE or DELETE, InnoDB automatically sets exclusive locks on the index records it encounters in the search for the rows to be updated or deleted. > Mike... Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query --------------------------------------------------------------------- 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
