MySQL：（十）MySQL鎖和事務

（一）關系型資料庫基礎

（二）MySQL安裝

（三）管理資料庫和表

（四）使用者和權限管理

（五）函數，存儲過程和觸發器

（六）MySQL架構

（七）存儲引擎

（八）MySQL伺服器選項，系統和狀态變量

（九）優化查詢和索引管理

（十）鎖和事務管理

（十一）日志管理

（十二）備份還原

（十三）MySQL叢集

MySQL 鎖

MySQL鎖的概述

1）MySQL鎖

**鎖是計算機協調多個程序或純線程并發通路某一資源的機制。**即鎖的目前是為了實作并發控制。相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支援不同的鎖機制。MySQL大緻可歸納為以下3種鎖：

• 開銷、加鎖速度、死鎖、粒度、并發性能
• 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高，并發度最低。
• 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低，并發度也最高。
• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

2）上述關于鎖的概念

• 鎖粒度：

  即通常所說的鎖級别。資料庫引擎具有多粒度鎖定，允許一個事務鎖定不同類型的資源。 為了盡量減少鎖定的開銷，資料庫引擎自動将資源鎖定在适合任務的級别。

  ①表級鎖

  ②行級鎖

如果兩個使用者同時對資料庫中的某個表的某一個記錄修改時：
==>存儲引擎自動加鎖的鎖粒度
    myisam：表級鎖  别的使用者不能對表中其他記錄進行修改，影響并發
    innodb：行級鎖  别的使用者隻是不能對目前記錄進行修改
           

• 鎖：

  讀鎖：共享鎖，隻讀不可寫（包括目前事務），多個讀互不阻塞

  寫鎖：獨占鎖，排它鎖，寫鎖會阻塞其他事務（不包括目前事務）

• 鎖的分類：

  隐式鎖：由存儲引擎自動施加的鎖

  顯示鎖：使用者手動請求

• 鎖的實作：

  存儲引擎：自行實作其鎖政策和鎖粒度

  伺服器級：實作了鎖，表級鎖，使用者可顯示請求

• 鎖政策：在鎖粒度及資料安全性尋求的平衡機制

3）下面主要實作顯示加鎖（存儲引擎加鎖是mysql自動實作的功能不能幹預）

• LOCK TABLES 加鎖

  tbl_name [[AS] alias] lock_type

  [, tbl_name [[AS] alias] lock_type] …

  lock_type: READ ， WRITE

• UNLOCK TABLES 解鎖

• FLUSH TABLES [tb_name[,…]] [WITH READ LOCK]

  關閉正在打開的表（清除查詢緩存），通常在備份前加全局讀鎖

• SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

  查詢時加寫或讀鎖

由上述指令可知：人為加鎖預設隻能加表級鎖
    lock tables
        其中加鎖類型有：read  和 write

示例：終端：/dev/pts/0 
> lock tables students read; 對studens加鎖
> select stuid,name,age from students where stuid<3; == ok
+-------+-------------+-----+
| stuid | name        | age |
+-------+-------------+-----+
|     1 | Shi Zhongyu |  22 |
|     2 | Shi Potian  |  22 |
+-------+-------------+-----+
> update students set age=25 where stuid=1; == 直接報錯
ERROR 1099 (HY000): Table 'students' was locked with a READ lock and can't be updated

終端：/dev/pts/1
> select stuid,name,age from students where stuid<3; == ok
+-------+-------------+-----+
| stuid | name        | age |
+-------+-------------+-----+
|     1 | Shi Zhongyu |  22 |
|     2 | Shi Potian  |  22 |
+-------+-------------+-----+    
> update students set age=25 where stuid=1;  == 阻塞
    此時會被阻塞在此處，直至表級鎖被釋放

總結：人為添加read的表級鎖時，從表中讀取資料是影響的，但是對表進行修改時會被阻塞.

> unlock tables; 
    ==>unlock tables;隻能解鎖目前終端加鎖的所有表

==>實驗出現這樣了的現象：
    在終端：/dev/pts/0  
        對其中一張表：>lock tables teachers read;

    在終端：/dev/pts/1  
        對其中一張表：>lock tables students read;
    
    然後在各自的終端通路對方加鎖的表就不能通路啦？直接報錯？    
           

示例：添加write鎖：
終端：/dev/pts/0 
    > lock tables teachers write;
    > select * from teachers;  == ok
    > select * from teachers;  == ok
終端：/dev/pts/1
    > select * from teachers;  == 阻塞
    此時将會被阻塞

總結：write鎖是獨占鎖，僅能自己查詢或者修改，其他使用者不能的操作均會别阻塞
           

示例：FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]
    > flush tables; 全局讀鎖
    整個執行個體的全局讀鎖！
    > flush tables students with read lock;
        注: <即使這樣明确寫明對students表加讀鎖也是全局讀鎖>

    解鎖：> unlock tables; 
           

