轉(zhuǎn)載自??insert ... on duplicate key update產(chǎn)生death lock死鎖原理

前言

編輯

????我們在實際業(yè)務(wù)場景中，經(jīng)常會有一個這樣的需求，插入某條記錄，如果已經(jīng)存在了則更新它如果更新日期或者某些列上的累加操作等，我們肯定會想到使用INSERT ... ON DUPLICATE KEY UPDATE語句，一條語句就搞定了查詢是否存在和插入或者更新這幾個步驟，但是使用這條語句在msyql的innodb5.0以上版本有很多的陷阱，即有可能導(dǎo)致death lock死鎖也有可能導(dǎo)致主從模式下的replication產(chǎn)生數(shù)據(jù)不一致。

正文

????正如前言說的那樣，在實際業(yè)務(wù)中，曾經(jīng)有過一個需求就是插入一條業(yè)務(wù)數(shù)據(jù)，如果不存在則新增，存在則累加更新某一個字段的值，于是乎就想到了使用insert... on duplicate key update這個語句，但是有一天去測試環(huán)境查看錯誤日志時，卻發(fā)現(xiàn)了在多個事務(wù)并發(fā)執(zhí)行同一條insert...on duplicate key update 語句時，也就是insert的內(nèi)容相同時，發(fā)生 了死鎖。

??對于insert...on duplicate key update這個語句會引發(fā)dealth lock問題，官方文檔也沒有相關(guān)描述，只是進(jìn)行如下描述：

An?INSERT ... ON DUPLICATE KEY UPDATE?statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)

也就是如果一個表定義有多個唯一鍵或者主鍵時，是不安全的，這又引發(fā)了以一個問題，見https://bugs.mysql.com/bug.php?id=58637

也就是

當(dāng)mysql執(zhí)行INSERT ON DUPLICATE KEY的 INSERT時，存儲引擎會檢查插入的行是否會產(chǎn)生重復(fù)鍵錯誤。如果是的話，它會將現(xiàn)有的 行返回給mysql，mysql會更新它并將其發(fā)送回存儲引擎。當(dāng)表具有多個唯一或主鍵時，此語句對存儲引擎檢查密鑰的順序非常敏感。根據(jù)這個順序， 存儲引擎可以確定不同的行數(shù)據(jù)給到mysql，因此mysql可以更新不同的行。存儲引擎檢查key的順序不是確定性的。例如，InnoDB按照索引添加到 表的順序檢查鍵。

?????insert ... on duplicate key 在執(zhí)行時，innodb引擎會先判斷插入的行是否產(chǎn)生重復(fù)key錯誤，如果存在，在對該現(xiàn)有的行加上S（共享鎖）鎖，如果返回該行數(shù)據(jù)給mysql,然后mysql執(zhí)行完duplicate后的update操作，然后對該記錄加上X（排他鎖），最后進(jìn)行update寫入。

????如果有兩個事務(wù)并發(fā)的執(zhí)行同樣的語句，那么就會產(chǎn)生death lock，如：

?

具體的bug描述見：https://bugs.mysql.com/bug.php?id=52020

https://bugs.mysql.com/bug.php?id=58637

編輯

https://bugs.mysql.com/bug.php?id=21356

?

解決辦法：

1、盡量不對存在多個唯一鍵的table使用該語句

2、在有可能有并發(fā)事務(wù)執(zhí)行的insert 的內(nèi)容一樣情況下不使用該語句

總結(jié)

以上是生活随笔為你收集整理的insert ... on duplicate key update产生death lock死锁原理的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。