PostgreSQL concurrency control and Serializable transactions

concurrencypostgresqltransaction

Im building an application that needs to do some concurrent read/write operations on specific tables. Even after reading the official documentation (and many, many articles), the trouble persists, no one talk about the problem I have (and I think it's VERY common)

My DB:

Table accounts with a boolean column called disabled (the other columns can be ignored)
Table request, that represents some relation between 2 accounts. An account can "request" some action to another account.

The issue:

Accounts cannot request actions to disabled accounts. It is a business rule, so I don't want to put any logic related to this in the database (using triggers/constraints). The database should only guarantee things related with the data (FK's, unique indexes, etc)

Since the database will not handle this business rule, my application will. To do it, every time a new request is issued, the following steps happen:

Open a transaction
Read target account disabled value
// Concurrency issues happens here
// What happens if the target account update it's disabled value here?
If the target account is not disabled, create a new "request" row

Do you see the concurrency issue?

At first, I tried to use Explicit Locking, but it is too complexity to handle (my real case is a little bit more complex, but the above example is sufficient). It works, but its not maintainable.

After, I tried to use Serializable transactions, but the problem above persists, although everyone saying "When using Serializable transactions you don't have to think about concurrency"

What do I have to do to ensure this consistency? The explicit locking strategy is the only way?

Thanks!

Best Answer

You don't get a serializable violation, because there is not one.

If the actual order is:

T1 begin serializable
T2 begin serializable
T1 see that account is not disabled
T2 disable account
T1 insert request
T2 commit
T1 commit

The outcome of the above is compatible with the serial order of:

T1 begin serializable
T1 see that account is not disabled
T1 insert request
T1 commit
T2 begin serializable
T2 disable account
T2 commit

Because the outcome is compatible with some serial execution, it is not a violation. If the serial execution it is compatible with is not the one you want, that is an error in your expectations, not in the execution.

If T2 were both to disable the account, and delete all pending requests for that account, in one serializable transaction, then that would be a violation. One of the two would get served an ERROR when it tried to commit. But if T2 only disables the account without deleting pending request against it, your business logic allows that outcome, so that is not a violation.

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 – How to Exclusively Access a Table and Prevent Other Queries

You will be able to avoid a lot of your race conditions by performing many of your steps in a single statement. By using a TOP() cluse will be able to set the flag on at most one row. By using the OUTPUT cluse you can return this to the application automically.

I define a simple test table and populate it:

create table dbo.T(id int, IsProcessing bit default 0);

insert dbo.T(id) values (1),(2),(3),(4);

The output clause needs a table variable to receive the changed values:

declare @U table (id int);

A bit of debug code to make the "before" and "after" states obvious:

select 'Before' as B, * from dbo.T;

And the statement itself:

update top (1) dbo.T
set IsProcessing = 1
OUTPUT INSERTED.id
INTO @U(id)
where IsProcessing = 0;

And the results:

select 'After' as A, * from dbo.T;
select * from @U;

This is the output from the second execution of the above.

B       id  IsProcessing
Before  1   1
Before  2   0
Before  3   0
Before  4   0

A       id  IsProcessing
After   1   1
After   2   1
After   3   0
After   4   0

id 
2

The row with id=2 had gone from IsProcessing=0 to IsProcessing=1 and that id is returned in the table variable.

With this trivial data the rows are processed in the sequence they were created. In a more complex environment the optimizer may choose any row that matches the where clause. If you have a requirement to process rows in, say, time sequence further qualification will be required.

I haven't thought it all the way through but I believe his will work at any isolation level with or without explicit transactions.

It is of course possible that there are no rows with IsProcessing=0 at the time the statement runs. In this case the table variable will have zero rows.

To fully isolate each transaction from the other you could try sp_getapplock. This will add overhead and reduce concurrency. You have to be careful to release the app lock as soon as you can in both success and failure scenarios.

Best Answer

Related Solutions

SQL Server Concurrency – Managing SELECT-UPDATE Pattern

SQL Server – How to Exclusively Access a Table and Prevent Other Queries

Related Question