Hi Tomasz!

Tested on 2.5.8, dialect 1:

CREATE TABLE RESERVED_DATE (
RES_DATE DATE NOT NULL
);
ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY (RES_DATE);

CREATE TABLE MEETINGS (
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL
);

CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
ACTIVE BEFORE INSERT POSITION 0
as
declare variable curr_date date;
begin
curr_date = new.date_from;
while (curr_date <= new.date_to) do
begin
insert into reserved_date (res_date) values (:curr_date);
curr_date = dateadd(1 day to curr_date);
end
end

run on first transaction:

insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.01', '2018.09.10')

run parallel on secound transaction:
insert into MEETINGS (DATE_FROM, DATE_TO)
values ('2018.09.08', '2018.09.15')

violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table "RESERVED_DATE".
Problematic key value is ("RES_DATE" = '8-SEP-2018').
At trigger 'MEETINGS_BI' line: 9, col: 7.

András

From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sent: Wednesday, September 12, 2018 1:59 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Prevent overlaping dates in concurent environment



On 12.09.2018 at 13:29, Omacht András aomacht@... [firebird-support]
wrote:

> 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.

This won't work. All dates may be different and the intervals may still
overlap.
Karol, that's an interesting issue and I'm really curious if there's a
clever solution.
So far I've checked the check (pun intended):

create table TST1 (
d1 timestamp,
d2 timestamp,

constraint no_overlap check (
not exists (
select * from TST1 t1
where exists (
select * from TST1 t2
where t1.d1 between t2.d1 and t2.d2
or t1.d2 between t2.d1 and t2.d2
)
)
)
);

insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A
insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B
-- commit A (no errors)
-- commit B (no errors)

and it doesn't work. You can still insert two overlapping pairs and both
transactions get committed without errors, resulting in overlapping
intervals being inserted. So, unless you change the transaction
isolation level (I always use read committed), I don't have more ideas
at the moment.

have a good one
Tomasz

--
__--==============================--__
__--== Tomasz Tyrakowski ==--__
__--== SOL-SYSTEM ==--__
__--== http://www.sol-system.pl ==--__
__--==============================--__



__________ 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]