mysql行級鎖怎么使用 mysql行鎖和表鎖怎么實(shí)現(xiàn)

MYSQL行級鎖使用方法

當(dāng) web 日志中出現(xiàn)行鎖超時錯誤后，很多開發(fā)都會找我來排查問題，這里說下問題定位的難點(diǎn)！

創(chuàng)新互聯(lián)建站專業(yè)提供成都主機(jī)托管四川主機(jī)托管成都服務(wù)器托管四川服務(wù)器托管，支持按月付款！我們的承諾：貴族品質(zhì)、平民價格，機(jī)房位于中國電信/網(wǎng)通/移動機(jī)房，BGP機(jī)房服務(wù)器托管服務(wù)有保障！

1. MySQL 本身不會主動記錄行鎖等待的相關(guān)信息，所以無法有效的進(jìn)行事后分析。

2. 鎖爭用原因有多種，很難在事后判斷到底是哪一類問題場景，尤其是事后無法復(fù)現(xiàn)問題的時候。

3. 找到問題 SQL 后，開發(fā)無法有效從代碼中挖掘出完整的事務(wù)，這也和公司框架-產(chǎn)品-項(xiàng)目的架構(gòu)有關(guān)，需要靠 DBA 事后采集完整的事務(wù) SQL 才可以進(jìn)行分析。

MySQL - for update 行鎖 表鎖

for update 的作用是在查詢的時候?yàn)樾屑由吓潘i，當(dāng)一個事務(wù)的操作未完成時候，其他事務(wù)可以讀取但是不能寫入或更新。

它的典型使用場景是 高并發(fā)并且對于數(shù)據(jù)的準(zhǔn)確性有很高要求 ，比如金錢、庫存等，一般這種操作都是很長一串并且開啟事務(wù)的，假如現(xiàn)在要對庫存進(jìn)行操作，在剛開始讀的時候是1，然后馬上另外一個進(jìn)程將庫存更新為0了，但事務(wù)還沒結(jié)束，會一直用1進(jìn)行后續(xù)的邏輯，就會有問題，所以需要用for upate 加鎖防止出錯。

行鎖的具體實(shí)現(xiàn)算法有三種：record lock、gap lock以及next-key lock。

只在可重復(fù)讀或以上隔離級別下的特定操作才會取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 時，除了基于唯一索引的查詢之外，其它索引查詢時都會獲取 gap lock 或 next-key lock，即鎖住其掃描的范圍。主鍵索引也屬于唯一索引，所以主鍵索引是不會使用 gap lock 或 next-key lock

for update 僅適用于InnoDB，并且必須開啟事務(wù)，在begin與commit之間才生效。

select 語句默認(rèn)不獲取任何鎖，所以是可以讀被其它事務(wù)持有排它鎖的數(shù)據(jù)的！

InnoDB 既實(shí)現(xiàn)了行鎖，也實(shí)現(xiàn)了表鎖。

當(dāng)有明確指定的主鍵/索引時候，是行級鎖，否則是表級鎖

假設(shè)表 user，存在有id跟name字段，id是主鍵，有5條數(shù)據(jù)。

明確指定主鍵，并且有此記錄，行級鎖

無主鍵/索引，表級鎖

主鍵/索引不明確，表級鎖

明確指定主鍵/索引，若查無此記錄，無鎖

參考博文：

巧用MySQL InnoDB引擎鎖機(jī)制解決死鎖問題[2]

索引 KEY_TSKTASK_MONTIME (STATUS_ID MON_TIME)

分析 涉及的兩條語句應(yīng)該不會涉及相同的TSK_TASK記錄 那為什么會造成死鎖呢？

查詢MySQL官網(wǎng)文檔 發(fā)現(xiàn)這跟MySQL的索引機(jī)制有關(guān) MySQL的InnoDB引擎是行級鎖 我原來的理解是直接對記錄進(jìn)行鎖定 實(shí)際上并不是這樣的

要點(diǎn)如下:

不是對記錄進(jìn)行鎖定 而是對索引進(jìn)行鎖定

在UPDATE DELETE操作時 MySQL不僅鎖定WHERE條件掃描過的所有索引記錄 而且會鎖定相鄰的鍵值 即所謂的next key locking

如語句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID 會鎖定所有主鍵大于等于 的所有記錄 在該語句完成之前 你就不能對主鍵等于 的記錄進(jìn)行操作

當(dāng)非簇索引(non cluster index)記錄被鎖定時 相關(guān)的簇索引(cluster index)記錄也需要被鎖定才能完成相應(yīng)的操作

再分析一下發(fā)生問題的兩條SQL語句 就不難找到問題所在了

當(dāng) update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where STATUS_ID= and MON_TIME

假設(shè) update TSK_TASK set STATUS_ID= UPDATE_TIME=now () where ID in ( ) 幾乎同時執(zhí)行時 本語句首先鎖定簇索引(主鍵) 由于需要更新STATUS_ID的值 所以還需要鎖定KEY_TSKTASK_MONTIME 的某些索引記錄

這樣第一條語句鎖定了KEY_TSKTASK_MONTIME 的記錄 等待主鍵索引 而第二條語句則鎖定了主鍵索引記錄 而等待KEY_TSKTASK_MONTIME 的記錄 在此情況下 死鎖就產(chǎn)生了

筆者通過拆分第一條語句解決死鎖問題

先查出符合條件的ID select ID from TSK_TASK where STATUS_ID= and MON_TIME date_sub(now() INTERVAL minute) 然后再更新狀態(tài) update TSK_TASK set STATUS_ID= where ID in (… )

至此 死鎖問題徹底解決

lishixinzhi/Article/program/MySQL/201311/29601

名稱欄目：mysql行級鎖怎么使用 mysql行鎖和表鎖怎么實(shí)現(xiàn)
網(wǎng)址分享：http://www.muchs.cn/article48/ddcedhp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供動態(tài)網(wǎng)站、、品牌網(wǎng)站設(shè)計(jì)、小程序開發(fā)、網(wǎng)站設(shè)計(jì)公司、域名注冊

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)