Is it really safe that running `UPDATE t SET v=v-1 WHERE id= ? and v>0` without pessimistic row locking? (MySQL/Postgres/Oracle)

Question

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

• (mysql innodb) Is single update statement with "where" transaction safe?
  • This question concludes that explicit pessimistic row locking is required for MySQL.
• My Twitter friend RDBMS geek says that:
  • Oracle tries to achieve write consistency; if the target row has been changed, the consecutive UPDATE query is automatically rolled back and it retries with implicit pessimistic row locking. He says that it is described in this book: Amazon | Expert Oracle Database Architecture | Kyte, Thomas, Kuhn, Darl | Software Development
  • PostgreSQL has immutable rows and then the old rows are treated as dead tuple; so the latter updates will never be applied.

Answer 1

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	✅	✅	✅

Answer 2

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.