Mysql – Using `transactions` without `select … for update`

MySQLtransaction

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?

Best Answer

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.

Related Question