欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

InnoDB中不同SQL語句設(shè)置鎖的情況詳解

 更新時間:2024年01月02日 09:31:11   作者:luffylv  
這篇文章主要介紹了InnoDB中不同SQL語句設(shè)置鎖的情況詳解,在Mysql中,鎖定讀、更新、刪除操作通常會對SQL語句處理過程中掃描到的每條索引記錄設(shè)置記錄鎖,需要的朋友可以參考下

前言

在Mysql中,鎖定讀、更新、刪除操作通常會對SQL語句處理過程中掃描到的每條索引記錄設(shè)置記錄鎖。語句中是否存在排除該行的WHERE條件并不重要。InnoDB不記得確切的WHERE條件,只知道掃描了哪些索引范圍。next-key鎖通常會阻塞往記錄之前的間隙插入數(shù)據(jù)??梢燥@示禁用間隙鎖,使得next-key鎖不可用。事務(wù)的隔離級別也會影響鎖的設(shè)置。

如果在搜索中使用了二級索引,并且在二級索引上設(shè)置的是排他鎖,InnoDB還會檢索相應(yīng)的聚集索引,并在聚集索引上也設(shè)置鎖。如果在SQL語句中沒有使用合適的索引,此時MySQL必須掃描整個表來處理該語句,表中的每一行都會被鎖住,這將導(dǎo)致其他用戶往該表的插入操作全部阻塞。所以,給表創(chuàng)建合適的索引非常重要,可以在查詢時不掃描多余的行。

InnoDB 設(shè)置特定類型的鎖如下

SELECT ... FROM:

是一致性讀取,該語句讀取的是數(shù)據(jù)庫快照并且不設(shè)置鎖,除非事務(wù)的隔離級別設(shè)置為SERIALIZABLE。在SERIALIZABLE隔離級別下執(zhí)行該語句會在滿足條件的索引記錄上設(shè)置共享next-key鎖。但是,在使用唯一索引查找唯一行的語句時,只需要索引記錄鎖。

SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE:

掃描行時為行加鎖,對于不滿足條件的行會釋放鎖(例如,不滿足WHERE字句中給出的條件)。但是在某些情況下,可能不會立即釋放鎖,因為在查詢期間,結(jié)果行與原始源之間的關(guān)系會丟失。例如,在UNION查詢中,可能會先將表中掃描和鎖定的行插入到臨時表中,然后在評估它們是否符合結(jié)果集。在這種情況下,臨時表中的行與原始表中的行的關(guān)系丟失,所以要在查詢執(zhí)行結(jié)束后才能解鎖原始表中的行。

SELECT ... LOCK IN SHARE MODE:

給查詢過程中遇到的所有索引記錄設(shè)置共享的next-key鎖。但是,對于使用唯一索引鎖定行以搜索唯一行的語句,只需要使用記錄鎖。

SELECT ... FOR UPDATE:

給查詢過程中遇到的每條記錄設(shè)置排他的next-key 鎖。但是,對于使用唯一索引鎖定行以搜索唯一行的語句,只需要使用記錄鎖。對于查詢過程中遇到的索引記錄,SELECT ... FOR UPDATE會阻塞其他會話執(zhí)行SELECT ... LOCK IN SHARE MODE或在某個事務(wù)隔離級別下讀取數(shù)據(jù)。一致性讀將忽略存在于讀取視圖中的記錄上的任何鎖。

UPDATE ... WHERE ... :

在搜索過程中遇到的每條記錄上設(shè)置排他的next-key鎖。如果WHERE子句中使用了唯一索引,則將使用記錄鎖。如果UPDATE的是聚集索引,則與其相關(guān)的二級索引也會被隱式上鎖。當(dāng)插入新的二級索引記錄之前執(zhí)行重復(fù)性檢查掃描和正在插入新的二級索引記錄時,UPDATE操作會對受影響的二級索引記錄設(shè)置共享鎖。

