MySQL面試題，如何書寫 update 避免表鎖？

點選上方“業餘草”，選擇“置頂公眾號”

第一時間獲取技術乾貨和業界資訊！

 

                                                                        

前面我推薦了極客時間的 Java 高併發課程，很多人根據這篇文章《2019 Java 高併發學習路線圖和必會的 50 道面試題！》的介紹買了課程，我希望大家能夠認真的看！別把錢浪費了！

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

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

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

之前也有網友在我的文章裡評論說，行鎖升級為表鎖與事務的隔離級別有關，因為事務的隔離性是靠加鎖來實現的，而加鎖不當勢必會影響併發。

不一樣的鎖，支援的併發也是不一樣的。而最終加什麼樣的鎖，與索引也有莫大的關係，因此，可以說採用什麼樣的索引決定了支援多少併發。

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

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

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

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

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

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

相關建表語句，索引，和資料如下所示：

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

當我們表裡面建立時間重複率比較高的時候。分別開啟兩個視窗，兩個事務。

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

 

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

 

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

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

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

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

以上，測試說明在更新資料時，儘量使用主鍵或唯一索引。但是唯一索引並不少每個表都有的，而主鍵必須是每個表都必須有的。所以，我建議你們在更新資料時，都帶上主鍵！

原文連結：https://www.xttblog.com/?p=3946

10T技術資源大放送！包括但不限於：C/C++，Linux，Python，Java，PHP，人工智慧，GO等等。在公眾號內回覆對應關鍵字或框架名字，即可免費獲取！！

 你再主動一點點   我們就有故事了