Search code examples
mysqlsqlpostgresqloraclemvcc

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

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

Solution

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