Locking and deadlocks

MVCC ขจัดความจำเป็นในการ lock เพื่ออ่าน แต่การเขียนยังต้องประสานกันอยู่ดี เมื่อสอง transaction ต้องการเปลี่ยน row เดียวกัน ตัวหนึ่งต้องรอให้อีกตัวทำเสร็จ ส่วนใหญ่แล้ว PostgreSQL จับ row lock ที่จำเป็นให้คุณในระหว่าง UPDATE หรือ DELETE แต่บางครั้งคุณก็อยาก lock row ตั้งแต่ตอนอ่าน เพื่อไม่ให้ใครแก้ row นั้นตัดหน้าคุณ

จอง row ด้วย FOR UPDATE

SELECT ... FOR UPDATE อ่าน row แล้ว lock ไว้เหมือนคุณกำลังจะ update ทันที ทำให้ transaction อื่นที่พยายาม update, delete หรือ lock row เดียวกันต้องรอจนกว่าคุณจะ commit หรือ roll back นี่คือรูปแบบมาตรฐานสำหรับ “อ่านยอดเงิน ตัดสินใจ แล้วเขียนกลับ” โดยไม่เกิด lost update

psql

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- the row is now locked for this transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Node.js

await client.query('BEGIN');
const { rows } = await client.query(
  'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
  [1],
);
await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, 1]);
await client.query('COMMIT');

Python

with conn.transaction():
    cur.execute("SELECT balance FROM accounts WHERE id = %s FOR UPDATE", (1,))
    balance = cur.fetchone()[0]
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))

Go

tx, _ := conn.Begin(ctx)
var balance float64
tx.QueryRow(ctx, "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", 1).Scan(&balance)
tx.Exec(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, 1)
tx.Commit(ctx)

Rust

let mut tx = pool.begin().await?;
let balance: f64 = sqlx::query_scalar("SELECT balance FROM accounts WHERE id = $1 FOR UPDATE")
    .bind(1_i64)
    .fetch_one(&mut *tx)
    .await?;
sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2")
    .bind(100_f64).bind(1_i64)
    .execute(&mut *tx)
    .await?;
tx.commit().await?;

lock คู่หูคือ FOR SHARE ซึ่งยอมให้คนอื่นอ่านและจับ FOR SHARE ได้ด้วย แต่ block ทุกคนที่ต้องการ FOR UPDATE หรือจะเขียน ใช้ตอนที่คุณต้องรับประกันว่า row จะไม่เปลี่ยนใต้มือ ทั้งที่ตัวคุณเองไม่ได้ตั้งใจจะเขียน

lock wait

เมื่อ transaction ขอ lock ที่อีกตัวถืออยู่ ก็แค่รอเฉย ๆ query ที่รออยู่จะดูเหมือนค้าง แล้วกลับมาทำงานต่อทันทีที่ผู้ถือ commit หรือ roll back การถือสั้น ๆ มองไม่เห็นจากผู้ใช้ ส่วนการถือนาน ๆ กลายเป็นคิว

-- session 2 issues SELECT ... FOR UPDATE on a row session 1 already locked
-- session 2 prints nothing and blocks here until session 1 ends its transaction

เมื่อการรอกลายเป็น deadlock

deadlock เกิดขึ้นเมื่อสอง transaction ต่างก็ถือ lock ที่อีกตัวต้องการ Transaction A lock row 1 แล้วต้องการ row 2 ส่วน transaction B lock row 2 แล้วตอนนี้ต้องการ row 1 ไม่มีตัวใดเดินหน้าต่อได้ และไม่มีตัวใดยอมปล่อยก่อน

flowchart LR
  A["Txn A holds lock on row 1"] -->|waits for row 2| B["Txn B holds lock on row 2"]
  B -->|waits for row 1| A

วงจร deadlock: แต่ละ transaction รอ lock ที่อีกตัวถืออยู่