事務

事務

• 事務Transactions：一組原子性的SQL語句，或一個獨立工作單元
• 事務日志：記錄事務資訊，實作undo,redo等故障恢複功能

• 事務的特性：

  A：atomicity原子性；整個事務中的所有操作要麼全部成功執行，

  要麼全部失敗後復原

  C：consistency一緻性;資料庫總是從一個一緻性狀态轉換為另

  一個一緻性狀态

  I：isolation隔離性；一個事務所做出的操作在送出之前，是不

  能為其它事務所見；隔離有多種隔離級别，實作并發

  D：durability持久性；一旦事務送出，其所做的修改會永久保

  存于資料庫中

事務的生命周期

• 啟動事務：

  BEGIN

  BEGIN WORK

  START TRANSACTION

• 結束事務：

  COMMIT：送出

  ROLLBACK: 復原

  注意： 隻有事務型存儲引擎中的DML語句方能支援此類操作，myisam不支援事務

• 自動送出：set autocommit={1|0} 預設為1，為0時設為非自動送出

  建議：顯式請求和送出事務，而不要使用“自動送出”功能

• 事務支援儲存點：savepoint

  SAVEPOINT identifier

  ROLLBACK [WORK] TO [SAVEPOINT] identifier

  RELEASE SAVEPOINT identifier

啟動一個事務：begin
    增删改....
結束事務：commit和rollback

> select @@autocommit 自動送出;
+---------+
| 自動送出 | @@autocommit
+---------+
|       1 |
+---------+
==>MySQL系統中預設啟動的是自動送出機制
    即對表的增删改在SQL語句最後的分号就當做事務送出了。

> set autocommit=0;會話将autocommit改為0
           

以students表為例

session1	session2
> set autocommit=0;将事務改為顯示，但是臨時修改
>delete from students where stuid=31;
> select * from hellodb.students;可查詢到stuid=31的記錄
> commit;顯示送出事務
> select * from hellodb.students;不能查詢stuid=31的記錄
同時執行多個增删改操作，如果rollback将全部撤銷
顯示送出事務時，如果掉電系統故障均視為rollback
隻有顯示送出即>commit;才算事務的送出

注意： 事務的rollback隻能針對DDL(insert,delete,update)即對表的增删改可以在事務未送出時撤銷，DDL(create,drop,alter)不能rollback即删庫删表不能使用rollback撤銷。

事務的意義

• 事務可以保證資料的完整性和安全性。eg：轉賬必須以事務方式進行
• 多個DDL語句作為整體執行，比單個DDL語句獨立執行速度更快。

示例：

定義如下存儲過程，create test表，insert 100000 條記錄
    快速清空表中記錄：truncate table testlog

create table test (id int auto_increment primary key,name char(10));
delimiter $$

create procedure  proc_test() 
begin  
declare i int;
set i = 1; 
while i < 100000 
do  insert into test(name) values (concat('bob',i)); 
set i = i +1; 
end while; 
end$$

delimiter ;
           

session1 以事務整體執行存儲過程	session2 獨立執行insert
> begin;> call proc_test;> commit;	> call proc_test;
耗時如下：	耗時如下：
Query OK, 1 row affected (2.99 sec)	Query OK, 1 row affected (2 min 18.05 sec)

事務的隔離級别

• READ UNCOMMITTED 可讀取到未送出資料，産生髒讀
• READ COMMITTED 可讀取到送出資料，但未送出資料不可讀，産生不可重複讀，即可讀取到多個送出資料，導緻每次讀取資料不一緻
• REPEATABLE READ 可重複讀，多次讀取資料都一緻，産生幻讀，即讀取過程中，即使有其它送出的事務修改資料，仍隻能讀取到未修改前的舊資料。此為MySQL預設設定
• SERIALIZABILE 可串行化，未送出的讀事務阻塞修改事務，或者未送出的修改事務阻塞讀事務。導緻并發性能差
• MVCC: 多版本并發控制，和事務級别相關
• 說明： REPEATABLE READ最适合資料庫的備份。備份是希望備份某個點的資料庫狀态。

事務隔離級别	髒讀可能性	不可重複讀可能性	幻讀可能性	加鎖讀
讀未送出（read-uncommitted）	是	是	是	否
不可重複讀（read-committed）	否	是	是	否
可重複讀（repeatable-read）	否	否	是	否
串行化（serializable）	否	否	否	是

髒資料：髒資料在臨時更新(髒讀)中産生。即一個事務讀取了另一個事務未送出的資料。
READ-UNCOMMITTED：髒讀
    髒資料可讀
READ-COMMITTED：不可重複讀
    即可能不同時間讀取的記錄不相同，出現幻讀
