You need a transaction and you have to fill the gap with
FOR UPDATE. The problem is that you do not have index to use to help you in the task.
I suggest having both a
end in a row makes the task difficult. If, on the contrary, there was only one
beginning (or equivalent,
end), you could have an index and use
SELECT ... FOR UPDATE with success.
As you have gaps in the table, you would need a way to represent the "real range" versus the "unused range". This would imply another column (that you might already have).
We find such a structure, more code to recover some of the space right here , where I discuss (as an example) to associate ranges of IP addresses with their "owner". I'm using a special owner_id to indicate that a beach is unassigned.
The code was designed for the sake of speed, because the search on start / end behaves poorly to scale.