Hi Karol!

Create a (reserved_dates) table with date field, and make that field unique. When a user inserts a date into the reservation table a trigger immadiate inserts this date to the reserved_dates table too. Then the unique key will stop secound insert instead of the first transaction is not commited.

András


From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sent: Wednesday, September 12, 2018 11:45 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Prevent overlaping dates in concurent environment


Hi,

is there a good way to prevent overlaping dates to be inserted to the table?

You know DATE_FROM, DATE_TO and you can have
2018-09-10 to 2018-09-20
and
2018-09-15 to 2018-09-22
they ovelap on 15,16,17,18,19 and 20


No my real sample but simple to understand is:

customer and meeting planning.
Currently we lock customer record and only one user can do update/insert/delete operation at a time.
But what if we need multiple users insert/update data in the same time?
How to prevent inserting overlaping dates then?

I know that we can write trigger and check overlap there but what if two users do insert and still not commited transaction?
User1 do insert, trigger is fired and check that no overlaping dates exists - and this transaction is not commited yet
User2 do insert, trigger is fired and check that no overlaping dates exists because user 1 still not commit transaction and user2 transaction do not see recrods from user1.
And at this point, if user1 commit and user2 commit, then overlaping dates can be inserted into database.

regards,
Karol Bieniaszewski



__________ Information from ESET Mail Security, version of virus signature database 18037 (20180912) __________

The message was checked by ESET Mail Security.
http://www.eset.com


[Non-text portions of this message have been removed]