DELETE FROM ... WHERE ...:

對搜索遇到的每條記錄上設(shè)置排他的next-key鎖。但是,對于使用唯一索引鎖定行以搜索唯一行的語句,只需要使用記錄鎖。

INSERT:

對插入的行設(shè)置排他鎖。這個鎖是記錄鎖,而不是next-key鎖(也就是說,你沒有間隙鎖),并且不會阻塞其他會話往插入行之前的間隙插入記錄。插入語句在執(zhí)行插入前會先獲得插入意向鎖。插入意向鎖表示插入的意圖,多個事務(wù)往相同間隙中的不同位置插入時則他們無需相互等待。假設(shè)有值為4和7的索引記錄。若兩個單獨事務(wù)分別插入5和6,在獲得插入行的排它鎖之前會分別獲得4和7之間間隙的插入意向鎖。因為這兩個事務(wù)插入的行不同,所以不會阻塞。

如果出現(xiàn)重復(fù)鍵錯誤,則對重復(fù)索引記錄加共享鎖。此時,如果有一個事務(wù)已經(jīng)獲得了該行的排他鎖,此時,有多個事務(wù)試圖插入同一行, 那么共享鎖的使用可能會導(dǎo)致死鎖。

如果有一個事務(wù)要刪除該行,此時,有多個事務(wù)試圖插入同一行,也有可能發(fā)生這種情況。

示例

假設(shè)InnoDB中有如下結(jié)構(gòu)表t1:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

假設(shè)有三個事務(wù)按順序執(zhí)行以下操作:

Session1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session1:

ROLLBACK;

事務(wù)1的插入操作獲取該行的排他鎖。事務(wù)2和事務(wù)3的操作都會導(dǎo)致重復(fù)鍵錯誤,他們都請求該行的共享鎖。當(dāng)事務(wù)1回滾時,它釋放該行上的排他鎖,事務(wù)2和事務(wù)3獲得該行的共享鎖。此時,事務(wù)2和事務(wù)3就會出現(xiàn)死鎖:由于另一方持有共享鎖,雙方都不能獲得該行的排他鎖。

如果表中已經(jīng)包含鍵值為1的行,并且三個會話按順序執(zhí)行以下操作也會出現(xiàn)類似的情況:

Session1:

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

Session2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session1:

COMMIT;

事務(wù)1的第一個操作獲取該行的排他鎖。事務(wù)2和事務(wù)3的操作都會導(dǎo)致重復(fù)鍵錯誤,此時這兩個事務(wù)都請求該行的共享鎖。當(dāng)事務(wù)1提交時,它釋放該行上的排他鎖,并且授權(quán)事務(wù)2和事務(wù)3的共享鎖請求。此時,事務(wù)2和事務(wù)3就會出現(xiàn)死鎖:由于另一方持有共享鎖,雙方都不能獲得該行的排他鎖。

INSERT ... ON DUPLICATE KEY UPDATE:

不同于簡單的INSERT,當(dāng)重復(fù)鍵錯誤發(fā)生時,請求的是該行的排他鎖而不是共享鎖。如果是主鍵索引的值重復(fù),則采用排他的記錄鎖;如果是唯一索引的值重復(fù),則采用next-key鎖。

REPLACE:如果唯一鍵中沒有該值,和REPLACE操作和INSERT操作一樣。否則,需先獲得該行排他的next-key鎖。

INSERT INTO T SELECT ... FROM S WHERE ...:

對插入到T中的每一行設(shè)置排他的記錄鎖(沒有間隙鎖)。如果事務(wù)的隔離級別是讀已提交,或者啟用innodb_locks_unsafe_for_binlog并且事務(wù)的隔離級別不是SERIALZABLE,InnoDB會將S作為一致性讀進(jìn)行搜索(沒有鎖)。否則,InnoDB會對來自S的行設(shè)置共享的next-key鎖。如下情景中,InnoDB必須設(shè)置鎖:在使用基于語句的二進(jìn)制日志進(jìn)行回滾恢復(fù)時,每條SQL語句都必須以與原來完全相同的方式進(jìn)行。

