重溫MySQL鎖機制

文章目錄

• 「鎖定
• • #共享鎖（讀鎖，讀寫互斥，讀讀互不影響）
排他鎖（寫鎖，獨佔鎖）
• 共享鎖和排他鎖的區別
• • 表格鎖定
  • • 意圖鎖定：意向共享鎖定與意圖排他鎖定
  • ##為什麼需要加入意圖鎖定
  單一表鎖定
• 全域表鎖定
• #行鎖定
• 行鎖升級為表鎖（行鎖實際上是給索引加鎖，如果沒用索引而全表掃描，則會給全表加鎖）
• 修改、刪除某一行記錄，且未提交交易時，該行會一直被行鎖定鎖定
• 記錄鎖定
  • 間隙鎖定（Gap Locks）
  • 間隙鎖定的作用：防止幻讀取
  • innodb自動使用間隙鎖的條件
  間隙鎖定鎖定的區域
• next-key鎖定【臨鍵鎖定】

相關免費學習推薦：

鎖定

鎖定分類

（1）從效能分為：樂觀鎖和悲觀鎖定

• （2）從資料庫操作類型分為：讀鎖（共享鎖）、寫鎖（排他鎖） 【讀鎖和寫鎖都屬於悲觀鎖】
• 讀取鎖定（共享鎖定)：針對相同行記錄，多個讀取操作可以同時進行，但任何事務都不能進行

寫鎖定（排他鎖)：獲取排他鎖的事務既能讀取數據，又能修改數據。在取得到寫鎖的交易為完成之前，會阻塞其他交易取得寫鎖或讀鎖。

（3）從顆粒度分割為，表鎖和行鎖

#注意：

（2）如果sql語句使用了行鎖，當SQL未使用索引而使用全表掃描的時候，行級鎖定會變成表鎖。

（3）鎖定只有在執行commit或rollback的時候才會釋放，並且所有的鎖都是在同一時刻被釋放。 （4）Innodb引擎執行普通的修改、刪除sql語句時，會為修改和刪除的行加行鎖定。

共享鎖（讀鎖，讀寫互斥，讀讀互不影響）

• 交易A使用共享鎖獲取了某條（或某些）記錄時，事務B可以讀取這些記錄，可以繼續添加共享鎖，但是不能修改這些記錄（當事務C對這些資料修改或刪除時，會進入阻塞狀態，直至鎖等待超時或事務A提​​交）

如何使用共享鎖定和釋放共享鎖定

• # 加锁SELECT ... LOCK IN SHARE MODE# 释放锁commit;rollback;

  登入後複製
  
  
作用
• SELECT … LOCK IN SHARE MODE在讀取的若干行記錄加上共享鎖（share lock），其他交易只能對這些行查詢而不能修改這些記錄。多個事務可以對相同的行記錄加上共享鎖定，因此獲取了共享鎖的事務不一定能修改行資料

  
  

  使用場景：
讀取結果集的最新版本，同時防止其他交易產生更新該結果集

排他鎖（寫鎖，獨佔鎖）

• select … for update在對讀取的行記錄加排他鎖，只允許獲取到該排他鎖的事務修改該行記錄，防止其他事務對該行進行修改，就像普通的update語句執行時會加行鎖一樣。

如何使用排他鎖定

# 加排他锁select ... for update //排他锁 # 释放锁commit;rollback;

共享鎖定和排他鎖定的區別

多個事務可以對相同行資料加共享鎖定。 （2）給指定行加了共享鎖的事務，不一定能修改該行數據，因為其他事務也可能對該行加了共享鎖或排他鎖； 給指定行加了排他鎖的事務，則可以修改加了排他鎖的該行資料

表鎖定

#使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務性儲存引擎。 表鎖一般在資料遷移時使用。

意向鎖定：意圖共享鎖定和意圖排他鎖定

某行加上共享鎖定的前提是：該行資料所在表會先取得到意向共享鎖定。 某行加排他鎖的前提是：該行資料所在表會先取得到意向排他鎖。

注意：意向共享鎖定和意向排他鎖都是表鎖，無法手動建立。

意向锁是为了告知mysql该表已经存在数据被加锁，而不需要逐行扫描是否加锁，提搞加锁的效率。

单个表锁定

lock tables saas.goods read,saas.account write;　　// 给saas库中的goods表加读锁,account表加写锁unlock tables;   //解锁

全局表锁定

FLUSH TABLES WITH READ LOCK; 　　// 所有库所有表都被锁定只读unlock tables;         //解锁

注意： 在客户端和数据库断开连接时，都会隐式的执行unlock tables。如果要让表锁定生效就必须一直保持连接。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
  行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

行锁

• 行锁是锁一行或者多行记录
• MySQL的行锁是基于索引，行锁是加在索引上，而不是加在行记录上的。
  
  如上图所示，数据库中有1个主键索引和1个普通索引，图中的sql语句是基于普通索引查询，命中4条记录，此时一把行锁就锁定两条记录，而其他事务修改这两条记录中的任意一条，都会一直阻塞【获取锁的事务没有执行commit之前】，下图就是上图没有执行commit语句时的情况。
  

行锁升级为表锁（行锁实际是给索引加锁，如果没用索引而全表扫描，则会给全表加锁）

上图中where条件中，虽然template_name建立普通索引，但使用or关键字，导致template_name的索引失效，从而进行了全表扫描，锁定了整张表。

修改、删除某一行记录，且未提交事务时，该行会一直被行锁锁定

窗口1中删除某一行，但没有提交。窗口2中更新该行会一直处于阻塞中。

记录锁

• 行锁：行锁是命中索引，一把锁锁的是一张表的一条记录或多条记录
• 记录锁：记录锁是在行锁的衍生锁，记录锁锁的是表中的某一条记录，记录锁出现的条件必须是：精确命中索引，且索引是唯一索引（比如主键id、唯一索引列）。

间隙锁（Gap Locks）

经典参考文章

间隙锁的作用：防止幻读

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止止间隙内有新数据被插入
（2）防止范围内已存在的数据被更新

innodb自动使用间隙锁的条件

（1）数据隔离级别必须为可重复读
（2）检索条件必须使用索引（没有使用索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。下图中，where number=5的话，那么间隙锁的区间范围为[4,11]；

session 1:start  transaction ;触发间隙锁的方式1：select  * from news where number=4 for update ;触发间隙锁的方式2：update news set number=3 where number=4; session 2:start  transaction ;insert into news value(2,4);#（阻塞）insert into news value(2,2);#（阻塞）insert into news value(4,4);#（阻塞）insert into news value(4,5);#（阻塞）insert into news value(7,5);#（执行成功）insert into news value(9,5);#（执行成功）insert into news value(11,5);#（执行成功）

next-key锁【临键锁】

next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身。InnoDB默认加锁方式是next-key 锁。

select * from news where number=4 for update ;

next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁，即next-key锁的锁定的范围为（2,4],(4,5]。

记录锁、间隙锁、临间锁的区别

update news  set number=0 where id>15

sql默认加的是next-key锁。根据上图，next-key锁的区间为（-∞,1],（1,5]，（5,9]，（9，11]，(11,+∞），上面id>15，实际上next-key锁是加在[11，+∞）这个范围内，而不是（15，+∞）这个范围内。注意：需要使用锁的字段必须加索引，因为锁是加在索引上的，没有索引则加的表锁。

相关免费学习推荐：mysql数据库(视频)

以上是重溫MySQL鎖機制的詳細內容。更多資訊請關注PHP中文網其他相關文章！