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