CREATE TABLE ... SELECT ...

執(zhí)行SELECT語句時使用共享的next-key 鎖,或者作為一致性讀取,類似于INSERT ... SELECT。當(dāng)SELECT用于如下語句中時,InnoDB會對滿足條件的S表中的行設(shè)置共享的next-key鎖:REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...)。

當(dāng)初始化表中AUTO_INCREMENT列時,InnoDB會在與AUTO_INCREMENT關(guān)聯(lián)的索引的最后上設(shè)置排他鎖。在innodb_autoinc_lock_mode=0的情況下,當(dāng)訪問auto-increment計數(shù)器時,InnoDB使用特殊的AUTO-INC表鎖模式,在該模式下當(dāng)前 SQL 語句的末尾獲取并持有鎖(而不是整個事務(wù)的結(jié)尾持有鎖)。當(dāng)某一事務(wù)持有AUTO-INC表鎖時,將阻塞其它事務(wù)往表中插入的操作。當(dāng)innodb_autoinc_lock_mode=1時,批量插入也會出現(xiàn)這種情況。當(dāng)innodb_autoinc_lock_mode=2時,不能使用表級鎖AUTO-INC。當(dāng)InnoDB獲取已初始化的AUTO_INCREMENT列的值時無需設(shè)置任何鎖。

如果在一個表上定義了一個外鍵約束,任何需要檢查約束條件的操作,如:插入,更新,刪除,都會對其檢查約束的過程中查看的記錄上設(shè)置共享的記錄鎖。InnoDB也會在約束失敗的情況下設(shè)置這些鎖。

LOCK TABLES

設(shè)置表鎖,該鎖是由InnoDB層之上的MySQL層設(shè)置的。如果innodb_table_locks = 1(默認(rèn)值)和autocommit = 0,InnoDB能夠識別表鎖,InnoDB層之上的MySQL層能夠識別行級鎖。否則,InnoDB 的自動死鎖檢測無法檢測到涉及表鎖的死鎖。此外,因為在這種情況下,更高的MySQL層識別不了行級鎖,所以,在另一個事務(wù)當(dāng)前持某表的行級鎖的情況下獲得該表的表鎖是有可能的。這并不會破壞事務(wù)的完整性。

LOCK TABLES

如果innodb_table_locks=1(默認(rèn)值),那么使用LOCK TABLE鎖表時,每個表上都會有兩個表鎖。除了MySQL層上的表鎖,還需要InnoDB層的表鎖??梢酝ㄟ^設(shè)置innodb_table_locks=0,避免獲取InnoDB層的表鎖。如果沒有獲得InnoDB層的表鎖,即使表中的一些記錄正被其他事務(wù)上鎖,LOCK TABLES操作依然可以完成。

在MySQL5.7中,如果innodb_table_locks=0,那么當(dāng)使用 LOCK TABLES ... WRITE給表顯示加鎖將不會起作用,當(dāng)讀操作或?qū)懖僮鲿r使用 LOCK TABLES ... WRITE給表隱式加鎖時(例如,通過觸發(fā)器)或使用LOCK TABLES ... READ時將會起作用。

當(dāng)事務(wù)提交或終止時,該事物將釋放其持有的所有InnoDB鎖。因此,在autocommit = 1的情況下使用LOCK TABLES沒有任何意義,因為獲得的InnoDB表鎖會立即被釋放。

不能在事務(wù)執(zhí)行中鎖定其他表,因為LOCK TABLES會隱式地執(zhí)行COMMIT和UNLOCK TABLES從而釋放表鎖。