PostgreSQL รัน deadlock detector อยู่ หลังจาก timeout สั้น ๆ ตัว detector จะเห็นวงจร เลือก transaction หนึ่งเป็นเหยื่อ แล้ว abort ทิ้งพร้อม error เพื่อให้อีกตัวทำงานต่อได้ เหยื่อจะเห็น SQLSTATE 40P01 และควร retry transaction นั้นใหม่ทั้งชุด

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 678; blocked by process 12346.
HINT:  See server log for query details.

เลือกที่จะไม่รอ: NOWAIT และ SKIP LOCKED

บางครั้งการรอเป็นพฤติกรรมที่ผิด มีตัวขยายสองตัวที่เปลี่ยนพฤติกรรมนี้ได้ NOWAIT ทำให้คำสั่ง fail ทันทีแทนที่จะเข้าคิวหาก row ถูก lock อยู่ ส่วน SKIP LOCKED ก้าวข้าม row ที่ถูก lock อย่างเงียบ ๆ และคืนเฉพาะ row ที่ lock ได้จริง นี่คือแกนหลักของรูปแบบ work-queue ที่ worker หลายตัวต่างก็คว้างานว่างตัวถัดไป

psql

-- fail at once rather than wait:
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- a worker grabbing the next available job, ignoring locked ones:
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

Node.js

const { rows } = await client.query(
  `SELECT id FROM jobs WHERE status = 'pending'
   ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1`,
);

Python

cur.execute(
    "SELECT id FROM jobs WHERE status = 'pending' "
    "ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1"
)
job = cur.fetchone()

Go

rows, err := tx.Query(ctx,
    `SELECT id FROM jobs WHERE status = 'pending'
     ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1`)

Rust

let job: Option<i64> = sqlx::query_scalar(
    "SELECT id FROM jobs WHERE status = 'pending' \
     ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 1",
)
.fetch_optional(&mut *tx)
.await?;

ใน pgAdmin: เพื่อสังเกต lock wait สด ๆ ให้รัน BEGIN; SELECT ... FOR UPDATE ในแท็บ Query Tool หนึ่งโดยไม่ commit แล้วรันคำสั่งเดียวกันในแท็บที่สองแล้วดูว่าค้าง ส่วน view pg_locks จะบอกว่าใครถืออะไรอยู่ระหว่างที่คุณทดลอง

เคล็ดลับและข้อควรระวัง

• lock row ในลำดับที่สอดคล้องกันทุกที่ในโค้ดของคุณ ถ้าทุก transaction แตะ row 1 ก่อน row 2 วงจรที่ก่อให้เกิด deadlock ก็จะไม่มีวันก่อตัวขึ้นได้
• ปฏิบัติต่อ deadlock error (40P01) และ serialization failure (40001) แบบเดียวกัน คือ roll back แล้ว retry ทั้ง transaction ทั้งสองเป็นเรื่องที่คาดหมายได้ภายใต้ concurrency
• ทำให้งานระหว่างการจับ lock กับการ commit สั้นที่สุดเท่าที่จะทำได้ ยิ่งคุณถือ lock นานเท่าใด คนอื่นก็เข้าคิวต่อท้ายคุณนานขึ้นเท่านั้น
• SKIP LOCKED เหมาะอย่างยิ่งสำหรับคิว แต่ผิดสำหรับงานบัญชี เพราะจงใจข้าม row ทิ้ง ดังนั้นอย่าใช้ในงานที่คุณต้องเห็นทุก row ที่ตรงเงื่อนไข

🐘 ลำดับ lock ที่สอดคล้องกันป้องกัน deadlock ส่วนใหญ่ได้

