高並發下的sql優化

上周活動有個並發功能。這裡撇開業務不談，只說技術實現。

假設有資料庫表中有列（列名是energy），作為一個計數器，這個計數器有一個上限。用戶發來一個請求，參數是一個隨機值，計數器會加上這個隨機值，直到到達上限為止（不能超過上限）。我們需要記錄處理用戶請求所增加的值，這個值包括正數和零。一開始的sql如下：

<update id="updateTable" parameterType="java.util.Map"> update table set energy = least(energy + #{incr, jdbcType=DECIMAL}, #{limit, jdbcType=DECIMAL}) , UPDATE_TIME = sysdate where energy &lt; #{limit, jdbcType=DECIMAL} AND id = #{id, jdbcType=VARCHAR}</update>

這條sql中，id表示某條記錄的id，limit表示上限，incr表示增加的值（隨機值，每次請求的數值不同）。由於sql使用了least方法，可以確保energy不會超過上限（超出的數值會截斷）。

這條sql看起來沒問題，測試環境中也工作良好。後來做壓力測試，10個線程還工作良好，但線程數量增加到20時，發現了一個問題：用戶的請求參數值不一定是增加的值。如果energy當前是9.5，上限是10。現在有A和B兩個線程，A的incr是0.5，B的incr是0.2，B線程的請求先操作，energy變成9.7，然後再處理A線程的請求，也會成功，但A線程誤以為0.5全部增加，其實只增加了0.3。

因為考慮到Mybatis的update不能返回實際增加值，所以我當時認為改動比較大，還想到用redis控制。但我試圖找到一個簡單方法，發現錯誤只是出現在即將封頂的情況，在高並發情況下其數量只有1-3條的。那麼可以使用樂觀鎖，更新失敗不要重試，相當於incr是0，按這種思路sql可以改成以下：

<update id="updateTable" parameterType="java.util.Map"> update table set energy = energy + #{incr, jdbcType=DECIMAL} , UPDATE_TIME = sysdate where energy &lt; #{limit, jdbcType=DECIMAL} AND id = #{id, jdbcType=VARCHAR} AND energy + #{incr, jdbcType=DECIMAL} &lt;= #{limit, jdbcType=DECIMAL}</update>

只是增加了一個where條件。在上面的問題中，A線程where條件是false，所以不會更新，會認為這次請求的incr是0

推薦閱讀：