Blog / Data Base

Avoiding typical mistakes and explicit locking

we will see sample of mistakes such as sales invoice naming series and solve it by considering alternative solution.

February 22, 2020 · 2 min read

Let's assume we two transactions :

Transaction 1	Transaction 2
BEGIN;	BEGIN;
SELECT max(id) FROM product;	SELECT max(id) FROM product;
User will see 17	User will see 17
User will decide to use 18	User will decide to use 18
INSERT INTO product ... VALUES (18, ...)	INSERT INTO product ... VALUES (18, ...)
COMMIT;	COMMIT;

In this case, there will be either a duplicate key violation or two identical entries.

One solution to the above problem is using locking :


BEGIN;
LOCK TABLE product IN ACCESS EXCLUSIVE MODE;
INSERT INTO product SELECT max(id) + 1, ... FROM product;
COMMIT;

Keep in mind that this is a pretty nasty way of doing this kind of operation because nobody else can read or write to the table during your operation. Therefore, ACCESS EXCLUSIVE should be avoided at all costs.

lets see the example where you are asked to write an application generating invoice numbers.

The tax office might require you to create invoice numbers without gaps and without duplicates.

How would you do it?

Of course, one solution would be a table lock. However, you can really do better. Here is what I would do to handle the numbering problem we are trying to solve:

CREATE TABLE t_invoice (id int PRIMARY KEY);
CREATE TABLE t_watermark (id int);
INSERT INTO t_watermark VALUES (0);

WITH x AS (UPDATE t_watermark SET id = id + 1 RETURNING *) INSERT INTO t_invoice SELECT * FROM x RETURNING *;

In this case, we introduced a table called t_watermark. It contains just one row, so WITH command will be executed first. The row will be locked and incremented, and the new value will be returned.

Only one person can do this at a time. The value returned by the CTE(PostgreSQL common table expressions) is then used in the invoice table. It is guaranteed to be unique. The beauty is that , there is only a simple row lock on the watermark table which leads to no reads being

blocked in the invoice table. Overall, this way is more scalable.

Avoiding typical mistakes and explicit locking

Ahmad Ragheb