Hi!

Create a lock table, and run all inserts on wait transactions:

New table with one record:
CREATE TABLE LOCK_TABLE (
TABLE_NAME VARCHAR(31) NOT NULL
);

INSERT INTO LOCK_TABLE (TABLE_NAME) VALUES ('MEETINGS_UQ');

ALTER TABLE LOCK_TABLE ADD CONSTRAINT LOCK_TABLE_PK PRIMARY KEY (TABLE_NAME);

And the changed trigger:
CREATE OR ALTER TRIGGER MEETINGS_UQ_BI FOR MEETINGS_UQ
ACTIVE BEFORE INSERT POSITION 0
as
declare variable lock_table__table_name type of column lock_table.table_name;
begin
lock_table__table_name = null;
select lt.table_name
from lock_table lt
where lt.table_name = 'MEETINGS_UQ'
with lock
into :lock_table__table_name;
if (lock_table__table_name is null) then
begin
exception exception_ex 'Lock method failed!';
end

-- Paste here the very sophisticated business logic
if (exists (select 1 from meetings_uq muq where muq.room = new.room)) then
begin
exception exception_ex 'Room is full!';
end
end

András

From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Sent: Wednesday, September 12, 2018 11:34 PM
To: firebird-support@yahoogroups.com
Subject: ODP: [firebird-support] Prevent overlaping dates in concurentenvironment



Hi,

quite interesting solution wihich minimize possibility of overlapping but do not eliminate it.
Consider first sample about two transactions
What is the difference between normal two transactions and two autonomous transactions?

Autonomous transaction 1 start
Autonomus transaction 2 start
Autonomous transaction 1 insert record
Autonomous transaction 2 insert record
Autonomous transaction 1 commit
Autonomous transaction 2 commit

I am wrong?

Pozdrawiam,
Karol Bieniaszewski

Od: Omacht András aomacht@... [firebird-support]
Wysłano: środa, 12 września 2018 21:23
Do: firebird-support@yahoogroups.com
Temat: RE: [firebird-support] Prevent overlaping dates in concurentenvironment


Hi All!

Let’s see an another solution.

!!! This won’t work if you are using snapshot transactions. !!!
(If you need snapshot you have to solve the rollback problem with an another solution.)

CREATE EXCEPTION EXCEPTION_EX 'Somewhere someting went terrible wrong…';

Table for checking the collisions:

CREATE TABLE MEETINGS_UQ (
ROOM INTEGER NOT NULL,
DATE_FROM DATE NOT NULL,
DATE_TO DATE NOT NULL,
TR_NO INTEGER NOT NULL
);

ALTER TABLE MEETINGS_UQ ADD CONSTRAINT MEETINGS_UQ_PK PRIMARY KEY (ROOM, DATE_FROM, DATE_TO);

CREATE DESCENDING INDEX MEETINGS_UQ_I1 ON MEETINGS_UQ (TR_NO);

CREATE OR ALTER TRIGGER MEETINGS_UQ_BI FOR MEETINGS_UQ
ACTIVE BEFORE INSERT POSITION 0
as
begin
-- Paste here the very sophisticated business logic
if (exists (select 1 from meetings_uq muq where muq.room = new.room)) then
begin
exception exception_ex 'Room is full!';
end
end

Table for inserted data:

