MySQL中如何書(shū)寫(xiě)update避免表鎖

MySQL中如何書(shū)寫(xiě)update避免表鎖，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

今天，我來(lái)說(shuō)另外一個(gè)面試題。為什么推薦 MySQL 的 update 語(yǔ)句中 where 條件要有主鍵？

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

不是的，能更新，也能使用。但是我不建議你們這樣做。因?yàn)槲覀兇蠖鄶?shù)人使用 MySQL 都使用的是 innodb 存儲(chǔ)引擎，它是支持事務(wù)的。如果你的 where 條件不加主鍵，那么 innodb 的行級(jí)鎖就可能變成表級(jí)鎖。如果升級(jí)為表級(jí)鎖，那么并發(fā)性就將大打折扣了。

行鎖升級(jí)為表鎖與事務(wù)的隔離級(jí)別有關(guān)，因?yàn)槭聞?wù)的隔離性是靠加鎖來(lái)實(shí)現(xiàn)的，而加鎖不當(dāng)勢(shì)必會(huì)影響并發(fā)。

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

常用的索引有三類：主鍵、唯一索引、普通索引。主鍵我就不再細(xì)說(shuō)，自帶最高效的索引屬性；唯一索引指的是該屬性值重復(fù)率為 0，一般可作為業(yè)務(wù)主鍵，例如訂單號(hào)；普通索引 與前者不同的是，屬性值的重復(fù)率大于 0，不能作為唯一指定條件，例如購(gòu)買(mǎi)用戶的姓名。今天我主要想說(shuō)的是“普通索引對(duì)并發(fā)的影響”。

沒(méi)有索引的情況，我就不說(shuō)了，那對(duì)并發(fā)來(lái)說(shuō)肯定是災(zāi)難，死鎖估計(jì)是常有的事。

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

因?yàn)橹麈I是唯一索引，你用其他唯一索引也可以，但是一般的表，可能只有主鍵才是唯一的。所以，我建議你更新的時(shí)候，記住加上主鍵就行了。

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

下面我們通過(guò)一個(gè)簡(jiǎn)單的例子來(lái)看一下，普通索引的情況。

相關(guān)建表語(yǔ)句，索引，和數(shù)據(jù)如下所示：

然后取消事務(wù)自動(dòng)提交 set autocommit = off;

當(dāng)我們表里面創(chuàng)建時(shí)間重復(fù)率比較高的時(shí)候。分別開(kāi)啟兩個(gè)窗口，兩個(gè)事務(wù)。

為了演示，你可以把數(shù)據(jù)量加多點(diǎn)，比如 03-01 和 03-02 的數(shù)據(jù)各 10 萬(wàn)條。

依次執(zhí)行兩個(gè)窗口中的 SQL，你會(huì)發(fā)現(xiàn)，其中一個(gè)窗口中的更新失敗了。提示：

看似這兩個(gè)事務(wù)不互相干，但是在其中一個(gè)事務(wù)中更新自己鎖定的數(shù)據(jù)失敗后，應(yīng)該能說(shuō)明在此時(shí)引發(fā)了表鎖。這是在非主鍵索引或者說(shuō)是唯一索引，并且索引數(shù)據(jù)重復(fù)量比較高的情況下，你的更新發(fā)生量表鎖。并發(fā)能力就會(huì)大大下降！

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

在我們的電商系統(tǒng)中，這樣的代碼并不少。在一些熱門(mén)商品和秒殺、優(yōu)惠、打折等活動(dòng)中經(jīng)常會(huì)發(fā)生一些莫名其妙的異常，導(dǎo)致用戶體驗(yàn)大打折扣。

上面的測(cè)試數(shù)據(jù)，你把它們?nèi)縿h除，然后再新增一些數(shù)據(jù)，這些數(shù)據(jù)中在 create_time 重復(fù)率為 0 的情況下，你會(huì)發(fā)現(xiàn)兩個(gè)事務(wù)就都能成功了。這說(shuō)明它們這時(shí)用的應(yīng)該是行級(jí)鎖，效率更高。

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。