MySQL資料庫進階（七）——事務和鎖

標籤：MySQL 事務 鎖

MySQL資料庫進階（七）——事務和鎖一、事務簡介1、事務簡介

事務(Transaction) 是指作為單個邏輯工作單元執行的一系列操作。

2、事務的特性

A、原子性（Atomicity）
表示組成一個事務的多個資料庫操作是一個不可分隔的原子單元，只有所有的操作執行成功，整個事務才提交，事務中任何一個資料庫操作失敗，已經執行的任何操作都必須撤銷，讓資料庫返回到初始狀態。
B、一致性（Consistency）
事務操作成功後，資料庫所處的狀態和它的商務規則是一致的，即資料不會被破壞。
C、隔離性（Isolation）
在並發資料操作時，不同的事務擁有各自資料空間，它們的操作不會對對方產生幹擾。資料庫規定了多種交易隔離等級，不同隔離等級對應不同的幹擾程度，隔離等級越高，資料一致性越好，但並發性越弱。
D、持久性（Durabiliy）
一旦事務提交成功後，事務中所有的資料操作都必須被持久化到資料庫中，即使提交事務後，資料庫馬上崩潰，在資料庫重啟時，也必須能保證能夠通過某種機制恢複資料。

3、事務類型

A、自動認可事務
系統預設每個TRANSACT－SQL命令都是一個交易處理，由系統自動開始並提交。
B、隱含交易
不需要顯示開始事務，需要顯示提交，隱含交易是任何單獨的INSERT、UPDATE 或者DELETE語句構成。當有大量的DDL和DML命令執行時會自動開始，並一直保持到使用者明確提交為止。
SHOW VARIABLES 查看變數。
SET AUTOCOMMIT=0，關閉自動認可功能。
需要顯示提交或者復原。

update tablename set sname=‘孫悟空‘ where studentid=‘000000000000003‘;commit;

或
rollback;
C、顯示事務
顯示事務是使用者自訂事務，以START TRANSACTION（事務開始）開頭，以 COMMIT（事務提交）或者 ROLLBACK（復原事務）語句結束。

start transaction update tablename set sname=‘孫悟空‘ where studentid=‘000000000000003‘;commit

或
rollback
D、分散式交易
跨越多個伺服器的事務稱為分散式交易。從MySQL5.03開始支援分散式交易。

4、事務控制

A、開始事務
標記一個明確交易的開始點，即事務開始。其文法如下：
START { TRAN | TRANSACTION }
B、提交事務
標記一個成功的隱性事務或明確交易的結束，即事務提交。其文法如下：
COMMIT
C、復原事務
將明確交易或隱性交易回復到事務的起點或事務內的某個儲存點。其文法如下：
ROLLBACK
D、事務設定
SET AUTOCOMMIT 可以修改當前串連事務提交方式。
SET AUTOCOMMIT=0，則需要明確的命令進行提交或者復原。

5、事務並髮帶來的問題

髒讀（Dirty Read）是指某個事務（A）讀取另外事務（B）尚未認可的變更資料，並在讀取的資料的基礎上操作。如果恰巧 B交易回復，那麼 A事務讀到的資料根本是不被承認的。
不可重複讀取（Unrepeatable Read）是指A事務讀取了B事務已經提交的更改資料。
幻象讀（Phantom Read）
A事務讀取B事務提交的新增資料，這時A事務將出現幻象讀的問題。
第一類丟失更新
A事務撤銷時，把已經提交的B事務的更新資料覆蓋。
第二類丟失更新
A事務覆蓋B事務已經提交的資料，造成B事務所做操作丟失。

二、交易隔離等級1、交易隔離等級簡介

SQL標準定義了4類隔離等級，包括了一些具體規則，用來限定事務內外的哪些改變是可見的，哪些是不可見的。低層級的隔離級一般支援更高的並發處理，並擁有更低的系統開銷。
Read Uncommitted（讀取未提交內容）
本隔離等級，事務可以讀取其他未提交事務的執行結果。讀取未提交的資料，也被稱之為髒讀（Dirty Read）。
Read Committed（讀取提交內容）
大多數資料庫系統的預設隔離等級（但不是MySQL預設的）。事務只能讀取其他事務已經提交的執行結果。本隔離等級支援所謂的不可重複讀取（Nonrepeatable Read），因為同一事務的其他執行個體在該執行個體處理其間可能會有新的commit，所以同一select可能返回不同結果。
Repeatable Read（可重讀）
MySQL預設的交易隔離等級，會給查詢的記錄做快照，直到事務結束。確保同一事務的多個執行個體在並發讀取資料時，會看到同樣的資料行，會導致幻讀（Phantom Read）。幻讀指當使用者讀取某一範圍的資料行時，另一個事務又在該範圍插入入了新行，當使用者再讀取該範圍的資料行時，會發現有新的“幻影” 行。InnoDB和Falcon儲存引擎通過多版本並發控制（MVCC，Multiversion Concurrency Control）機制解決了幻讀問題。
Serializable（可序列化）
最高的隔離等級，對同一條記錄讀和修改的多個事務只能結束一個，才能開始下一個。
通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。在每個讀的資料行上加上共用鎖定，可能導致大量的逾時現象和鎖競爭。

