Re: Fake interval data type
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
Re: Fake interval data type
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. - 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
Re: Fake interval data type
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
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
Re: Fake interval data type
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
