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
