解決重複賣票問題

在搶演唱會門票時，常遇到選完座位要結帳出現該座位已被訂走的訊息。

以後端的角度來說，如何確保座位只會被一位使用者買走而不 block 住整個系統，是基本的要求。

這篇文章探討如何解決重複賣票問題。

假設我們有一張表記錄演唱會門票售出情形

CREATE TABLE  tickets (
  id SERIAL PRIMARY KEY NOT NULL,
  is_solded NUMBER DEFAULT 0,
  owner VARCHAR NOT NULL,
  created_at TIMESTAMP WITHOUT TIMEZONE DEFAULT now),
  updated_at TIMESTAMP WITHOUT TIMEZONE
)

訂購門票的 SQL 用一般的 transaction 寫法:

BEGIN

// 判斷票是否被訂走
SELECT is_solded, owner FROM tickets WHERE id = 1 and is_solded = 0

// 如果票券還在則更新狀態
UPDATE tickets SET is_solded = 1, owner = 'Alex' WHERE id = 1;

COMMIT

這樣的寫法有什麼問題呢？ 假如同時有另一位使用者在 Alex 的 transaction 還沒結束時也訂了 id = 1 的票，則這張票會被他買走。

為了避免此情況，可以加上 FOR UPDATE 先查詢:

BEGIN

// 使用 FOR UPDATE 
SELECT is_solded, owner FROM tickets WHERE id = 1 and is_solded = 0 FOR UPDATE;

// 如果票券還在則更新狀態
UPDATE tickets SET is_solded = 1, owner = 'Alex' WHERE id = 1;

COMMIT

FOR UPDATE 會加上 row level lock，避免其它 transaction 更動記錄。

PostgreSQL

如果 database server 是 PostgreSQL，以下的 transaction 也能防止 double booking

BEGIN

UPDATE tickets SET is_solded = 1, owner = 'Alex' WHERE id = 1 and is_solded = 0;

COMMIT

假如有另一位使用者同時執行 transaction，在第一個 transaction commit 後，第二個 transaction UPDATE 不會 overwrite 結果。

PG 在每筆 record 後面會加上 lock 的資訊並放在 heap，當第二個 transaction 在 heap 找到 id = 1 的 record 就能得到目前 lock 的狀態。

在第一個 transaction commit 後，PG 會更新 heap 資訊，讓其它 transaction 在 update 時得到新的狀態。

用這種 implicitly 寫法最好加個註解說明。不同的 RDBMS 對於 isolation level 和 lock 有自己的管理方式，工程師需要了解細節才能設計出穩定的服務。