MySQL實現計數器如何在高並發情境下更新並保持資料正確性

一張表 兩個欄位 一個id 一個useCount
表裡存了100個id 每個id對應自己的useCount
業務情境是：當id每使用一次 useCount要加1。 當useCount大於1000時 這個id就不能在被使用了（換句話說 無法從資料庫中查出）

在高並發情況下，會遇到一種問題：假設資料表中有一條記錄為 id=123456; useCount=999
a與b兩個串連並發查詢這個id 123456 都執行下列sql:

select * from table where id=123456 and useCount < 1000

a先執行 得到id 123456的useCount是999 之後在程式裡做了一些邏輯判斷或業務操作後執行sql： update useCount + 1
在a做判斷且沒有update之前 b也執行了查詢sql 發現useCount是999 之後它也會執行sql: update useCount +1
但是 事實上b不應該取得這個id 因為a已經是第1000個使用者

所以請教一下 在高並發情況下 僅使用資料庫 如何完成這一需求？

回複內容：

一張表 兩個欄位 一個id 一個useCount
表裡存了100個id 每個id對應自己的useCount
業務情境是：當id每使用一次 useCount要加1。 當useCount大於1000時 這個id就不能在被使用了（換句話說 無法從資料庫中查出）

在高並發情況下，會遇到一種問題：假設資料表中有一條記錄為 id=123456; useCount=999
a與b兩個串連並發查詢這個id 123456 都執行下列sql:

select * from table where id=123456 and useCount < 1000

a先執行 得到id 123456的useCount是999 之後在程式裡做了一些邏輯判斷或業務操作後執行sql： update useCount + 1
在a做判斷且沒有update之前 b也執行了查詢sql 發現useCount是999 之後它也會執行sql: update useCount +1
但是 事實上b不應該取得這個id 因為a已經是第1000個使用者

所以請教一下 在高並發情況下 僅使用資料庫 如何完成這一需求？

典型的並發問題。建議用樂觀鎖模式處理，同時兼顧效能。具體步驟如下：

1、添加第3個欄位version, int類型，default值為0。version值每次update時作加1處理。

ALTER TABLE test_tb ADD COLUMN version INT DEFAULT '0' NOT NULL AFTER useCount;  

2、select時同時擷取version值（例如為3）

SELECT useCount, VERSION FROM test_tb WHERE id=123456 AND useCount < 1000

3、update時檢查version值是否為第2步擷取到的值

UPDATE test_tb SET VERSION=4, useCount=useCount+1 WHERE id=123456 AND VERSION=3

如果update的記錄數為1，則表示成功；
如果update的記錄數為0，則表示已經被其他應用（線程）update過了，需作異常處理

註：以上思路參考自JavaEE的JPA樂觀鎖@version處理機制。這裡有一個參考http://www.blogjava.net/sway/archive/2008/10/10/233569.html

對於你現在的使用方式，userCount就類似於version，有些類似樂觀鎖的方式。

只是你要嚴格控制userCount的更新，那就加行鎖唄！

select * from table where id=123456 and useCount < 1000 for update;

update table set userCount = userCount + 1 where userCount = userCount and userCount < 1000;
InnoDB環境下為了不鎖表，userCount還需要建索引。

