用 SELECT ... FOR UPDATE 避免 Race condition @ Vexed's Blog

200902151644用 SELECT ... FOR UPDATE 避免 Race condition

假設我們有一套出貨系統，在出貨時必須確認還有貨，那我們會下下面這樣的兩行 SQL ︰

SELECT quantity FROM product WHERE product_id = 5;
UPDATE product SET quantity = quantity - 1 WHERE product_id = 5;

如果 SELECT 到的 quantity 大於 0 ，才執行 UPDATE 。

但我們也知道現在的電腦都是多工的，如果有兩個 thread 的執行順序像下面這樣，貨又剛好只剩一筆，那代誌就大條了！

	thread A	thread B
↓	SELECT
↓		SELECT
↓	UPDATE
↓		UPDATE

thread A 和 thread B SELECT 的結果都是還有貨，但是 thread B UPDATE 的時候其實已經沒有貨了。這就是大名鼎鼎的 Race condition ， SELECT ... FOR UPDATE 就是用來避免這種情況的發生！

Oracle 、 PostgreSQL 、 MySQL 都有 SELECT ... FOR UPDATE 語法，但要特別注意的是 MySQL 只有 Storage Engine 是 InnoDB 的情況下才可以使用。下面就用 MySQL 語法示範使用 SELECT ... FOR UPDATE ︰

START TRANSACTION;
SELECT quantity FROM product WHERE product_id = 5 FOR UPDATE;
UPDATE product SET quantity = quantity - 1 WHERE product_id = 5;
COMMIT;

按照 MySQL 的官方文件，使用 SELECT ... FOR UPDATE 會在被讀取的 row 加上 exclusive lock ，而 exclusive lock 可以避免其他 thread UPDATE 、 DELETE 、 SELECT ... FOR UPDATE 這些 row ，直到 COMMIT 為止。要特別注意其他 thread 仍能 SELECT 這些被鎖起來的 row ，要 SELECT ... FOR UPDATE 才會被 exclusive lock 擋下。