MySQL鎖機制

• 2021 年 1 月 20 日
• 筆記
• 數據結構和演算法

一、概述

1，鎖的定義

• 鎖是電腦協調多個進程或執行緒並發訪問某一資源的機制
• 在資料庫中，除傳統的電腦資源(如CPU、RAM、I/O等)的爭用以外，數據也是一種供許多用戶共享的資源
• 如何保證數據並發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫並發訪問性能的一個重要因素。

2，鎖的分類

a）數據操作的類型

• 讀鎖(共享鎖)：針對同一份數據，多個讀操作可以同時進行而不會互相影響
• 寫鎖(排它鎖)：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖

b）數據操作的顆粒度

• 行鎖
• 表鎖
• 頁鎖

二、表鎖

1，特點

　　偏向MyISAM存儲引擎，開銷小，加鎖快，不會出現死鎖；鎖定力度大，發生鎖衝突概率高，並發度最低。

2，案例分析

#加鎖方式
lock table 表名1 read(write),表名2 read(write);
#查看錶上加過的鎖 0表示未上鎖
show open tables;
#釋放鎖
unlock tables;

a)建表

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

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');

select * from mylock;

b)加讀鎖

• 在session 1中加鎖

#給mylock表加讀鎖
lock table mylock read;
#查詢mylock表  可以展示所有數據
select * from mylock;
#在當前會話中不能讀取別的表:Table 'tbl_emp' was not locked with LOCK TABLES
select * from tbl_emp;

• 在session 2中讀取和修改

#可以查詢
select * from mylock;
#修改mylock表會出先等待，直到mylock表釋放鎖
update mylock set name='aaa' where id = 1;

• 結論：當前session為當前表加寫讀鎖

1. 當前 session只能讀取當前表，不能更新當前表。並且不能操作其他表的讀和寫
2. 其他 session只能讀取當前表，不能更新當前表(阻塞)。可以操作其他表的讀和寫。

c)加寫鎖

• 在session 1中添加寫鎖

#給mylock表加寫鎖
lock table mylock write;
#查詢mylock表  可以展示所有數據
select * from mylock;
#在當前會話中不能讀取別的表:Table 'tbl_emp' was not locked with LOCK TABLES
select * from tbl_emp;

• 在session 2中讀取和更新

#在session 2中無法讀取數據和更新數據，一直阻塞；直到session中unlock tables解鎖
select * from mylock;

• 結論：當前session為當前表加寫鎖

1. 當前session只能操作當前表的讀和寫，不能操作其他表的讀和寫
2. 其他session不能操作當前表的讀和寫(阻塞)，可以操作其他表。

d)總結

• MyIsam在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行增刪改操作前，會自動給涉及的表加寫鎖。
• 對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它進程的寫操作。
• 對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作。
• 簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞。

三、行鎖

1，特點

• 偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖(間隙鎖)；鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高。
• InnoDB與MyISam的最大不同有兩個：1.支援事務；2.採用行級鎖。

2，事務

a）事務（Transation）及其ACID

　　事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。

1. 原子性（Atomicity）：事務是一個原子操作單元，其對數據的修改，要麼全都執行，要麼全都不執行。
2. 一致性（Consistent）：在事務開始和完成時，數據都必須保持一致狀態。這意味著所有相關的數據規則都必須應用於事務的修改，以保持數據的完整性；事務結束時，所有的內部數據結構（如B樹索引或雙向鏈表）也都必須是正確的。
3. 隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並發操作影響的「獨立」環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。
4. 持久性（Durability）：事務院成之後，它對於數據的修改是永久性的，即使出現系統故障也能夠保持。

b）事務隔離級別

　　MySQL查看事務隔離級別：show variables like ‘tx_isolation’;

• 臟讀：事務A讀取到了事務B已修改但尚未提交的的數據，還在這個數據基礎上做了操作。
• 不可重複讀：事務A讀取到了事務B已經提交的修改數據，不符合隔離性
• 幻讀：第一個事務對一定範圍的數據進行批量修改，第二個事務在這個範圍內增加一條數據，這時候第一個事務就會丟失對新新增數據的修改

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');
INSERT INTO test_innodb_lock VALUES(6, '6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8, '8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');

CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);

show status like 'innodb_row_lock%';