REPEATABLE READ：可重複讀
    讀取事務開始時刻的資料庫狀态，在整個事務過程中記錄不變化
SERIALIZABILE 可串行化
    未送出的讀事務阻塞修改事務以及未送出的修改事務阻塞讀事務
           

事務隔離級别相關的設定

• 事務隔離級别系統變量：tx_isolation

• 事務隔離級别伺服器選項：

  transaction-isolation=[READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE]

• 系統預設的事務隔離級别是REPEATABLE-READ

實驗：READ-UNCOMMITTED

• [mysqld]

  transaction-isolation=READ-UNCOMMITTED

session1	session2
>begin;	>begin;
> insert students(stuid,name,age)values(32,‘Wu Ming’,35);	> select * from students where name like ‘W%’;
事務未送出	記錄可查詢
>rollback;	記錄不不可查詢
commit;

實驗：READ-UNCOMMITTED

• ransaction-isolation=READ-COMMITTED

session1	session2
>begin;	>begin;
> insert students(stuid,name,age)values(35,‘Duan lang’,35);
還未送出的事務
> select * from students where name like ‘duan%’;	> select * from students where name like ‘duan%’;
可查詢	不可查詢
> commit;送出事務
> select * from students where name like ‘duan%’;	> select * from students where name like ‘duan%’;
可查詢	可查詢
>begin; 再次開啟一個事務
> insert students(stuid,name,age)values(36,‘Duan Yu’,30);
> commit;送出事務
> select * from students where name like ‘duan%’;
可查詢的記錄增加
此session中同樣的查詢，結果不同，即不可重複讀
> commit;

實驗：REPEATABLE-READ

• ransaction-isolation=REPEATABLE-READ

session1	session2
>begin;	>begin;
>insert students(stuid,name,age)values(36,‘Qiao Feng’,40);
可查詢	> select * from students;
不可查詢
> commit; 送出事務
不可查詢
可重複讀，但實際上資料已經改變，幻讀。
> commit;

實驗：SERIALIZABLE

session1	session2
>begin;	>begin;
> select * from students;	> select * from students;
可同時讀	可同時讀
> select * from students;
>insert students(stuid,name,age)values(38,‘Liu Bei’,50);
讀阻塞改，阻塞一定時間，若其他人還不送出事務，報錯
> commit;	> commit;
> begin;	> begin;
>insert students(stuid,name,age)values(38,‘Liu Bei’,50);
> select * from students;
更改阻塞讀,阻塞一定時間，若其他人還不送出事務，報錯
阻塞時間内送出事務	可執行，包含立即更改的資料均可查詢

死鎖

• 兩個或多個事務在同一資源互相占用，并請求鎖定對方占用的資源的狀态

session1	session2
> begin;	> begin;
> update teachers set age=50 where tid=4;	> update students set age=10 where stuid=39;
修改成功，innodb會加行級鎖	修改成功，innodb會加行級鎖
此時session1對session2事務中修改的行stuid=39再修改時	此時session2對session1事務中修改的行tid=4再修改時
死鎖	死鎖
> update students set age=100 where stuid=39;	> update teachers set age=10 where tid=4;
阻塞	阻塞
系統會發現死鎖，然後将其中一個事務進行復原	系統會發現死鎖，然後将其中一個事務進行復原
—	—
Query OK, 1 row affected (7.46 sec)	Lock wait timeout exceeded; try restarting transaction

可能出現這種場景：當一個事務執行改操作時，innodb預設添加行鎖，但是事務一直未送出

show processlist and kill #

session1	session2	session3
> begin;	-	-
> update teachers set age=50 where tid=4;	-	-
session1一直不送出事務，行鎖一直存在	-	-
-	session2想對同一行進行修改時	-
-	> update teachers set age=30 where tid=4;	-
-	因為行級鎖的原因會阻塞	-
-	-	> show processlist;
-	-	查詢哪些線程正在運作
-	-	需要幹預，将session1的事務關閉
-	-	> kill id（kill session1對應的id）
-	-	釋放行級鎖
-	-	不能允許這種事務一直不送出，将行鎖一直開啟，影響其他session通路

事務日志簡介

• 事務日志檔案： ib_logfile0， ib_logfile1
• 事務日志的寫入類型為“追加”，是以其操作為“順序IO”；通常也被稱為：預寫式日志 write ahead logging
• 注：事務即使沒有送出，事務日志仍會記錄
• 預設 ib_logfile0， ib_logfile1 兩個檔案固定大小為5M，檔案被寫滿，前面的日志将會被覆寫
• 查詢不會記錄事務日志，事務日志隻記錄增删改操作即DDL（insert,update,delete）
• 事務日志記錄的是對應具體修改的哪個磁盤塊，并不是記錄SQL語句
• 關于事務日志詳情見mysql日志