2、交易隔離等級設定

使用者可以用SET TRANSACTION語句改變單個會話或者所有新進串連的隔離等級。文法如下：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
預設的行為（不帶session和global）是為下一個（未開始）事務設定隔離等級。如果使用GLOBAL關鍵字，語句在全域對新開始建立的所有新串連設定預設事務層級，需要SUPER許可權。使用SESSION關鍵字為將來在當前串連上執行的事務設定預設事務層級。 任何用戶端都能自由改變會話隔離等級，或者為下一個事務設定隔離等級。
查詢全域和會話交易隔離等級：

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

通過mySQL設定檔修改全域交易隔離等級，設定全域會話預設交易隔離等級。

[mysqld]xxxxxxxtransaction-isolation=read-committed

重啟mySQL服務，生效。
設定當前會隔離等級

SET  SESSION  TRANSACTION ISOLATION LEVEL  READ UNCOMMITTEDSET  SESSION  TRANSACTION ISOLATION LEVEL  READ COMMITTEDSET  SESSION  TRANSACTION ISOLATION LEVEL  REPEATABLE READSET  SESSION  TRANSACTION ISOLATION LEVEL  SERIALIZABLE

三、交易隔離等級驗證1、不同會話的隔離等級

不同會話的交易隔離等級不同
在會話1終端查看當前會話的交易隔離等級
select @@tx_isolation
查詢結果為：可重複讀REPEATABLE-READ
設定當前會話交易隔離等級為READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
開啟另一個SQL Manager終端作為會話2，查看當前會話的交易隔離等級
select @@tx_isolation
查詢結果為：可重複讀REPEATABLE-READ
建立一張表，含ID、姓名、年齡欄位，用於驗證不同的交易隔離等級。

CREATE TABLE ta(id INT NOT NULL PRIMARY KEY,name VARCHAR(10),age INT)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into ta values(1, ‘孫悟空‘, 500);insert into ta values(2, ‘唐僧‘, 30);

註：由於本人SQL Manager Lite用戶端的交易回復機制失效，以下實驗使用Navicat for MySQL用戶端。

2、驗證READ UNCOMMITTED隔離等級

開啟一個會話1，設定交易隔離等級為READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
開啟會話2，開始一個事務，更新ID為1的記錄的age為1000。

start TRANSACTION;update ta set age=1000 where id =1;

在會話1查看ta表中ID為1的資訊，age已經為1000。
select * from ta;
會話1的交易隔離等級允許讀取未提交的資料。
在會話2復原事務
ROLLBACK;
會話1和會話2查詢ta表中ID為1的記錄，age為500

3、驗證READ COMMITTED隔離等級

開啟一個會話1，設定交易隔離等級為READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
開啟會話2，開始一個事務，更新ID為1的記錄的age為5000。

start TRANSACTION;update ta set age=5000 where id =1;

在會話1查看ta表中ID為1的資訊，age為500。
select * from ta;
會話1的交易隔離等級不允許讀取未提交的資料。
在會話2提交事務
COMMIT;
會話1查詢ta表中ID為1的記錄，age為5000

4、驗證REPEATABLE READ隔離等級

開啟一個會話1，設定交易隔離等級為REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
在會話1，開始一個事務，查詢ID為1的記錄的age為5000。

start TRANSACTION;SELECT * FROM ta where id =1;

在會話2更新ta表中ID為1的資訊，age為1000。
UPDATE ta SET age=1000 WHERE id=1;
在會話2查看ta表中ID為1的資訊，age已經為1000。
select * from ta WHERE id=1;
在會話1再次查看ta表中ID為1的資訊，age仍舊為5000。
select * from ta WHERE id=1;
在會話1提交事務
COMMIT;
會話1查詢ta表中ID為1的記錄，age已經為1000。

