一文帶你瞭解MySQL中的鎖機制

2023-02-20 06:00:26

一.概述

鎖是計算機協調多個程序或執行緒並行存取某一資源的機制（避免爭搶）。

在資料庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，資料也是一種供許多使用者共用的資如何保證資料並行存取的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並行存取效能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。

分類

1、從對資料操作的粒度分

表鎖:操作時，會鎖定整個表。
行鎖:操作時，會鎖定當前操作行。

2、從對資料操作的型別分

讀鎖（共用鎖):針對同一份資料，多個讀操作可以同時進行而不會互相影響。
寫鎖（排它鎖）:當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。

相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的儲存引擎支援不同的鎖機制。下表中羅列出了各儲存引擎對鎖的支援情況:

儲存引擎	表級鎖	行級鎖
MylSAM	支援	不支援
lnnoDB	支援	支援
MEMORY	支援	不支援
BDB	支援	不支援

MySQL鎖的特性可大致歸納如下:

鎖型別	特點
表級鎖	偏向MyISAM儲存引擎，開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖衝突的概率最高,並行度最低。
行級鎖	偏向InnoDB儲存引擎，開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖衝突的概率最低,並行度也最高。

從上述特點可見，很難籠統地說哪種鎖更好，只能就具體應用的特點來說哪種鎖更合適! 僅從鎖的角度來說:表級鎖更適合於以查詢為主，只有少量按索引條件更新資料的應用，如web應用;

而行級鎖則更適合於有大量按索引條件並行更新少量不同資料，同時又有並查詢的應用，如一些線上事務處理（OLTP)系統。

二.MyISAM表鎖

MylSAM儲存引擎只支援表鎖

如何加表鎖

MylSAM在執行查詢語句(SELECT)前，會自動給涉及的所有表加讀鎖，在執行更新操作(UPDATE、DELETE、INSERT等)前，會自動給涉及的表加寫鎖，這個過程並不需要使用者干預，因此，使用者一般不需要直接用LOCK TABLE命令給MylSAM表顯式加鎖。

讀鎖演示

create database test_lock;
use test_lock;
 
drop table tb_book ;
 
create table tb_book(
	id int(11) auto_increment,
	name varchar(50) default null,
	publish_time date default null,
	status char(1) default null,
	primary key (id)
)engine=myisam default charset=utf8;
 
insert into tb_book values(null,'sql','2088-08-01','1');
insert into tb_book values(null,'sql',20880908,'0');
 
 
create table tb_user(
	id int(11) auto_increment,
	name varchar(50) default null,
	primary key (id)
	
)engine =myisam default charset=utf8;
 
insert into tb_user values(null,'張三');
insert into tb_user values(null,'李四');
 
-- 加讀鎖
lock table tb_book read;
 
select * from tb_book  ;-- 能正常輸出
select * from tb_book tb ;-- 不能正常輸出，不能有別名
update tb_book set status=1;-- 不能正常輸出 讀鎖：可讀不可改
 
select * from tb_user ;-- 不能正常輸出,當鎖住一個表時也不能看其他表
-- 解鎖
unlock tables;

加了讀鎖後只能看自己的表，不能修改，也不能看其他的表

寫鎖演示

create table tb_book(
	id int(11) auto_increment,
	name varchar(50) default null,
	publish_time date default null,
	status char(1) default null,
	primary key (id)
)engine=myisam default charset=utf8;
 
insert into tb_book values(null,'sql','2088-08-01','1');
insert into tb_book values(null,'sql',20880908,'0');
 
 
create table tb_user(
	id int(11) auto_increment,
	name varchar(50) default null,
	primary key (id)
	
)engine =myisam default charset=utf8;
 
insert into tb_user values(null,'張三');
insert into tb_user values(null,'李四');
 
-- 加寫鎖
lock table tb_book write;
 
select * from tb_book  ;-- 能正常輸出
select * from tb_book tb ;-- 不能正常輸出，不能有別名
update tb_book set status=1;-- 可以修改 寫鎖：可讀可修改，但在沒有解鎖之前不能被其他人檢視，會被掛起
 
select * from tb_user ;-- 不能正常輸出,當鎖住一個表時也不能看其他表
-- 解鎖
unlock tables;

三.InnoDB行鎖

行鎖特點

行鎖特點︰偏向InnoDB儲存引擎，開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖衝突的概率最低,並行度也最高。

InnoDB與 MylSAM的最大不同有兩點:一是支援事務;二是採用了行級鎖。

行鎖模式

lnnoDB實現了以下兩種型別的行鎖。

1、共用鎖(S)∶又稱為讀鎖，簡稱s鎖，共用鎖就是多個事務對於同一資料可以共用一把鎖，都能存取到資料，但是隻能讀不能修改。

2、排他鎖(x):又稱為寫鎖，簡稱x鎖，排他鎖就是不能與其他鎖並存，如一個事務獲取了一個資料行的排他鎖，其他事務就不能再獲取該行的其他鎖，包括共用鎖和排他鎖，但是獲取排他鎖的事務是可以對資料就行讀取和修改。

對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加排他鎖(x);

對於普通SELECT語句，InnoDB不會加任何鎖;

可以通過以下語句顯示給記錄集加共用鎖或排他鎖。

-- 行鎖
 
drop table is exists test_innodb_lock;
create table test_innodb_lock(
	id int(11),
	name varchar(16),
	sex varchar(1)
)engine=innodb;
 
insert into test_innodb_lock values(1,'100','1'),
									(2,'200','0'),
									(3,'200','1'),
									(4,'300','0'),
									(5,'400','0'),
									(6,'500','1'),
									(7,'600','1'),
									(1,'700','1');
 
create index index_id on test_innodb_lock(id);
create index index_name on test_innodb_lock(name);

可以修改是因為上面的行鎖只對id=1的那兩行加了排他鎖

到此這篇關於一文帶你瞭解MySQL中的鎖機制的文章就介紹到這了,更多相關MySQL鎖機制內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！