deadlock ไม่ได้เกิดจากการ lock มากเกินไป แต่เกิดจากการ lock ในลำดับที่ขัดแย้งกัน จงตัดสินใจลำดับสากลหนึ่งลำดับสำหรับการจับ lock เช่นเรียงตาม primary key จากน้อยไปมากเสมอ และนำไปใช้ในทุก transaction PostgreSQL จะยังตรวจจับ deadlock ใด ๆ ที่หลุดรอดมาได้ แต่คุณแทบจะไม่เปิดโอกาสให้เกิดขึ้นเลย

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
row-level lock	ให้ concurrency สูง เพราะ transaction อื่นยังแก้ row อื่นใน table เดียวกันได้	ต้องออกแบบลำดับการ lock ให้สอดคล้องกันเอง ไม่งั้นเสี่ยง deadlock
table-level lock	เขียนง่าย รับประกันไม่มีใครแตะ table ทั้งก้อนระหว่างที่คุณทำงาน	บล็อกงาน concurrent จำนวนมากโดยไม่จำเป็น ลด throughput ทั้งระบบ
SELECT ... FOR UPDATE แบบชัดเจน	ควบคุมจังหวะการ lock ได้แน่นอน ป้องกัน lost update	ต้องเขียนโค้ดเพิ่มและคิดเรื่องลำดับ lock เอง
พึ่ง lock โดยนัยจาก UPDATE เฉย ๆ	โค้ดสั้นกว่า ไม่ต้องคิดเรื่อง lock ล่วงหน้า	ควบคุมจังหวะ lock ไม่ได้ เสี่ยง race condition ระหว่างอ่านกับเขียน

ข้อผิดพลาดที่พบบ่อย

• lock row ในลำดับที่ต่างกันในแต่ละ code path — นี่คือสูตรคลาสสิกของ deadlock ให้กำหนดลำดับ lock สากลหนึ่งเดียว เช่นเรียงตาม primary key แล้วใช้ทุกที่
• ถือ transaction พร้อม lock ค้างไว้ระหว่างทำงานแอปพลิเคชันที่ช้า — ยิ่งถือ lock นาน คิวที่รอก็ยิ่งยาว ให้ทำงานที่ช้านอก transaction แล้วค่อยเปิด transaction สั้น ๆ ตอนเขียนจริง
• ไม่จัดการ deadlock error — PostgreSQL จะ abort transaction หนึ่งเป็นเหยื่อด้วย SQLSTATE 40P01 เสมอ ถ้าไม่มีการ retry งานของเหยื่อจะหายไปเฉย ๆ

💡 ตัวอย่างจากของจริง

ระบบ job queue บน PostgreSQL (Sidekiq-style) — ใช้ SELECT ... FOR UPDATE SKIP LOCKED ให้ worker หลายตัวคว้างานคนละ row พร้อมกันโดยไม่ต้องรอ lock ของกันและกัน เป็น pattern ที่ใช้กันแพร่หลายสำหรับสร้างคิวงานตรงบนฐานข้อมูลโดยไม่ต้องพึ่ง message broker แยกต่างหาก

ระบบจองที่นั่ง/ตั๋ว — ใช้ SELECT ... FOR UPDATE lock ที่นั่งก่อนยืนยันการจอง เพื่อไม่ให้สองคนจองที่นั่งเดียวกันพร้อมกันได้สำเร็จทั้งคู่

SELECT ... FOR UPDATE ทำอะไร?

คืน row เร็วขึ้นด้วยการข้าม snapshot อ่านและ lock row ไว้ เพื่อให้คนอื่นต้องรอก่อนจะแก้ row นั้น กัน row ไม่ให้ถูกลบอย่างถาวร commit transaction ปัจจุบัน

PostgreSQL แก้ deadlock ระหว่างสอง transaction อย่างไร?

รอตลอดไปจนกว่า client ตัวหนึ่งจะตัดการเชื่อมต่อ ตรวจจับวงจรแล้ว abort transaction หนึ่งเป็นเหยื่อ รวมสอง transaction เข้าด้วยกัน ลด isolation level ลงโดยอัตโนมัติ

แนวปฏิบัติไหนที่ป้องกัน deadlock ได้น่าเชื่อถือที่สุด?

ใช้ SELECT โดยไม่มี FOR UPDATE จับ lock ในลำดับที่สอดคล้องกันข้ามทุก transaction ตั้ง isolation level เป็น Read Committed รัน VACUUM บ่อยขึ้น

Submit

Mark as complete