如何在不鎖定搜尋遇到的每條記錄的情況下更新數據？

我們有以下數據庫規範：

version: 10.1.47-MariaDB-0ubuntu0.18.04.1
innodb_version: 5.6.49-89.0
tx_isolation: READ-COMMITTED
innodb_strict_mode: ON
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
autocommit: ON

我們有一個問題是我們在表中鎖定了太多的數據條目，這導致了死鎖。這是我們的例子。

我們執行的第一個查詢：

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3 = 3;

我們在所有列上都有單列索引。根據文件：這將在搜尋遇到的每條記錄上設置一個獨占的下一個鍵鎖。

所以下UPDATE一條語句將等待前一條語句，儘管匹配的最終結果記錄不重疊。這是因為索引在col1.

UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

我只想鎖定最終結果記錄，而不是“搜尋遇到的每條記錄”。按主鍵將查詢拆分為 aSELECT和 anUPDATE會給我這個結果，但隨後可能會在 aSELECT和UPDATE. 在事務中組合它們也無濟於事，因為每個 SELECT 都是一致的讀取。

您將如何設計這樣的流程，我可以在其中執行UPDATE語句，而無需鎖定搜尋遇到的每條記錄？

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3  = 3;

需要這個綜合指數：

INDEX(col1, col2, col3)  -- in any order

因此，如果您以col1, col2（以任一順序）開始，該索引也可以很好地工作

UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

如果“我們在所有列上都有索引”是指單列索引，那麼這些索引就被浪費了。MySQL 將只使用一個索引。它會在 (col1), (col2), (col3) 中挑選最有選擇性的。然後它將掃描所有行，例如col1 = 1. 這可能比您需要的行數多得多UPDATE。

使用我推薦的複合索引，它將直接進入匹配所有WHERE子句的行。這將減少很多行，從而降低死鎖的可能性。此外，它會更快，這也減少了死鎖的機會。

無論如何，您應該檢查錯誤並重新執行任何遇到死鎖的事務。

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