MySQL中如何書寫update避免表鎖

MySQL中如何書寫update避免表鎖，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

今天，我來說另外一個面試題。為什么推薦 MySQL 的 update 語句中 where 條件要有主鍵？

看到這個問題的朋友，我相信不少人有疑問，我 where 不加主鍵還不能更新了？

不是的，能更新，也能使用。但是我不建議你們這樣做。因為我們大多數人使用 MySQL 都使用的是 innodb 存儲引擎，它是支持事務的。如果你的 where 條件不加主鍵，那么 innodb 的行級鎖就可能變成表級鎖。如果升級為表級鎖，那么并發性就將大打折扣了。

行鎖升級為表鎖與事務的隔離級別有關，因為事務的隔離性是靠加鎖來實現的，而加鎖不當勢必會影響并發。

不一樣的鎖，支持的并發也是不一樣的。而最終加什么樣的鎖，與索引也有莫大的關系，因此，可以說采用什么樣的索引決定了支持多少并發。

常用的索引有三類：主鍵、唯一索引、普通索引。主鍵我就不再細說，自帶最高效的索引屬性；唯一索引指的是該屬性值重復率為 0，一般可作為業務主鍵，例如訂單號；普通索引 與前者不同的是，屬性值的重復率大于 0，不能作為唯一指定條件，例如購買用戶的姓名。今天我主要想說的是“普通索引對并發的影響”。

沒有索引的情況，我就不說了，那對并發來說肯定是災難，死鎖估計是常有的事。

為什么我推薦 update 中 where 條件加入主鍵呢？

因為主鍵是唯一索引，你用其他唯一索引也可以，但是一般的表，可能只有主鍵才是唯一的。所以，我建議你更新的時候，記住加上主鍵就行了。

你只需要記住主鍵和唯一索引是行鎖，其他索引并不一定是行鎖，很可能是表鎖。這樣，死鎖的概率就非常的高，并發也就隨之下降。

下面我們通過一個簡單的例子來看一下，普通索引的情況。

相關建表語句，索引，和數據如下所示：

然后取消事務自動提交 set autocommit = off;

當我們表里面創建時間重復率比較高的時候。分別開啟兩個窗口，兩個事務。

為了演示，你可以把數據量加多點，比如 03-01 和 03-02 的數據各 10 萬條。

依次執行兩個窗口中的 SQL，你會發現，其中一個窗口中的更新失敗了。提示：

看似這兩個事務不互相干，但是在其中一個事務中更新自己鎖定的數據失敗后，應該能說明在此時引發了表鎖。這是在非主鍵索引或者說是唯一索引，并且索引數據重復量比較高的情況下，你的更新發生量表鎖。并發能力就會大大下降！

你們可以試一下，如果此時使用主鍵或唯一索引會不會這樣。

在我們的電商系統中，這樣的代碼并不少。在一些熱門商品和秒殺、優惠、打折等活動中經常會發生一些莫名其妙的異常，導致用戶體驗大打折扣。

上面的測試數據，你把它們全部刪除，然后再新增一些數據，這些數據中在 create_time 重復率為 0 的情況下，你會發現兩個事務就都能成功了。這說明它們這時用的應該是行級鎖，效率更高。

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。