Atomic Increment/Decrement operations in SQL and fun with locks

SQL supports atomic increment and decrement operations on numeric columns. The “trick” is to use an update query following a specific pattern using a relative right hand side value.

-- This assumes the existence of a table defined as: -- CREATE TABLE test(id SERIAL PRIMARY KEY, x INTEGER); UPDATE test set x = x - 1 where id = 1;

Deadlock risk

Example

insert into test values (1, 0); insert into test values (2, 0);
$1> psql psql1> BEGIN; psql1> UPDATE test SET x = x + 1 WHERE id = 1; -- A lock is acquired on the row with id 1, no other transactions can update it$2> psql psql2> BEGIN; psql2> UPDATE test SET x = x + 1 WHERE id = 2; -- A lock is acquired on the row with id 2, no other transactions can update itpsql1> UPDATE test SET x = x + 1 WHERE id = 2; -- The second session hasn't committed yet, this operation is now waitingpsql2> UPDATE test SET x = x + 1 WHERE id = 1; -- The first session hasn't committed yet, this operation is now waiting
ERROR: deadlock detected DETAIL: Process 14803 waits for ShareLock on transaction 43356; blocked by process 14431. Process 14431 waits for ShareLock on transaction 43357; blocked by process 14803. HINT: See server log for query details. CONTEXT: while updating tuple (0,1) in relation "test"

Solution

Deterministic Condition

$1> psql psql1> BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; psql1> UPDATE test SET x = x + 1 WHERE id = 2; -- A lock is acquired on the row with id 2, no other transaction can update it$2> psql psql2> BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; psql2> UPDATE test set x = x + 1 WHERE x % 2 = 0; -- A lock is acquired on all rows with an even x value, since there's a lock on the row with id 2, this query waits for -- the first transaction to commit or rollbackpsql1> UPDATE test set x = x + 1 WHERE x % 2 = 0;

Real world example

CREATE TABLE inventories(sku VARCHAR(3) PRIMARY KEY, quantity INTEGER);

Our original (over-engineered) solution

Example code


Harry's Engineering

The engineering blog of Harry's

pierre jambet

Written by

I like beers. I made the Beech app. Developer @harrys

Harry's Engineering

The engineering blog of Harry's