Best way of managing database concurrency?

Question

I've come to an issue when handling concurrency.

In the example below, two users A and B edit the same invoice and make different changes to it. If both of them click save at the same time I would like one of them to succeed, and the other one to fail. Otherwise the resulting invoice would be an undesired "merged invoice".

Here's the example, tested in PostgreSQL (but I think this question should be database agnostic):

create table invoice (
  id int primary key not null,
  created date
);

create table invoice_line (
  invoice_id int, 
  line numeric(6), 
  amount numeric(10,2),
  constraint fk_invoice foreign key (invoice_id) references invoice(id)
  );

insert into invoice(id, created) values (123, '2018-03-17');
insert into invoice_line (invoice_id, line, amount) values (123, 1, 24);
insert into invoice_line (invoice_id, line, amount) values (123, 2, 26);

So the initial rows of the invoice are:

invoice_id  line  amount
----------  ----  ------
       123     1      24
       123     2      26

Now, user A edits the invoice, removes line 2 and clicks SAVE:

-- transaction begins

set transaction isolation level serializable;

select * from invoice where id = 123; -- #1 will it block the other thread?

delete invoice_line where invoice_id = 123 and line = 2;

commit; -- User A would expect the invoice to only include line 1.

At the same time user B edits the invoice and adds line 3, and clicks SAVE:

-- transaction begins

set transaction isolation level serializable;

select * from invoice where id = 123; -- #2 will this wait the other thread?

insert into invoice_line (invoice_id, line, amount) values (123, 3, 45);

commit; -- User B would expect the invoice to include lines 1, 2, and 3.

Unfortunately both transactions succeed, and I get the merged rows (corrupted state):

invoice_id  line  amount
----------  ----  ------
       123     1      24
       123     3      45

Since this is not what I wanted, what options do I have to control concurrency?

only one should be process-able at a time. when a change is submitted it should check that the state to be changed from hasn't been adjusted from what it was when the user selected the data they were modifying. — TZHX, Mar 22 '18 at 15:10
You have to handle this within a stored proc, using `if else` or best method to wrap them in begin and end, Both transactions will succeed what ever transaction isolation level u choose to opt for. — Ven, Mar 22 '18 at 15:14

score 2 · Accepted Answer · answered Mar 22 '18 at 15:38

This is not a database concurrency issue. The ACID properties of databases are about transactions completing, while maintaining database integrity. In the situation you describe, the transactions are correct, and the database is correctly processing them.

What you want is a locking mechanism, essentially a semaphore that guarantees that only one user can have write access to the data at any one time. You might be able to rely on database locking mechanisms, capturing when locks fail to occur.

But, I would suggest one of two other approaches. If you are comfortable with the changes being only in the application logic, then put the locking mechanism there. Have a place where a user can "lock" the table or record; then don't let anyone else touch it.

You can go a step further. You can require that users obtain "ownership" of the table for changes. Then you can implement a trigger that fails unless the user is the one making the changes.

And, you might think of other solutions. What I really want to point out is that your use-case is outside what RDBMSs do by default (because they would let both transactions complete successfully). So, you will need additional logic for any database (that I'm familiar with).

Maybe this use case is a good fit for optimistic locking? (i.e. adding an extra "version" column in the invoice table) — The Impaler, Mar 22 '18 at 18:16

score 0 · Answer 2 · answered Mar 22 '18 at 16:03

Invoice line items, as a general rule, should not be edited or deleted after being posted. If a customer needs a charge reversed, the typical way to do that is to add a new transaction that credits the amount, possibly with a cross-reference field that contains the ID of the line item that is being reversed. The advantage of this approach is (1) You can modify a customer's balance without having to go back and rebook any prior statement periods, and (2) you won't run into concurrency issues like this one, which are hard to solve.

If the invoice hasn't posted yet, you still don't allow editing of the line items. Instead, you'd cancel the prior invoice and create a new one, with all new line items. This again dodges the concurrency issue at hand.

Didn't mention it, but the use case is for non-posted invoices. I guess I can remove the edit button and provide delete+create. Not ideal but -- as you say -- this way we will dodge the issue. — The Impaler, Mar 22 '18 at 16:13

Best way of managing database concurrency?

2 Answers2