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]
xxxxxxx
transaction-isolation=read-committed

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

SET  SESSION  TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED
SET  SESSION  TRANSACTION ISOLATION LEVEL  READ COMMITTED
SET  SESSION  TRANSACTION ISOLATION LEVEL  REPEATABLE READ
SET  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)
BEGIN
DECLARE 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)
BEGIN
DECLARE 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表的余額不足，回滾