Concurrency with Select-conditional Insert/Update/Delete - PostgreSQL

Question

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 insert, delete and/or 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 intervals table are the intervals on which we have the variable's values. However I don't think this interferes with the issue described above.

Answer 1

Regarding the SERIALIZABLE transactions, be sure to pay attention to the "for optimal performance" tips near the bottom of this section: http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html#XACT-SERIALIZABLE These can reduce your rollback rates quite significantly.

That said, and not to discourage use of the SERIALIZABLE transaction isolation level, what you describe sounds like exactly the sort of problem that EXCLUSION CONSTRAINTs were designed to solve. Where there is a declarative constraint to enforce a business rule, that is almost always better than using imperative coding (in triggers or application code).

http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION

http://www.postgresql.org/docs/9.1/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

To use this feature with dates you would currently need to use the TEMPORAL module:

http://pgfoundry.org/projects/temporal/

You will probably want to migrate TEMPORAL columns to a RANGE when you move to PostgreSQL version 9.2 (expected to be released this summer). The 9.2 RANGE feature, the TEMPORAL data type, and the EXCLUSION CONSTRAINT feature were all the work Jeff Davis to address problems like that posed in the question. They were designed to work together.

Full disclosure: The SERIALIZABLE transaction behavior in 9.1 was developed by myself and Dan R.K. Ports of MIT.