5、驗證SERIALIZABLE隔離等級

開啟一個會話1，設定交易隔離等級為SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE
開啟會話2，開始一個事務，更新ID為1的記錄的age為5000。

start TRANSACTION;update ta set age=5000 where id =1;

在會話1開始一個事務，查看ta表中ID為1的資訊，會話1處於等待狀態。

start TRANSACTION;select * from ta;

在會話2提交事務後，
COMMIT;
會話1查詢SQL執行完畢，結果為5000。

四、鎖1、鎖簡介

資料庫中的鎖是指一種軟體機制，用來控制防止某個使用者（進程會話）在已經佔用了某種資料資源時，其他使用者做出影響本使用者資料操作或導致資料非完整性和非一致性問題發生的手段。

2、鎖的層級

按照鎖層級劃分，鎖可分為共用鎖定、獨佔鎖定。
A、共用鎖定（讀鎖）
　　針對同一塊資料，多個讀操作可以同時進行而不會互相影響。
共用鎖定只針對UPDATE時候加鎖，在未對UPDATE操作提交之前，其他事務只能夠擷取最新的記錄但不能夠UPDATE操作。
B、獨佔鎖定（寫鎖）
當前寫操作沒有完成前，阻斷其他寫鎖和讀鎖。

3、鎖的粒度

按鎖的粒度劃分，鎖可分為表級鎖、行級鎖、頁級鎖。
A、行級鎖
開銷大，加鎖慢，會出現死結，鎖定力度最小，發生鎖衝突的機率最低，並發度高。
B、表級鎖
開銷小，加鎖快，不會出現死結，鎖定力度大，發生衝突所的機率高，並發度低。
C、頁面鎖
開銷和加鎖時間介於表鎖和行鎖之間，會出現死結，鎖定力度介於表和行行級鎖之間，並發度一般。

4、MySQL儲存引擎和鎖機制

MySQL的鎖機制比較簡單，最顯著的特點是不同的儲存引擎支援不同的鎖機制。
MyISAM和MEMORY儲存引擎採用表級鎖。
InnoDB支援行級鎖、表級鎖，預設情況採用行級鎖。

五、表級鎖1、表級鎖簡介

MyISAM儲存引擎和InnoDB儲存引擎都支援表級鎖。
MyISAM儲存引擎支援表級鎖，為了保證資料的一致性，更改資料時，防止其他人更改資料，可以人工添加表級鎖。可以使用命令對資料庫的表枷鎖，使用命令對資料庫的表解鎖。
給表加鎖的命令Lock Tables，給表解鎖的命令Unlock Tables
MyISAM引擎在使用者讀資料自動加READ鎖，更改資料自動加WRITE鎖。使用lock Tables和Unlock Tables顯式加鎖和解鎖。

2、添加表級讀鎖

開啟會話1，建立表

CREATE TABLE tc(id INT,name VARCHAR(10),age INT)ENGINE=MyISAM DEFAULT CHARSET=utf8;

插入兩條記錄：

insert into tc values(1, ‘孫悟空‘, 500);insert into tc values(3, ‘豬八戒‘, 100);

對錶加READ鎖
lock tables tc read;
加鎖後只可以查詢已經加鎖的表，
select * from tc;
查詢沒有加鎖的表將失敗
select * from ta;
開啟會話2，對已經加鎖的表進行查詢，成功。
select * from tc;
對加鎖的表tc進行更新操作，將失敗
update tc set age=100 where id=1;
會話1中使用LOCK TABLE命令給表加了讀鎖，會話1可以查詢鎖定表中的記錄，但更新或訪問其他表都會提示錯誤；會話2可以查詢表中的記錄，但更新就會出現鎖等待。
在會話1對錶進行解鎖，會話2的更新操作成功。
unlock tables;
在會話1，再次鎖定表tc，後面帶local參數。
lock tables tc read local;
Local參數允許在表尾並發插入，只鎖定表中目前記錄，其他會話可以插入新的記錄
在會話2插入一條記錄
insert into tc values(2, ‘唐僧‘, 20);
在會話1查看tc表的記錄，無插入記錄
select * from tc;

3、設定表級鎖並發性

READ鎖是共用鎖定，不影響其他會話的讀取，但不能更新已經加READ鎖的資料。MyISAM表的讀寫是串列的，但是總體而言的，在一定條件下，MyISAM表也支援查詢和插入操作的並發進行。
MyISAM儲存引擎有一個系統變數concurrent_insert，用以控制其並發插入的行為，其值分別可以為0、1或2。
0：不允許並行作業
1：如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄，是MySQL的預設設定。
2：無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。
在MySQL設定檔添加，concurrent_insert=2，重啟mySQL服務設定生效。

