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:

  1. Find gaps (gaps in example would be 6-11, 15-19, 25-26,31-null)
  2. Compute random ranges that do not intersect existig ranges from table
  3. 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-17 and 15-19 would be inserted and that the ranges of my table intersect.

I know that one usually uses transactions with 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?

  • 1
    Neither transaction nor SELECT .. FOR UPDATE can help you... your task needs in locking the data which NOT EXISTS! How can you do something with the entity which not exists? You need in LOCK TABLE before select/calculate/update and UNLOCK after - because you MUSTprevent any data reading for parallel process. In any other case you can only detect that the inserted range which was calculated using old data is overlapped with the recent data. – Akina Nov 28 '18 at 15:23
  • He knows that @Akina this question on stackoverflow stackoverflow.com/questions/53522444/… is related to this on. – Raymond Nijland Nov 28 '18 at 15:25
  • @RaymondNijland The author "forget" to tell this... in that case the only way I see is to read documentation for the quering method which allows multi-query. Or, the best way, to call Stored procedure. – Akina Nov 28 '18 at 15:28
  • Well @Akina he could try a TEMPORARY table to make a "copy state" ... INSERT INTO temp SELECT FROM table2 it could give a locking read (shared locks) for the complete table2 table In thoery it could "work" – Raymond Nijland Nov 28 '18 at 15:33
  • @RaymondNijland It's like hammer nails with a microscope... unwise, but possible. – Akina Nov 28 '18 at 15:36

You need a transaction and it needs to grab the gap with FOR UPDATE. The problem is that you don't have an index to use to help with the task.

I suggest that having both a start and end in a row makes the task difficult. If, instead, there were only a start (or, equivalently, end), you could have an index and use SELECT...FOR UPDATE successfully.

Since you have gaps in the table, you would need a way to represent "real range" versus "unused range". That would involve another column (which you might already have).

Building such a structure, plus code to grab part of a gap, is found here , where I discuss (as an example) associating ranges of IP-addresses with their 'owner'. I use a special owner_id to indicate that a range is 'unassigned'.

The code was designed with speed in mind, since searching on start..end performs miserably at scale.

  • Thank you for sharing this design idea. However, I also want to give a user the ability to change the ranges. I think this would get in conflict with that design, because the algorithm may lock all rows with owner='0', but if another user changes a range he would insert new rows with owner=0. Its not a high scale project. There might be only 20 inserts per day. Maybe its best if simply lock the table while inserting new ranges in my table. – Adam Nov 28 '18 at 6:39
  • @Adam - It's more complex, but don't lock all owner=0 rows. Instead, locate the next and previous start values (hence complexity) and lock just that range. – Rick James Nov 28 '18 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.