MySQL中事物的詳解

標籤：不成功   roc   test   mys   詳解   poi   show   主鍵約束   epo   

1.1. 事物的定義及特性

事務是一組操作資料庫的SQL語句組成的工作單元，該工作單元中所有操作要麼同時成功，要麼同時失敗。事物有如下四個特性，ACID簡稱“酸性”。

1）原子性：工作單元中所有的操作要麼都成功，要麼都不成功，不會出現部分成功的情況。

2）一致性：工作完成其結果應與預期一致，比如由A賬戶向B賬戶轉賬的事物，若該事物執行成功則必須保證A賬戶轉出多少錢,B賬戶相應轉入多少錢；若該事物失敗，則此次轉賬即失敗。事物的其它三個性質都是為了保證該一致性的。

3）隔離性：隔離性還可以稱為並發控制、可序列化、鎖等。事物中所操作的資料要隔離起來，以防止其他使用者訪問這些資料而帶來的不一致情況。

4）持久性：事務一旦提交，其所做的修改就會永久儲存到資料庫中，即使資料庫發生故障也不應該對其有任何影響。

事務的持久性不能做到100%的持久，只能從事務本身的角度來保證永久性，而一些外部原因導致資料庫發生故障，如硬碟損壞，那麼所有提交的資料可能都會丟失。

1.2. MySQL中開啟事務的方法1.2.1. 方法一

用BEGIN或START TRANSACTION來開啟一個事物，COMMIT或ROLLBACK來結束該事物。

1. -- 儲存點 savepoint  
2. begin  
3.   declare is_error int default false;#是否出錯的標誌  
4.   declare continue handler for sqlexception  
5.   set is_error=true;#聲明例外處理常式，如果sql異常，則把標誌為設定為true  
6.   start TRANSACTION;#開啟事務，則會同時失敗，同時成功  
7.   savepoint s1;#建立儲存點  
8.   insert into employee(id,name,salary) values(146,‘cq‘,9000);  
9.   savepoint s2;  
10.   insert into employee(id,name,salary) values(101,‘cq‘,9000);  
11.   insert into employee(id,name,salary) values(102,‘cq‘,9000);  
12.   if is_error THEN  
13.   rollback to savepoint s1;-- 還原到s1  
14.   insert into employee(id,name,salary) values(151,‘cq‘,9000);  
15.   insert into employee(id,name,salary) values(152,‘cq‘,9000);  
16.   commit;  
17.   end if;  
18.   end;  

1.2.2. 方法二

關閉自動認可，設定SET AUTOCOMMIT = 0，該語句後的所有操作都將變成事物操作，而且關閉自動認可的情況下，每個事物結束其後續操作都將開啟新的事物。

1. set autocommit=0;#關閉自動認可  
2. #因為關閉了自動認可事務，則添加資料不會儲存到資料庫中  
3. insert into employee(id,name,salary) values(143,‘cq‘,9000);  
4. commit;#手動提交所有未執行的資料 
5. #由於是關閉自動認可的方式開啟的事務，所以每個事物結束其後的操作自動開啟新的事物
6. insert into employee(id,name,salary) values(258,‘wd‘,8000); #該操作屬於新啟的事物

1.3. 事物的隱式提交

由於事物不能被嵌套，所以當新事物開啟時其前的舊事物會被隱式提交。如下情況會導致事物被隱式提交：

1)新事物的開啟會導致舊事物的隱式提交

START TRANSACTION;

INSERT INTO `dm_性別`(性別名稱) VALUES(‘不限‘);#該操作會被隱式提交

START TRANSACTION;

INSERT INTO `dm_性別`(性別名稱) VALUES(‘男女‘);

ROLLBACK;

2)InnoDB中所有的DDL或DCL操作都會開啟一個新的事物，所以DDL或DCL語句會導致舊事物的隱式提交

SET AUTOCOMMIT = 0;#利用法二關閉自動認可來開啟事務 

BEGIN;  

INSERT INTO t1 VALUES (1); 

#該DDL語句會導致其前面的插入操作隱式提交 ，並開啟一單一的事物

CREATE TABLE t2 (pk int primary key);

INSERT INTO t2 VALUES (2); #自動開啟新的事物 

ROLLBACK; #插入表t1的資料已提交，僅能復原插入表t2的操作

3)過程的執行區結束End之前會有一次隱式提交

BEGIN

START TRANSACTION;

INSERT INTO `dm_性別`(性別名稱) VALUES(‘不限‘);

INSERT INTO `dm_性別`(性別名稱) VALUES(‘男女‘);

END #在此之前會導致事物的隱式提交

1.4. 有關事物操作的注意事項

①  預存程序的執行區Begin會開啟一個事物，執行區結束End會隱式提交一次

BEGIN

INSERT INTO `dm_性別`(性別名稱) VALUES(‘不限‘);

INSERT INTO `dm_性別`(性別名稱) VALUES(‘男女‘);

## COMMIT隱式提交該執列區域的操作

END

②  不要在事物的中途進行提交操作，一方面會破壞事物的原子性 ，另一方面該事物會到此結束

create table testproc(id int(4) primary key, name varchar(100));

#測試過程

CREATE PROCEDURE test_proc_ins( 

IN i_id INT, 

IN i_name VARCHAR(100) 

) 

BEGIN 

start transaction; #本意是將兩次插入操作捆綁成一個事物 

     INSERT INTO testproc VALUES (i_id, i_name); 

COMMIT;#由於中途提交導致該事物提交前結束，其後的操作不再是事物操作

      INSERT INTO testproc VALUES (i_id, i_name); #這裡故意違反主鍵約束

ROLLBACK;#由於第一條插入資料的操作已提交，故這裡的ROLLBACK無效

END;

③  由於DDL或DCL操作會建立新的事物，這導致其前的操作會隱式提交，從而破壞事物的原子性，所以盡量不要在過程中使用DDL或DCL語句。而且在過程中使用DDL或DCL語句的文法是比較複雜的，所以不建議在過程中使用DDL或DCL語句。

SET AUTOCOMMIT = 0;  

BEGIN;  

INSERT INTO t1 VALUES (1);  

#該DDL語句開啟新事物會隱式提交其前的事物 

CREATE TABLE t2 (pk int primary key); INSERT INTO t2 VALUES (2);  

ROLLBACK;  

SHOW TABLES

④  Start Transaction(Begin)與閉自動認可開啟事務的區別：Start Transaction只開啟了一個當前事物，該事物結束其後的操作將不再是事物操作；但關閉自動認可的方式開啟事務，每個事物結束其後的操作自動預設為新的事物操作。

⑤  MySQL的事務支援不是綁定在MySQL伺服器本身，而是與儲存引擎相關：

1.MyISAM：不支援事務，用於唯讀程式提高效能

2.InnoDB：支援ACID事務、行級鎖、並發

3.Berkeley DB：支援事務

MySQL中事物的詳解