我有一個導致鎖定超時的有問題的查詢：

UPDATE <some_table> SET col1=<some value> 
WHERE col1 IS NULL AND col2 > <some value> 
ORDER BY col2 
LIMIT 100    

我這裡有兩個問題：

1. 我有多個伺服器同時送出查詢，它們相互鎖定，有時我會出現鎖定超時甚至死鎖。最佳情況下，伺服器應該更新互斥行，因此根本不應該發生鎖定。有沒有辦法可以跳過更新中的任何鎖定行？
2. 如果我無法避免鎖定，並且我已經有 col1 的索引和 col2 的另一個索引，Innodb 會鎖定滿足 WHERE 子句中任何條件的所有行還是只鎖定同時滿足這兩個條件的行？如果答案是前者，我可以一起為兩列添加索引還是我還需要刪除我擁有的索引（分別為每列）？

在 each 之前，您需要自己鎖定所有行UPDATE。

請參閱MySQL 文件SELECT ... LOCK FOR UPDATE。這將對您通過的所有行執行排他鎖。然後，您可以針對錶執行所需的 UPDATE。

在您的特定情況下，您可以這樣做：

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100    

索引

• 您應該完全索引表以支持您將查詢數據的所有可能方式。SELECT ... FOR UPDATE儘管如此，您必須在和之間交替UPDATE。
• 由於您同時擁有row1和row2在 WHERE 子句中，因此您應該有一個包含兩列的索引。
• 有一個警告：如果這些列被索引，預計會有些慢，因為列正在更新並且 BTREE 索引頁正在每行更新。您還應該期望 ibdata1 的插入緩衝區部分快速增長（請參閱 InnoDB 地圖）

我有很多關於SELECT ... FOR UPDATE和的文章SELECT ... LOCK IN SHARED MODE。

• 我通過@RedBlueThing完成的案例研究
  • 偶爾慢查詢的原因？
  • 無法破譯 innodb 狀態日誌中的死鎖
  • 這兩個查詢如果按順序執行會導致死鎖嗎？
• Aug 08, 2011: InnoDB 死鎖是 INSERT/UPDATE/DELETE 獨有的嗎？
• Oct 18, 2011:防止使用 SELECT…LOCK IN SHARE MODE 的 php 應用程序中的 mysql 死鎖
• Jan 02, 2012:鎖定分享模式
• Nov 19, 2012:死鎖資訊在innodb 狀態頁面可以保存多久？
• Dec 13, 2012: MySQL InnoDB 在刪除時鎖定主鍵，即使在 READ COMMITTED
• Feb 03, 2013: MySQL InnoDB 鎖定組合的 UPDATE-JOIN 語句
• Mar 12, 2013:如何防止我的應用程序發生死鎖？

更新 2013-03-17 19:21 EDT

由於您有 9 個 WebServers 訪問數據庫伺服器，請試試這個

在 WebServer1 上執行

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 0,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 0,100;  

在 WebServer2 上執行

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 100,100;  

在 WebServer3 上執行

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 200,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 200,100;  

一路到WebServer9，執行

SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 800,100;
UPDATE <some_table> SET row1=<some value> 
WHERE row1 IS NULL AND row2 > <some value> 
ORDER BY row2 LIMIT 800,100;  

您必須放置一些 PHP 標頭檔，以唯一標識哪台機器執行哪個版本的查詢。

引用自：https://dba.stackexchange.com/questions/36885