Optimistic concurrency control across tables in Postgres

Question

I'm looking for a way to manage optimistic concurrency control across more than one table in Postgres. I'm also trying to keep business logic out of the database. I have a table setup something like this:

CREATE TABLE master
(
    id SERIAL PRIMARY KEY NOT NULL,
    status VARCHAR NOT NULL,
    some_value INT NOT NULL,
    row_version INT NOT NULL DEFAULT(1)
)

CREATE TABLE detail
(
    id SERIAL PRIMARY KEY NOT NULL,
    master_id INT NOT NULL REFERENCES master ON DELETE CASCADE ON UPDATE CASCADE,
    some_data VARCHAR NOT NULL
)

master.row_version is automatically incremented by a trigger whenever the row is updated.

The client application does the following:

1. Reads a record from the master table.
2. Calculates some business logic based on the values of the record, this may include a delay of several minutes involving user interaction.
3. Inserts a record into the detail table based on logic in step 2.

I want step 3 to be rejected if the value of master.row_version has changed since the record was read at step 1. Optimistic concurrency control seems to me like the right answer (the only answer?), but I'm not sure how to manage it across two tables like this.

I'm thinking a function in Postgres with a row-level lock on the relevant record in the master table is probably the way to go. But I'm not sure if this is my best/only option, or what that would look like (I'm a bit green on Postgres syntax).

I'm using Npgsql, given that the client application is written in C#. I don't know if there's anything in it which can help me? I'd like to avoid a function if possible, but I'm struggling to find a way to do this with straight-up SQL, and anonymous code blocks (at least in Npgsql) don't support the I/O operations I'd need.

Answer 1

Locking is out if you want to use optimistic concurrency control, see the Wikipedia article on the topic:

OCC assumes that multiple transactions can frequently complete without interfering with each other. While running, transactions use data resources without acquiring locks on those resources.

You could use a more complicated INSERT statement. If $1 is the original row_version and $2 and $3 are master_id and some_data to be inserted in detail, run

WITH m(id) AS
     (SELECT CASE WHEN master.row_version = $1
                  THEN $2
                  ELSE NULL
             END
      FROM master
      WHERE master.id = $2)
INSERT INTO detail (master_id, some_data)
   SELECT m.id, $3 FROM m

If row_version has changed, this will try to insert NULL as detail.id, which will cause an
ERROR: null value in column "id" violates not-null constraint
that you can translate into a more meaningful error message.

Answer 2

I've since come to the conclusion that a row lock can be employed in a "typical" pessimistic concurrency control approach, but when combined with a row version can produce a "hybrid" approach with some meaningful benefits.

Unsurprisingly, the choice of pessimistic, optimistic or "hybrid" concurrency control depends on the needs of the application.

Pessimistic Concurrency Control

A typical pessimistic concurrency control approach might look like this.

1. Begin database transaction.
2. Read (and lock) record from master table.
3. Perform business logic.
4. Insert a record into detail table.
5. Commit database transaction.

If the business logic at step 3 is long-running, this approach may be undesirable as it leads to a long-running transaction (generally unfavourable), and a long-running lock on the record in master which may be otherwise problematic for concurrency.

Optimistic Concurrency Control

An approach using only optimistic concurrency control might look more like this.

1. Read record (including row version) from master table.
2. Perform business logic.
3. Begin database transaction.
4. Increment row version on record in master table (an optimistic concurrency control check).
5. Insert a record into detail table.
6. Commit database transaction.

In this scenario, the database transaction is held for a shorter period of time, as are any (implicit) row locks. But, the increment of row version on the record in the master table may be a bit misleading to concurrent operations. Imagine several concurrent operations of this scenario, they'll start failing on the optimistic concurrency check because the row version has been incremented, even though the meaningful properties on the record haven't been changed.

Hybrid Concurrency Control

A "hybrid" approach uses both pessimistic locking and (sort of) optimistic locking, like this.

1. Read record (including row version) from master table.
2. Perform business logic.
3. Begin database transaction.
4. Re-read record from master table based on it's ID and row version (an optimistic concurrency control check of sorts) AND lock the row.
5. Insert a record into detail table.
6. Commit database transaction.

If step 4 fails to obtain a record, this should be considered an optimistic concurrency control check failure. The record has been changed since step 1 so the business logic is no longer valid.

Like the typical pessimistic concurrency control scenario, this involves a transaction and an explicit row lock, but the duration of the transaction+lock no longer includes the time necessary to perform the business logic.

Like the optimistic concurrency control scenario, the record requires a version. But where it differs is that the version is not updated, which means other operations depending on that row version won't be impacted.

Example of Hybrid Approach

An example of where the hybrid approach might be favourable:

A blog has a post table and comment table. Comments can be added to a post only if the post.comments_locked flag is false. The process for adding comments could use the hybrid approach, ensuring users can concurrently add comments without any concurrency exceptions.

The owner of the blog may edit their post, in which case the conventional optimistic concurrency control approach could be employed. The owner of the blog can have a long-running edit process which won't be affected by users adding comments. When the post is updated to the database, the version will be incremented, which means any in-progress comment-adding operations will fail, but they could be easily retried with a database-wins approach of re-fetching the post record from the database and retrying the process.