I have an
intervals table which stores a
start date and an
end date for variables
variable_id (four columns :
id, start, end, variable_id).
Clients compute new intervals for one variable and store them in that table. To preserve consistency (for a
variable_id I don't want intervals to overlap), clients first check (
select) what intervals we have and then
update accordingly (or do nothing).
If two clients do that simultaneously there is of course a risk that I insert overlapping intervals, a thing I don't want.
Well actually my question is simple: what should I do?
It seems that a
SELECT .. FOR UPDATE doesn't work, because this locks existing rows, and that's not enough for me, as rows may be inserted. I'd rather need the result of "
SELECT * FROM intervals WHERE variable_id = 1234" to be "safe" in some way.
I have implemented a solution that uses
SERIALIZABLE transactions. The result is that if two clients enter this select/update-delete-insert transaction, one will fail (
could not serialize access due to read/write dependencies among transactions or
could not serialize access due to concurrent update), in which case I just have to run again my select/update-delete-insert logic to try to insert more appropriate intervals.
The issue here is that the
serializable transaction seems to lock the entire table. But if a client wants to update variable 1234's intervals, while another one wants to update variables 4321's, there should be no problem, both transactions should be able to run happily.
I'm no SQL pro and can't even decide whether this is a simple classic sql question or a difficult issue.
Some remarks :
- PostgreSQL 9.1.1
- There's actually a lot more going in the transaction. Not only I update the intervals but I also insert corresponding variables' values in another table. Intervals in the
intervalstable are the intervals on which we have the variable's values. However I don't think this interferes with the issue described above.