Select For Update

前陣子發生商品的庫存欄位發生負值~ 也就是超賣~
因此很有經驗「會向中對齊」的長官就說一定沒用select for update~

啥系 Select For Update
就是把某欄位select 出來做 update

//例如..
SELECT quantity FROM products WHERE id=1
...拉出來減掉購買數... 再更新回DB
Update products Set quantity = $quantity WHERE id =1 

看起來沒問題，但如果在select後，update前，這商品先其他人更新了，這quantity就會有問題了~
所以Oracle可以下

SELECT quantity FROM products WHERE id=1 FOR UPDATE

這樣會把此筆先lock，其他人就select此商品就待等待Update剛成

說這麼多~~ 但公司是用MS SQL....
只好再研究一下怎麼用...

• MS SQL
  網上查到可加HOLDLOCK or UPDLOCK
  研究了一下，由於是用select for update，如果搭HOLDLOCK，可能發生互相等待而造成deadlock，因此應使用UPDLOCK，SQL Server會選擇由一完成update後釋放資源
  所以SQL應該如下，以避免 deadlock
  

  SELECT quantity FROM products WHERE id = 1 WITH (UPDLOCK)
  ...
  UPDATE FROM products SET quantity = $quantity WHERE id =1 

• MySQL refer to [MySQL] 使用 SELECT ... FOR UPDATE 做交易寫入前的確認
  SET AUTOCOMMIT=0;
  BEGIN WORK;
  SELECT quantity FROM products WHERE id=3 FOR UPDATE;
  ===========================================
  此時 products 資料中 id=3 的資料被鎖住(註3)，其它交易必須等待此次交易
  送交後才能執行 SELECT * FROM products WHERE id=3 FOR UPDATE (註2)
  如此可以確保 quantity 在別的交易讀到的數字是正確的。
  ===========================================
  UPDATE products SET quantity = '1' WHERE id=3 ;
  COMMIT WORK;
  ===========================================
  送交(Commit)寫入資料庫，products 解鎖。
  註1: BEGIN/COMMIT 為交易的起始及結束點，可使用二個以上的 MySQL Command 視窗來交互觀察鎖定的狀況。
  

其他問題
"select for update"會造成其他Query等待，如何才能讓其他Query不等待呢？

• Oracle
  

  SELECT * quantity FROM products WHERE id=1 FOR UPDATE NOWAIT;               //不等，回報錯誤
  SELECT * quantity FROM products WHERE id=1 FOR UPDATE NOWAIT skip Locked;   //不等，給目前的值 也就是dirty read 

• MS SQL
  

  SELECT * quantity FROM products WHERE id=1 WITH (NOWAIT);  //不等，回報錯誤
  SELECT * quantity FROM products WHERE id=1 WITH (NOLOCK);  //不等，給目前的值 也就是dirty read 

• MySQL
  等有用到再補....

Reference

• [MySQL] 使用 SELECT ... FOR UPDATE 做交易寫入前的確認
• The SELECT/UPDATE problem, or, why UPDLOCK?