MYSQL 一個特殊需求在不同的MYSQL配置產生不同的結果 與 update 0 是否需要應用程式判斷

來源：AustinDatabases

最近有一個需求關於資料的清理的需求，但是這個需求裡面有一個部分有一個部分是特殊，也就是在資料清理中，是需要進行資料的匯出和匯入的，並確定在匯入和匯出的過程中，匯出資料在匯出到清理的整個過程中中不能被改變，不能進行commited這些資料需要具有獨佔性 。

這裡要完成這個事情，可以採用對於要遷移的行進行鎖定的方法來進行，但鎖定的方法可以用 select * from table where 條件 for update; 但問題的重點是， 在不同的MYSQL配置中會產生什麼樣的結果，不同的結果開發是否能接受的問題。

這裡有一個相關的說明和測試的大綱

1   MYSQL innodb_lock_wait_timeout = 更長的時間如 86400 和  innodb_deadlock_detect =ON

2    MYSQL innodb_lock_wait_timeout =3  和  innodb_deadlock_detect = OFF 的情況

在不同場合下，MySQL 在這兩邊有不同的設定可能性，在一些早期的MYSQL 和網際網路的情況下，innodb_deadlock_detect 是為OFF的，並且在 innodb_lock_wait_timeout = 3 也就是不管怎麼樣，只要出現互斥的狀態下，鎖超時為3秒，當然這裡也包含了死鎖的情況，死鎖不超過3秒，這裡是透過系統鎖超時來進行判斷的，當然blocked 的情況也是3秒內解決。

但在一些傳統性的單位，也有另外的一種配置，innodb_deadlock_detect =ON 並且因為程式編制和需求的原因blocked 的時間都設定的較長並不和網際網路設定的相同。 

這裡需要在不同的情況下來分析，同樣的設定給應用程式帶來的不同的問題。

這裡先從網際網路的方案來說，死鎖探測為0 innodb_lock_wait_timeout = 3 當然有的地方更短設定成1秒。具體什麼成因這裡就不討論了，同時這裡還有一個不同就是隔離級別，我們在每次測試使用不同的隔離級別來看看會有什麼影響。

1  innodb_lock_wait_timeout = 3  and innodb_deadlock_detect = OFF

innodb_deadlock_detect = OFF
innodb_lock_wait_timeout = 3 

表的狀態

mysql> select * from read_table;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  5 | 5    |
|  6 | 6    |
|  7 | 7    |
|  8 | 8    |
|  9 | 9    |
+----+------+
9 rows in set (0.00 sec)

1

2 

3

4

這裡我們可以看到，整體的操作中，隔離級別對於操作是沒有任何影響的，結果都是一樣，對於表中的鎖定的資料更新失敗。

innodb_deadlock_detect = ON
innodb_lock_wait_timeout = 86400

5

6

7

8

透過上面的幾個實驗，我們可以總結出以下一些結論

在MYSQL 配置中如果使用的 innodb_lock_wait_timeout =3 的配置的情況下，在很短的時間資料庫就能判斷出BLOCKED 或死鎖，在這樣的情況下，無論使用什麼隔離級別，那麼結果都是一樣的，都會是鎖超時的報錯和讓你重試的資訊。

或者你使用了自動檢測死鎖，同時將innodb_lock_wait_timeout = 更大的數值，那麼你得到的結果就與隔離級別有關了，如果是RR 的情況，你將會獲得 update 0 的結果，如果是RC 資料還在的情況下，你會獲得update 對應結果的結果，如果相關的行不在的情況下，獲得結果也是UPDATE 0 的結果。

另這裡也需要注意，在設定 innodb_lock_wait_timeout = 3 的情況下如果blocked 的情況不超過3秒，那麼結果還是和 innodb_lock_wait_time=無限大的情況類似。

最終基於以上的結果，應用程式是需要針對程式最終在執行語句後的結果進行判斷，到底是 update 0  還是 非0，並根據結果做出相關後續的操作。