On Thursday 21 February 2002 03:06, Heikki Tuuri wrote: Oh, Duh. The bigger Oops was mine. Doesn't help much to have a transaction if you don't ask for a lock! ;o). Thx.
> Oops, > > the syntax is > > SELECT ... FROM ... WHERE .. LOCK IN SHARE MODE; > > Heikki > > -----Original Message----- > From: Heikki Tuuri <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] <[EMAIL PROTECTED]> > Date: Thursday, February 21, 2002 9:27 AM > Subject: Re: Fake interval data type > > >Hi! > > > >>Its kind of a subtle point, but innodb's locking not only locks rows, it > >>locks the ABSENCE of rows. If I understand correctly this means that > >>something like: > >> > >>BEGIN WORK > >>SELECT COUNT(*) FROM table WHERE starttime>? AND endtime<? > >>(check the count) > >>INSERT INTO table .... (if count is zero) > >>COMMIT > >> > >>SHOULD do the trick. The transaction should block anything else that > >> tries > > > >to > > > >>do an insert into the same range before you do your commit. > > > >I must add you have to use a LOCKING read in the SELECT. Either > > > >SELECT ... FROM ... WHERE .. FOR UPDATE; > > > >which sets exclusive locks, or > > > >SELECT ... FROM ... WHERE .. FOR LOCK IN SHARE MODE; > > > >which sets shared locks on the rows (to be precise, index records) it > >encounters. > > > >Without these additional clauses the default SELECT mode of InnoDB is the > >consistent non-locking read of Oracle. > > > >Locking reads in InnoDB also lock the absence of rows, as Tod states. > > > >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 > > --------------------------------------------------------------------- > 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 --------------------------------------------------------------------- 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
