Benjamin, ----- Original Message ----- From: "Benjamin Pflugmann" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, December 09, 2002 9:58 AM Subject: Re: Record Lock?
> Hi. > > On Sun 2002-12-08 at 14:50:35 +0200, [EMAIL PROTECTED] wrote: > [...] > > > SELECT * > > > FROM your_table > > > WHERE <some-condition-for-the-row-in-question> > > > FOR UPDATE > [...] > > I would like to clarify that InnoDB holds locks till the current transaction > > COMMIT or ROLLBACK. Thus, in the AUTOCOMMIT=1 mode is makes no sense to use > > FOR UPDATE. > > Thanks. I was not aware, that FOR UPDATE works this way. > > Then I have another question: Why would one use FOR UPDATE with > BEGIN/COMMIT? I thought, in the default transaction seperatation level > for InnoDB, REPEATED READ, I am guaranteed that nobody changed the > rows anyway. Correct? not correct. A plain SELECT in InnoDB is a 'consistent read'. It does not set any locks but reads a snapshot of the database. That is how under the default REPEATABLE READ isolation level you always get the same result set back, though other users have updated the table. You must use FOR UPDATE or LOCK IN SHARE MODE to lock the index records the SELECT touches. The exception is the isolation level SERIALIZABLE, where InnoDB internally adds LOCK IN SHARE MODE to each plain SELECT. ... > > Benjamin. > > -- > [EMAIL PROTECTED] sql query Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com 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
