0

Overview

Assume that there is a table which controls stock amount information.

CREATE TABLE products(
    id INTEGER PRIMARY KEY,
    remaining_amount INTEGER NOT NULL
);
INSERT INTO products(id, remaining_amount) VALUES (1, 1);

Now, user A and B try to take the last stock at the same time.

A/B: UPDATE products
     SET remaining_amount = remaining_amount - 1
     WHERE id = 1 and remaining_amount > 0;

The questions are:

  • Could remaining_amount never be negative values? Do we need any explicit pessimistic row locking?
  • Which transaction level should I use: READ COMMITTED, REPEATABLE READ, SERIALIZABLE or READ UNCOMMITTED(only for MySQL)?
  • Does it yield different conclusion with different RDBMS?

Related Information

mpyw
  • 5,526
  • 4
  • 30
  • 36

2 Answers2

1

If performance is a must I would avoid locks and changing isolation levels. Use Optimistic Locking instead. There are three implementations of it, but for simplicity I would use the traditional one: adding a version column to the table.

For example:

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  remaining_amount INTEGER NOT NULL,
  version int default 0 -- new column
);

INSERT INTO products(id, remaining_amount) VALUES (1, 1);

select * from products where id = 1; -- A and B get version 0

UPDATE products
SET remaining_amount = remaining_amount - 1,
    version = version + 1
WHERE id = 1 and remaining_amount > 0
  and version = 0; -- here, version number from previous SELECT

The above UPDATE returns the count of updated rows; we save it in a variable "c".

In this case, either A or B will get to update the row first and "will win" the update. The other one won't update anything.

We can find out which one won by looking at the count "c". If it's 1 then it was able to update, and if it's 0 then it couldn't update. Easy, isn't it?

The downsides are:

  • It requires an extra column in the table (maybe not, depending on the strategy),
  • Needs an extra SELECT, if you weren't doing so already.
  • It changes the UPDATE query.
  • It requires an extra line of code in your app to check the value of c.

But, it's blazing fast, safe, and does not require any locking or isolation management.

The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

We noticed that each RDBMS(MySQL/Postgres/Oracle) ALWAYS, AUTOMATICALLY locks updating rows WITH ANY TRANSACTION ISOLATION LEVELS. It means that MySQL(InnoDB)'s READ UNCOMMITTED works well.

A B
SET TRANSACTION ISOLATIONLEVEL READ UNCOMMITTED;
BEGIN;		 SET TRANSACTION ISOLATIONLEVEL READ UNCOMMITTED;
BEGIN;
UPDATE products SET remaining_amount = remaining_amount - 1 WHERE remaining_amount > 0;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
UPDATE products SET remaining_amount = remaining_amount - 1 WHERE remaining_amount > 0;
BLOCKED!!!
COMMIT;
Query OK, 0 rows affected
Rows matched: 0 Changed: 0 Warnings: 0
COMMIT;

Special Thanks: @zyake
Other evidences on Postgres: https://twitter.com/zyake/status/1543229753968041984

When checking if a specific tuple satisfies the update condition or when performing an update operation, an exclusive lock per page is acquired, so it is serialized. https://github.com/postgres/postgres/blob/e3fcca0d0d2414f3a50d6fd40eddf48b7df81475/src/backend/access/heap/heapam.c#L3215 https://github.com/postgres/postgres/blob/e3fcca0d0d2414f3a50d6fd40eddf48b7df81475/src/backend/access/heap/heapam.c#L3447

UPDATE: Detailed investigation on MySQL/Postgres

mpyw-yattemita/mysql-postgres-update-locking-and-isolation-levels

However, it is not guaranteed to be conflict-free whenever a SELECT subquery is included. Our investigation revealed that the results vary depending on the transaction isolation level, as follows:

  • UPDATE t SET v=v-1 WHERE id=1 AND v>0
  • UPDATE t SET v=v-1 WHERE EXISTS(SUBQUERY)
  • UPDATE t SET v=(SUBQUERY)-1 WHERE id=1 AND v>0

Postgres

  • For simple updates or subquery SET, use READ COMMITED.
  • For complex subquery WHERE, use REPEATABLE READ and retry on serialization errors.
Simple Subquery WHERE Subquery SET
READ COMMITTED ✅ ❌ 5/6 Broken ✅
REPEATABLE READ ❗ Serialization Error ❗ Serialization Error ❗ Serialization Error
SERIALIZABLE ❗ Serialization Error ❗ Serialization Error ❗ Serialization Error

Subquery WHERE with READ COMMITED will be broken:

[B] Latter ＼ [A] Former Before-Read Delay Pre-Write Delay Post-Write Delay
Act before A's commit ❌ Broken ❌ Broken ❌ Broken
Act after A's commit ✅ ❌ Broken ❌ Broken

MySQL

  • For simple updates or subquery SET, any transaction isolation level works well. READ UNCOMMITTED or READ COMMITTED are recommended.
  • For complex subquery WHERE, use REPEATABLE READ and retry on deadlock errors.
Simple Subquery WHERE Subquery SET
READ UNCOMMITTED ✅ ❌ 4/6 Broken ✅
READ COMMITTED ✅ ❌ 5/6 Broken ✅
REPEATABLE READ ✅ ❗ 1/6 Deadlock ✅
SERIALIZABLE ✅ ❗ 1/6 Deadlock ✅

Subquery WHERE with READ UNCOMMITED will be broken:

[B] Latter ＼ [A] Former Before-Read Delay Pre-Write Delay Post-Write Delay
Act before A's commit ✅ ❌ Broken ❌ Broken
Act after A's commit ✅ ❌ Broken ❌ Broken

Subquery WHERE with READ COMMITED will be broken:

[B] Latter ＼ [A] Former Before-Read Delay Pre-Write Delay Post-Write Delay
Act before A's commit ❌ Broken ❌ Broken ❌ Broken
Act after A's commit ✅ ❌ Broken ❌ Broken

Subquery WHERE with REPEATABLE READ will get deadlocks:

[B] Latter ＼ [A] Former Before-Read Delay Pre-Write Delay Post-Write Delay
Act before A's commit ✅ ✅ ✅
Act after A's commit ✅ ✅ ❗ Deadlock

Subquery WHERE with SERIALIZABLE will get deadlocks:

[B] Latter ＼ [A] Former Before-Read Delay Pre-Write Delay Post-Write Delay
Act before A's commit ✅ ✅ ❗ Deadlock
Act after A's commit ✅ ✅ ✅
mpyw
  • 5,526
  • 4
  • 30
  • 36