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

concurrencypostgresql

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.

Best Answer

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.

Related Solutions

SQL Server Concurrency – Managing SELECT-UPDATE Pattern

Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk.

Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when attempting to acquire the locks needed for the update (object IX lock or index RangeS-U respectively) which will lead to deadlock.

The use of an explicit UPDLOCK hint instead will serialize the reads thus avoiding the deadlock risk.

SQL Server – Concurrent Insert/Update and Storing Data

The updated scripts in your question are equally safe.

Expanding on the answer to your other question, you could use the natural key in both WHERE clauses and assign the @OrderLineID variable in the UPDATE statement. Perform the conditional insert first if that is the most common case, otherwise perform the update first. This method will eliminate the SELECT query.

BEGIN TRANSACTION;

DECLARE @OrderLineId int;
INSERT INTO Orderline (...)
    SELECT ...
    WHERE NOT EXISTS (
        SELECT * 
        FROM Orderline WITH (UPDLOCK, SERIALIZABLE)
        WHERE LastName = @LastName
            AND FirstName = @FirstName
            AND WebLoginDate = @WebLoginDate
            AND ProductName = @ProductName
    );
IF @@ROWCOUNT > 0
BEGIN
    SET @OrderLineId = SCOPE_IDENTITY();
END
ELSE
BEGIN
    UPDATE Orderline 
    SET
         ...
         , @OrderLineId = OrderLineId
    WHERE LastName = @LastName
        AND FirstName = @FirstName
        AND WebLoginDate = @WebLoginDate
        AND ProductName = @ProductName
        AND LastModified<@CreatedTime;
END;

COMMIT TRANSACTION;

Be aware that when you mix using the natural key and surrogate key to locate rows, deadlock likelihood is increased.

Best Answer

Related Solutions

SQL Server Concurrency – Managing SELECT-UPDATE Pattern

SQL Server – Concurrent Insert/Update and Storing Data

Related Question