到此這篇關(guān)于InnoDB中不同SQL語句設(shè)置鎖的情況詳解的文章就介紹到這了,更多相關(guān)InnoDB的SQL語句設(shè)置鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • mysql對binlog的處理說明

    mysql對binlog的處理說明

    Mysql和其它開源數(shù)據(jù)庫相比,具有更好的擴(kuò)展性。其主要原因是它提供了存儲引擎的開放接口。喜歡自己折騰數(shù)據(jù)庫的程序員可以從這個接口起步,打造有個性的數(shù)據(jù)庫。
    2011-07-07
  • 如何開啟mysql中的嚴(yán)格模式

    如何開啟mysql中的嚴(yán)格模式

    這篇文章介紹了如何開啟mysql中的嚴(yán)格模式,有需要的朋友可以參考一下
    2013-09-09
  • Mysql排序的特性詳情

    Mysql排序的特性詳情

    這篇文章主要介紹Mysql排序的特性,新寫了一個功能,自測和測試環(huán)境測試都沒問題,但在生產(chǎn)環(huán)境會出現(xiàn)偶發(fā)問題。于是,加班到12點一直排查問題,終于定位了的問題原因:Mysql Limit查詢優(yōu)化導(dǎo)致?,F(xiàn)抽象出問題模型及解決方案,分析給大家,避免大家踩坑,需要的朋友可以參考一下
    2021-10-10
  • Mysql實現(xiàn)null值排在最前/最后的方法示例

    Mysql實現(xiàn)null值排在最前/最后的方法示例

    這篇文章主要給大家介紹了關(guān)于Mysql實現(xiàn)null值排在最前/最后的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧
    2019-02-02
  • Mysql經(jīng)典的“8小時問題”

    Mysql經(jīng)典的“8小時問題”

    MySQL 的默認(rèn)設(shè)置下,當(dāng)一個連接的空閑時間超過8小時后,MySQL 就會斷開該連接,而 c3p0 連接池則以為該被斷開的連接依然有效。
    2015-04-04
  • MySQL觸發(fā)器使用過程詳解

    MySQL觸發(fā)器使用過程詳解

    觸發(fā)器,就是一種特殊的存儲過程。觸發(fā)器和存儲過程一樣是一個能夠完成特定功能、存儲在數(shù)據(jù)庫服務(wù)器上的SQL片段。本文將通過簡單的實力介紹一下觸發(fā)器的操作,需要的可以參考一下
    2023-03-03
  • 新手學(xué)習(xí)MySQL索引

    新手學(xué)習(xí)MySQL索引

    如果正確合理設(shè)計并且使用索引的MySQL是一輛蘭博基尼的話,那么沒有索引的MySQL就是一個人力三輪車。通常大型網(wǎng)站單日就可能會產(chǎn)生幾十萬甚至幾百萬的數(shù)據(jù),沒有索引查詢會變的非常緩慢,下面小編來帶大家了解下索引
    2019-05-05
  • 親手教你SQLyog12.08安裝詳細(xì)教程

    親手教你SQLyog12.08安裝詳細(xì)教程

    SQLyog?是一個快速而簡潔的圖形化管理MYSQL數(shù)據(jù)庫的工具,它能夠在任何地點有效地管理你的數(shù)據(jù)庫,這篇文章主要介紹了SQLyog12.08安裝詳細(xì)教程,需要的朋友可以參考下
    2023-04-04
  • MySQL下載安裝配置詳細(xì)教程?附下載資源

    MySQL下載安裝配置詳細(xì)教程?附下載資源

    這篇文章主要介紹了MySQL下載安裝配置詳細(xì)教程?附下載資源,本文通過圖文并茂的形式給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2022-09-09
  • CentOS7下 MySQL定時自動備份的實現(xiàn)方法

    CentOS7下 MySQL定時自動備份的實現(xiàn)方法

    這篇文章主要介紹了CentOS7 下MySQL定時自動備份的實現(xiàn)方法,主要實現(xiàn)了數(shù)據(jù)庫備份,清理過期備份文件功能,需要的朋友可以參考下
    2019-12-12

最新評論