用 SELECT ... FOR UPDATE 避免 Race condition

http://blog.xuite.net/vexed/tech/22289223-%E7%94%A8+SELECT+...+FOR+UPDATE+%E9%81%BF%E5%85%8D+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 讀寫這些 row ，直到 COMMIT 為止。也就是當我執行 SELECT ... FOR UPDATE ，其他的 thread 必須等到我 COMMIT ，才能碰到這些被 SELECT ... FOR UPDATE 讀取的 row 。

而按造 PostgreSQL 的官方文件，其他的 thread 如果要 UPDATE 、 DELETE 、 SELECT ... FOR UPDATE 被我用 SELECT ... FOR UPDATE 鎖起來的 row ，必須等待我 COMMIT 。按照字面上的意思，在 PostgreSQL 裡其他 thread 仍能 SELECT 這些被鎖起來的 row，這和 MySQL 有點不同，要特別注意。