CREATE TABLE MEETINGS (
ROOM INTEGER NOT NULL,
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 tr_no integer;
begin
tr_no = current_transaction;
in autonomous transaction do
begin
insert into MEETINGS_UQ (ROOM, DATE_FROM, DATE_TO, TR_NO)
values (new.room, new.date_from, new.date_to, :tr_no);
end
end

In case of rollback:

CREATE OR ALTER TRIGGER DATABASE_ON_TR_ROLLBACK
ACTIVE ON TRANSACTION ROLLBACK POSITION 1
as
declare variable tr_no integer;
begin
tr_no = current_transaction;
in autonomous transaction do
begin
delete from meetings_uq muq where muq.tr_no = :tr_no;
end
end

Test1:
isql1:
insert into meetings(room, date_from, date_to) values (1, 'TODAY', 'TODAY');

isql2:
insert into meetings(room, date_from, date_to) values (2, 'TODAY', 'TODAY');

isql3:
insert into meetings(room, date_from, date_to) values (1, 'TODAY', 'TODAY');

GL_EXCEPTION_EX.
Room is full!.
At trigger 'MEETINGS_UQ_BI' line: 8, col: 7
At trigger 'MEETINGS_BI' line: 9, col: 7.

Test2:
isql1:
insert into meetings(room, date_from, date_to) values (3, 'TODAY', 'TODAY');
rollback;

isql2:
insert into meetings(room, date_from, date_to) values (3, 'TODAY', 'TODAY');
commit;

András

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

Hi,

Solution is good only for dates and only in small range but considering date with time or numbers you see that solution is realy limited.
As Tomasz say will be good to see „systematic solution”

And to check overlaping your sample is quite ok but is ineficient
|---R1---|
|-----------R2---------|

|---R1---|
|-----------R2---------|

|---R1---|
|-----------R2---------|

|--------R1-------|
|--R2--|

Better is check when dates do not overlap and do negation – only 2 possibilities 😉
NOT (DATE_TO1<DATE_FROM2 OR DATE_TO2<DATE_FROM1)

DATE_FROM1|--------------------|DATE_TO1
…………………………DATE_FROM2|--------------------|DATE_TO2

…………………………DATE_FROM1|--------------------|DATE_TO1
DATE_FROM2|--------------------|DATE_TO2

Pozdrawiam,
Karol Bieniaszewski

Od: 'Louis van Alphen' louis@... [firebird-support]
Wysłano: środa, 12 września 2018 15:18
Do: firebird-support@yahoogroups.com
Temat: RE: [firebird-support] Prevent overlaping dates in concurent environment

The practicality of entering each date in the range into a table depends on the application. For short date ranges this should be fine, but very wide ranges will have a bit of a performance impact on inserts.

Let’s say you want to book a resource for a date range. Then you could create tables like this:

create table RESOURCE

(

ID integer,

NAME varchar(64)

);

alter table RESOURCE add constraint RESOURCE_PK primary key (ID) using index RESOURCE_PK_IDX;

create table RESOURCE_BOOKING

(

ID integer,

RESOURCE_ID integer,

FROM_DAT DATE,

TO_DAT DATE,

);

alter table RESOURCE_BOOKING add constraint RESOURCE_BOOKING_PK primary key (ID) using index RESOURCE_BOOKING_PK_IDX;

create index RESOURCE_BOOKING_RESOURCE_IDX on RESOURCE_BOOKING(RESOURCE_ID);

create table RESOURCE_BOOKING_DETAIL

(

BOOKING_ID integer,

RESOURCE_ID integer,

BOOKED_DAT DATE

);

alter table RESOURCE_BOOKING_DETAIL add constraint RESOURCE_BOOKING_DETAIL_UNQ unique (RESOURCE_ID,BOOKED_DAT) using index RESOURCE_BOOKING_DETAIL_UNQ_IDX;

create index RESOURCE_BOOKING_DETAIL_IDX on RESOURCE_BOOKING_DETAIL(BOOKING_ID);

The RESOURCE_BOOKING_DETAIL_UNQ unique constraint will ensure that a RESOURCE cannot be booked more than once for the same day. The associated index is also useful for selecting the days a RESOURCE is booked for.

The RESOURCE_BOOKING_DETAIL_IDX is useful for when you want to delete a BOOKING by its ID.

Two ranges may overlap in 4 ways and you have to check all 4 scenarios to detect an overlap. There is no way to utilise standard DB constraints to validate this.

I have written some PSQL SP to validate the 4 cases, but I normally do this in my domain layer. This means there is no guarantee that overlap does not exist in a concurrent environment.

The range overlap cases are as follows (not sure if the email spacing will be preserved):

|---R1---|

|-----------R2---------|

|---R1---|

|-----------R2---------|

|---R1---|

|-----------R2---------|

|--------R1-------|

|--R2--|

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.....com]
Sent: Wednesday, 12 September, 2018 14:41
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Prevent overlaping dates in concurent environment

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]

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

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

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

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



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