4、驗證表級鎖的並發性

設定concurrent_insert為0
在會話1對錶tc加鎖
lock tables tc read local;
在會話2插入一條記錄，此時tc表被鎖定，進入等待
insert into tc values(4, ‘沙悟淨‘, 30);
在會話1解鎖表tc，此時會話2插入成功
unlock tables;

設定concurrent_insert為1
在會話1刪除ID為3的記錄
delete from tc where id=3;
在會話1對錶tc加鎖
lock tables tc read local;
在會話2插入一條記錄，此時tc表被鎖定，並且表中有空洞，進入等待
insert into tc values(5, ‘白骨精‘, 1000);
在會話1解鎖表tc，此時會話2插入成功，此時表中已經沒有空洞
unlock tables;
在會話1對錶tc加鎖
lock tables tc read local;
在會話2插入一條記錄，插入成功，支援有條件並發插入
insert into tc values(6, ‘白骨精‘, 1000);
在會話1解鎖表tc
unlock tables;

設定concurrent_insert為2
在會話1刪除ID為5的記錄，創造一個空洞
delete from tc where id=5;
在會話1對錶tc加鎖
lock tables tc read local;
在會話2插入一條記錄，插入成功，支援無條件並發插入
insert into tc values(7, ‘蜘蛛精‘, 1000);
在會話1解鎖表tc
unlock tables;

5、添加表級寫鎖

添加表級寫鎖文法如下：
LOCK TABLES tablename WRITE;
不允許其他會話查詢、修改、插入記錄。

六、行級鎖1、行級鎖簡介

InnoDB儲存引擎實現的是基於多版本的並發控制協議——MVCC (Multi-Version Concurrency Control)。MVCC的優點是讀不加鎖，讀寫不衝突。在讀多寫少的OLTP應用中，讀寫不衝突是非常重要的，極大的增加了系統的並發效能。
在MVCC並發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當前讀 (current read)。
快照讀，讀取的是記錄的可見版本 (有可能是曆史版本)，不用加鎖。
當前讀，讀取的是記錄的最新版本，並且當前讀返回的記錄都會加上鎖，保證其他事務不會再並發修改。事務加鎖，是針對所操作的行，對其他行不進行加鎖處理。
快照讀：簡單的SELECT操作，屬於快照讀，不加鎖。
select * from table where ?;
當前讀：特殊的讀操作，INSERT/UPDATE/DELETE，屬於當前讀，需要加鎖。

select * from table where ? lock in share mode;select * from table where ? for update;insert into table values (…);update table set ? where ?;delete from table where ?;

以上SQL語句屬於當前讀，讀取記錄的最新版本。並且，讀取之後，還需要保證其他並發事務不能修改目前記錄，對讀取記錄加鎖。其中，除了第一條語句，對讀取記錄加S鎖 (共用鎖定)外，其他的操作，都加的是X鎖 (排它鎖)。

2、驗證快照讀

開啟會話1，建立一個表，含ID、姓名、年齡

CREATE TABLE td(id INT ,name VARCHAR(10),age INT)ENGINE=innoDB DEFAULT CHARSET=utf8;

在插入兩條記錄

insert into td values(1, ‘孫悟空‘, 500);insert into td values(2, ‘豬八戒‘, 100);

在會話1開始事務
start transaction;
在會話1查詢ID位1的記錄資訊
select * from td where id =1;
開啟會話2，更新ID為1的age為1000
update td set age=1000 where id=1;
在會話2查看ID為1的age已經更新為1000。
select * from td where id =1;
在會話1查看ID為1的age，仍然為500。
select * from td where id =1;
在會話1提交事務
COMMIT;
在會話1查看ID為1的age，已經為1000。

3、驗證當前讀

在會話1開始事務
start transaction;
在會話1給select語句添加共用鎖定。
select * from td where id=1 lock in share mode;
在會話2，更新ID為1的age的值為100，進入鎖等待
update td set age=100 where id=1;
在會話1提交事務
COMMIT;
會話2的更新操作成功。

4、驗證事務給記錄加鎖

