Best way of managing database concurrency?

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?

Solution

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).