On Wednesday 20 February 2002 05:51, Sasa Babic wrote: > On Wed, Feb 20, 2002 at 11:38:31AM +0100, [EMAIL PROTECTED] wrote: > > sql,query > > The above is for antispam filter. > > > > I have a need for an interval data type. It would contain start and end > of a certain time event. Since it is not possible to have two different > events at the same place and at the same time, I want to ensure that > there cannot exist two entries for which time interval would overlap > with another entry's interval. > > I've done a simple routine in PHP which cheks if there is an overlaping > entry in database and it inserts new entry only if it's 'safe'. > Obviously, there is a problem if there are two entries, let's call them > A and B, and A entry is made after B has done the checking but before B > has made a new record. > > Any sugestions? Even sugestions for another free open source db would > do. Sorry for my broken english. Thanks.
Transactions would be the answer. Use an innodb table and wrap your check/insert code in a transaction. The select which does the check will lock the table in such a way that any other attempt to insert within the range you tested for should end up being locked. 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. > > > --------------------------------------------------------------------- > 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