在會話1開始事務
start transaction;
在會話1更新ID為1的age的值為500。
update td set age=500 where id=1;
在會話2開始事務
start transaction;
在會話2更新ID為2的age的值為1000，此時進入鎖等待
update td set age=1000 where id=2;
td表沒有指定主鍵，事務不支援行級鎖。會話1的事務給整張表加了鎖。
在會話1提交事務，此時會話2的修改成功
COMMIT;
在會話2提交事務，解除對錶的鎖定
COMMIT;
在會話1，給表的ID增加主鍵
alter table td add primary key(id);
在會話1開始事務
start transaction;
在會話1更新ID為1的age的值為5000
update td set age=5000 where id=1;
在會話2上開始事務
start transaction;
在會話2上修改ID為2的get的值為10000，更新成功，說明會話1隻鎖定了ID為1的行。
update td set age=10000 where id=2;
在會話2上更新ID是1的age值為100，出現等待。因為會話1給ID為1的行添加了獨佔鎖。
update td set age=5000 where id=1;
在會話1提交事務
COMMIT;
在會話2提交事務
COMMIT;
在會話1查詢，會話1和會話2對age列的修改都生效
select * from td;

5、死結的產生

A事務添加共用鎖定後，B事務也可以添加共用鎖定。A事務UPDATE鎖定記錄，處於等待中，於此同時B事務也UPDATE更新鎖定的記錄，就產生死結。
在會話1開始事務
start transaction;
在會話1查詢ID是1的記錄，並添加共用鎖定。
select * from td where id=1 lock in share mode;
在會話2開始事務
start transaction;
在會話2查詢ID是1的記錄，並添加共用鎖定。
select * from td where id=1 lock in share mode;
在會話1更新ID為1的age值為，等待會話2釋放共用鎖定
update td set age=200 where id=1;
在會話2更新ID為1的age為，會話2發現死結，復原事務。
update td set age=200 where id=1;
在會話1提交事務
COMMIT;

七、事務執行個體

事務提交還是復原，可以在事務結束處判斷是否出現錯誤，如果出現，復原。如果沒有錯誤，提交事務。
使用自訂條件來決定事務是提交還是復原。

1、由錯誤決定事務提交或復原

在預存程序中使用事務，在事務的末尾判斷是否有錯誤，插入失敗，則復原事務。
建立兩張表，儲存ID、姓名、年齡，建立預存程序將A表的指定ID的記錄轉移到B表。

CREATE TABLE ta(id INT NOT NULL PRIMARY KEY,name VARCHAR(10),age INT)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into ta values(1, ‘孫悟空‘, 500);insert into ta values(2, ‘唐僧‘, 30);CREATE TABLE tb(id INT NOT NULL PRIMARY KEY,name VARCHAR(10),age INT)ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into tb values(1, ‘孫悟空‘, 500);insert into tb values(3, ‘豬八戒‘, 100);CREATE PROCEDURE move(num INT)BEGINDECLARE errorinfo INT DEFAULT 0;DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET errorinfo=1;START TRANSACTION;INSERT INTO tb SELECT * FROM ta WHERE id=num;DELETE FROM ta WHERE id=num;IF errorinfo=1    THEN ROLLBACK;ELSE   COMMIT;END IF;END

將ID為2的記錄從A錶轉移到B表
call move(2);

2、由自訂條件決定事務提交或復原

建立兩個表，每個表含賬戶、姓名、餘額資訊，建立一個預存程序，從A表中的一個賬戶轉賬一定金額到B表的一個賬戶，如果轉出賬戶的餘額不足，則復原，否則提交。

create table accountA(account INT PRIMARY KEY NOT NULL,name VARCHAR(10),balance DOUBLE)ENGINE=innoDB default CHARSET=utf8;insert into accountA VALUES(1, ‘孫悟空‘, 10000);insert into accountA VALUES(2, ‘唐僧‘, 20000);create table accountB(account INT PRIMARY KEY NOT NULL,name VARCHAR(10),balance DOUBLE)ENGINE=innoDB default CHARSET=utf8;insert into accountB VALUES(1, ‘孫悟空‘, 10000);insert into accountB VALUES(2, ‘唐僧‘, 20000);CREATE PROCEDURE transfer(fromaccout INT,toaccount INT, num DOUBLE)BEGINDECLARE m DOUBLE;START TRANSACTION;UPDATE accountB SET balance=balance + num WHERE account=toaccount;UPDATE accountA SET balance=balance - num WHERE account=fromaccout;SELECT balance INTO m from accountA WHERE account=fromaccout;IF m < 0   THEN ROLLBACK;ELSE    COMMIT;END IF;END

從A表的賬戶2轉出25000元到B表的賬戶2。
call transfer(2,2,25000);
此時A表的餘額不足，復原

MySQL資料庫進階（七）——事務和鎖