I have a table of ranges that should never intersect:
id | from | to -------------- 1 | 3 | 5 2 | 12 | 14 3 | 20 | 24 4 | 27 | 30
I have an algorithm that does the following:
- Find gaps (gaps in example would be 6-11, 15-19, 25-26,31-null)
- Compute random ranges that do not intersect existig ranges from table
- Insert these ranges (for example
insert into table (from,to) values (15,17)and
insert into table (from,to) values (31,40))
Now if this algorithm happens at the exact same time it would be possible that the range
15-19 would be inserted and that the ranges of my table intersect.
I know that one usually uses
select ... for update in such a situation. However, I am not really selecting any row that I want to update. Instead, I first compute gaps that do not exist in the database. The computation of the gaps is done with this SQL:
SELECT gap_starts, gap_ends FROM (SELECT (t1.to + 1) as gap_starts, (SELECT Min(t3.from) - 1 FROM table t3 WHERE t3.from > t1.to) as gap_ends FROM table t1 WHERE NOT EXISTS (SELECT t2.from FROM table t2 WHERE t2.from = t1.to +1 ) )
How can I use
transaction here to make sure that my inserted rows from step 3 are not intersecting?