文章詳情頁

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

瀏覽：205日期：2023-02-18 16:43:52

一.概述

鎖是計算機協調多個進程或線程并發訪問某一資源的機制（避免爭搶）。

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

二.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);