數(shù)據(jù)庫鎖
數(shù)據(jù)庫鎖
- 表鎖
- 行鎖
- 間隙鎖
鎖是計算機協(xié)調(diào)多個進程或線程并發(fā)訪問某一資源的機制。
表鎖（偏讀）
偏向MyISAM存儲引擎,開銷小,加鎖快;無死鎖,鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)度低
表鎖案例
- 建表
create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;
插入數(shù)據(jù)
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
- 手動增加表鎖
lock table 表名字 read(write),表名字2 read(write);
- 釋放表鎖
unlock tables;
表鎖總結
MyISAM在執(zhí)行查詢語句(select)前,會自動給涉及的所有表加讀鎖,在執(zhí)行增刪改操作前,會自動給涉及的表加寫鎖
- 對MyISAM表的讀操作(加讀鎖),不會阻塞其他進程對同一表的讀請求,但會阻塞對同一表的寫請求。只有當讀鎖釋放后,才會執(zhí)行其他進行的寫操作
- 對MyISAM表的寫操作(加寫鎖),會阻塞其他進程對同一表的讀和寫操作,只有當寫鎖釋放后,才會執(zhí)行其他進程的讀寫操作
行鎖（偏寫）
偏向InnoDB存儲引擎,開銷大,加鎖慢,會出現(xiàn)死鎖。鎖定粒度最小,發(fā)生鎖沖突的概率最低,并發(fā)度也最高
InnoDB與MyISAM的最大不同點,支持事務,采用了行級鎖
行鎖案例
create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;
insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
create index idx_test_innodb_a on test_innodb_lock(a);
create index idx_test_innodb_b on test_innodb_lock(b);
如何分析行鎖定
通過檢查innodb_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖爭奪情況
show status like 'innodb_row_lock%';
各個狀態(tài)量的說明
Innodb_row_lock_current_waits 當前正在等待鎖定的數(shù)量
* Innodb_row_lock_time 從系統(tǒng)啟動到現(xiàn)在鎖定的總時間長度
* Innodb_row_lock_time_avg 每次等待所花費平均時間
Innodb_row_lock_time_max 從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花費的時間
* Innodb_row_lock_waits 系統(tǒng)啟動后到現(xiàn)在總共等待的次數(shù)
什么是間隙鎖
當我們用范圍條件而不是相等條件檢索數(shù)據(jù),并請求共享或排他鎖時,innodb會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖, 對于鍵值在條件范圍內(nèi)但并不存在的記錄,叫做"間隙"
innodb也會對這個"間隙"加鎖,這種鎖機制就是所謂的間隙鎖
間隙鎖的危害
因為SQL執(zhí)行過程中通過范圍查找的話,他會鎖定整個范圍內(nèi)所有的索引值,即使這個鍵值并不存在
間隙鎖有一個比較致命的弱點,就是當鎖定以為范圍鍵值之后,即使某些不存在的鍵值也會被無辜的鎖定,而造成在鎖定的時候無法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)。在某些場景下這可能會對性能造成很大的危害
如何鎖定一行
select * from test_innodb_lock where a = 8 for update;
