關于Mysql的InnoDB鎖概述
一 概述
InnoDB與MyISAM有兩處不同:
1)InnoDB支持事務;
2)默認采用行級鎖(也可以支持表級鎖)
對于更新操作(UPDATE、INSERT、DELETE),InnoDB會自動給涉及到的數(shù)據(jù)集加排他鎖(X);對于普通的SELECT語句,InnoDB不加任何鎖(所以即使有一個線程的寫操作在占用鎖,不影響其他線程的讀,但是如果某個線程試圖加共享鎖則不行)。
InnoDB的行鎖模式及加鎖方法 InnoDB實現(xiàn)了以下兩種類型的行鎖。 共享鎖(S):允許一個事務去讀一行,阻止其他事務獲得相同數(shù)據(jù)集的排他鎖; 排他鎖(X):允許獲得排他鎖的事務更新數(shù)據(jù),阻止其他事務獲得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。
另外,為了允許行鎖和表鎖共存,InnoDB還有兩張內(nèi)部使用的意向鎖,都是表鎖: 意向共享鎖(IS):事務打算給數(shù)據(jù)行加行共享鎖,事務在給一個數(shù)據(jù)行加共享鎖前先必須取得該表的意向共享鎖; 意向排他鎖(IX):事務打算給數(shù)據(jù)行加行排他鎖,事務在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的意向排他鎖。 上述幾種鎖的兼容性如下: 表20-6 InnoDB行鎖模式兼容性列表
如果一個事務請求的鎖模式與當前的鎖兼容,InnoDB就將請求的鎖授予該事務;反之,如果兩者不兼容,該事務就要等待鎖釋放。 意向鎖是InnoDB自動加的,不需用戶干預。 對于更新操作(UPDATE、INSERT、DELETE),InnoDB會自動給涉及到的數(shù)據(jù)集加排他鎖(X);對于普通的SELECT語句,InnoDB不加任何鎖(所以即使有一個線程的寫操作在占用鎖,不影響其他線程的讀,但是如果某個線程試圖加共享鎖則不行)。
顯式的給記錄集加共享鎖:
- 共享鎖: SELECT * FROM tableName WHERE …. LOCK IN SHARE MODE
- 排他鎖: SELECT * FROM tableName WHERE …. FOR UPDATE
二、 共享鎖中執(zhí)行update操作容易導致死鎖
注意:**用共享鎖然后執(zhí)行了update操作,則有可能和別的線程的update操作發(fā)生鎖沖突,從而死鎖。死鎖后Mysql會自動關閉一個線程的事務操作,讓鎖被一個線程使用。**如下所示:
1)線程A和線程B對同一行記錄使用了共享鎖,兩個線程讀都沒有問題(讀不需要加鎖,不管當前記錄加了共享鎖還是排他鎖,都不影響單獨的讀操作);
2)線程A進行更新操作,因為更新操作需要加獨占鎖,而線程B還對當前記錄保留了共享鎖,故線程A無法獲得當前線程的獨占鎖,要等待線程B釋放共享鎖;
3)線程B也進行了更新操作,它也要對當前記錄加獨占鎖。那么顯然它也無法獲得到該記錄的獨占鎖,兩個線程都會等待下去,也就是死鎖。
4)此時Mysql會自動根據(jù)一定規(guī)則把鎖交給某個線程,另一個線程失去鎖重新啟動事務。
另外,注意,默認情況下單行執(zhí)行后就會自動提交事務,此時鎖也就被自動釋放了。需要關閉事務的自動提交。
set autocommit = 0;
對于需要更新的操作,應當直接使用排他鎖。這種情況下,因為線程A已經(jīng)占有了排他鎖,線程B無法獲得共享鎖和排他鎖,只能等待。但是注意,InnoDB的讀操作不需要加鎖,所以可以照常的讀。 當使用SELECT…FOR UPDATE加鎖后再更新記錄,出現(xiàn)如表20-8所示的情況。 表20-8 InnoDB存儲引擎的排他鎖例子
三、 InnodDB行鎖實現(xiàn)方式
InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的。這一點Mysql和Oracle不同,Oracle是通過直接在數(shù)據(jù)塊中對相應數(shù)據(jù)行加鎖來實現(xiàn)的。 InnoDB的這種特性意味著:只有通過索引條件檢索數(shù)據(jù),InnoDB才使用行級鎖;否則InnoDB將使用表鎖。
1)非索引字段加鎖變成表鎖
表20-9 InnoDB存儲引擎的表在不使用索引時使用表鎖例子
注意,對于表沒有加索引,線程A僅要求獲取id=1的記錄的獨占鎖,但是因為沒有加索引,所以該語句鎖住了整個表,使用了表鎖。
當我們對id行添加索引 alter table tab_with_index add index id(id); 則會有下面的例子:
2)相同索引鍵導致阻塞
由于Mysql的行鎖是針對索引加的鎖,而不是針對記錄加的,所以即使是訪問不同行,但是如果使用了相同的索引鍵,依然會沖突:
mysql> select * from tab_with_index where id = 1;
±-----±-----+
| id | name |
±-----±-----+
| 1 | 1 |
| 1 | 4 |
±-----±-----+
例如對于上表,如果對id加了索引,但是有兩個記錄的id相同,也就是索引相同。此時兩個線程分別試圖獲取兩個記錄的獨占鎖依然會導致阻塞,因為mysql的行鎖是加在索引上的。
3)不同索引鍵指向同一行記錄也會導致阻塞
- mysql> alter table tab_with_index add index name(name);
- alter table tab_with_index add index id(id);
假設我們分別對id和name增加索引,那么不管是什么索引,InnoDB都會使用行鎖來鎖定不同的行。
如果是不同的索引,但是指向了同一條記錄,那么依然會導致阻塞。 我的理解是不同索引最后指向了同一條主鍵id,鎖住了注解id,故依然會阻塞,應該不是鎖住記錄。
4)間隙鎖當我們使用范圍條件而不是相等條件來檢索數(shù)據(jù),并請求共享或排他鎖時,InnoDB會給所有符合條件的已有數(shù)據(jù)記錄的索引加鎖;對于鍵值在條件范圍內(nèi)但是并不存在的記錄,叫做間隙gap,InnoDB也會對這些間隙加鎖。這種鎖機制就是間隙鎖。
舉例來說,假如emp表中只有101條記錄,其empid的值分別是 1,2,…,100,101,下面的SQL: Select * from emp where empid > 100 for update; 這是一個范圍條件的檢索,InnoDB不僅會對empid為101的記錄加鎖,對于大于101的不存在間隙也會加鎖。
**Mysql使用間隙鎖的目的是防止幻讀(應該只是一部分滿足,不能完全回避),以滿足相關隔離級別的要求。**比如對于上面的情況,如果不加鎖,那么其他事務插入了empid為102的記錄,則會導致本事務內(nèi)再次執(zhí)行上述語句時得到empid為102的記錄,也就導致了幻讀。另一方面,也是為了滿足其回復和復制的需要。 因此,在使用范圍條件檢索并鎖定記錄時,InnoDB的這種間隙加鎖機制會阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入,從而導致嚴重的鎖等待。因此,對于并發(fā)插入較多的應用,我們要盡量優(yōu)化業(yè)務邏輯,盡量用相等條件來訪問更新數(shù)據(jù),避免使用范圍條件。
還要特別說明的是,InnoDB除了通過范圍條件加鎖時使用間隙鎖外,如果使用相等條件請求給一個不存在的記錄加鎖,InnoDB也會使用間隙鎖!
5)關于恢復和復制的需要,對InnoDB鎖機制的影響
Mysql通過BINLog記錄執(zhí)行成功的INSERT、UPDATE、DELETE等更新數(shù)據(jù)的SQL語句,并由此實現(xiàn)MySQL數(shù)據(jù)庫的回復和主從復制。Mysql的恢復記錄(復制實際就是在Slave Mysql不斷的做基于BINLOG的恢復)有以下特點: 一是MySQL的恢復是SQL語句級的,也就是重新執(zhí)行BINLOG中的SQL語句。 二是MySQL的Binlog是按照事務提交的先后順序記錄的,恢復也是按這個順序進行的。
**根據(jù)上述的特點,Mysql的恢復機制要求:在一個事務未提交前,其他并發(fā)事務不能插入滿足其鎖定條件的任何記錄,也就是不允許出現(xiàn)幻讀。****這已經(jīng)超過了ISO/ANSI SQL92“可重復讀”隔離級別的要求,實際上是要求事務要串行化。這也是許多情況下,InnoDB要用到間隙鎖的原因。**比如在用范圍條件更新記錄時,無論是Read Commited還是Repeatable Read隔離級別,InnoDB都要使用間隙鎖,這并不是隔離級別的要求,而是由于Mysql恢復和復制的要求。
到此這篇關于關于Mysql的InnoDB鎖概述的文章就介紹到這了,更多相關Mysql的InnoDB鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
textarea標簽(存取數(shù)據(jù)庫mysql)的換行方法
textarea標簽本身不識別換行功能,回車換行用的是\n換行符,輸入時的確有換行的效果,但是html渲染或者保存數(shù)據(jù)庫mysql時就只是一個空格了,這時就需要利用換行符\n和br標簽的轉(zhuǎn)換進行處理2023-09-09Linux下MySQL5.7.18二進制包安裝教程(無默認配置文件my_default.cnf)
這篇文章主要介紹了Linux下MySQL5.7.18二進制包安裝教程(無默認配置文件my_default.cnf) ,需要的朋友可以參考下2017-05-05mysql max 與 where 間的執(zhí)行問題小結(jié)
這篇文章主要介紹了mysql max 與 where 間的執(zhí)行問題小結(jié),需要的朋友可以參考下2018-01-01mysql8.0數(shù)據(jù)庫無法被遠程連接問題排查小結(jié)
本文主要介紹了mysql8.0數(shù)據(jù)庫無法被遠程連接問題排查小結(jié)2024-07-07Windows7下安裝使用MySQL8.0.16修改密碼、連接Navicat問題
這篇文章主要介紹了Windows7下安裝使用MySQL8.0.16修改密碼、連接Navicat問題,本文給大家介紹的非常詳細,具有一定的參考借鑒價值,需要的朋友可以參考下2019-06-06詳解MySQL如何實現(xiàn)數(shù)據(jù)批量更新
最近需要批量更新大量數(shù)據(jù),習慣了寫sql,所以還是用sql來實現(xiàn),下面這篇文章主要給大家總結(jié)介紹了關于MySQL批量更新的方式,需要的朋友可以參考下2023-10-10