MySQL資料庫的事務處理用法與例項程式碼詳解

鹹魚想翻身發表於2019-03-07
MySql資料庫

　　文章主要介紹了MySQL事務處理用法與例項程式碼詳解，詳細的介紹了事物的特性和用法並實現php和mysql事務處理例子，非常具有實用價值，需要的朋友可以參考下

　　MySQL的事務支援不是繫結在MySQL伺服器本身，而是與儲存引擎相關

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

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

　　3.Berkeley DB：支援事務

　　一個事務是一個連續的一組資料庫操作，就好像它是一個單一的工作單元進行。換言之，永遠不會是完整的事務，除非該組內的每個單獨的操作是成功的。如果在事務的任何操作失敗，則整個事務將失敗。

　　實際上，會俱樂部許多SQL查詢到一個組中，將執行所有的人都一起作為事務的一部分。

事務的特性：

　　事務有以下四個標準屬性的縮寫ACID，通常被稱為：

　　原子性: 確保工作單元內的所有操作都成功完成，否則事務將被中止在故障點，和以前的操作將回滾到以前的狀態。

　　一致性: 確保資料庫正確地改變狀態後，成功提交的事務。

　　隔離性: 使事務操作彼此獨立的和透明的。

　　永續性: 確保提交的事務的結果或效果的系統出現故障的情況下仍然存在。

　　在MySQL中，事務開始使用COMMIT或ROLLBACK語句開始工作和結束。開始和結束語句的SQL命令之間形成了大量的事務。

COMMIT & ROLLBACK:

　　這兩個關鍵字提交和回滾主要用於MySQL的事務。

　　當一個成功的事務完成後，發出COMMIT命令應使所有參與表的更改才會生效。

　　如果發生故障時，應發出一個ROLLBACK命令返回的事務中引用的每一個表到以前的狀態。

　　可以控制的事務行為稱為AUTOCOMMIT設定會話變數。如果AUTOCOMMIT設定為1(預設值)，然後每一個SQL語句(在事務與否)被認為是一個完整的事務，並承諾在預設情況下，當它完成。 AUTOCOMMIT設定為0時，發出SET AUTOCOMMIT =0命令，在隨後的一系列語句的作用就像一個事務，直到一個明確的COMMIT語句時，沒有活動的提交。

　　可以透過使用mysql_query()函式在PHP中執行這些SQL命令。

通用事務例子

　　這一系列事件是獨立於所使用的程式語言，可以建立在任何使用的語言來建立應用程式的邏輯路徑。

　　可以透過使用mysql_query()函式在PHP中執行這些SQL命令。

BEGIN WORK開始事務發出SQL命令

　　發出一個或多個SQL命令，如SELECT，INSERT，UPDATE或DELETE

　　檢查是否有任何錯誤，一切都依據的需要。

　　如果有任何錯誤，那麼問題ROLLBACK命令，否則發出COMMIT命令。

在MySQL中的事務安全表型別：

　　如果打算使用MySQL事務程式設計，那麼就需要一種特殊的方式建立表。有很多支援事務但最流行的是InnoDB表型別。

　　從原始碼編譯MySQL時，InnoDB表支援需要特定的編譯引數。如果MySQL版本沒有InnoDB支援，請網際網路服務提供商建立一個版本的MySQL支援InnoDB表型別，或者下載並安裝Windows或Linux/UNIX的MySQL-Max二進位制分發和使用的表型別在開發環境中。

　　如果MySQL安裝支援InnoDB表，只需新增一個的TYPE=InnoDB 定義表建立語句。例如，下面的程式碼建立InnoDB表tcount_tbl：?

  1. root@host# mysql -u root -p password;
  2. Enter password:*******
  3. mysql> use TUTORIALS;
  4. Database changed
  5. mysql> create table tcount_tbl
  6. -> (
  7. -> tutorial_author varchar(40) NOT NULL,
  8. -> tutorial_count INT
  9. -> ) TYPE=InnoDB;
  10. Query OK, 0 rows affected (0.05 sec)

　　可以使用其他GEMINI或BDB表型別，但它取決於您的安裝，如果它支援這兩種型別。

　　由於專案設計裡面，牽扯到了金錢的轉移，於是就要用到MYSQL的事務處理，來保證一組處理結果的正確性。用了事務，就不可避免的要犧牲一部分速度，來保證資料的正確性。

只有InnoDB支援事務

　　事務 ACID Atomicity(原子性)、Consistency(穩定性)、Isolation(隔離性)、Durability(可靠性)

　　1、事務的原子性

　　一組事務，要麼成功;要麼撤回。

　　2、穩定性

　　有非法資料(外來鍵約束之類)，事務撤回。

　　3、隔離性

　　事務獨立執行。

　　一個事務處理後的結果，影響了其他事務，那麼其他事務會撤回。

　　事務的100%隔離，需要犧牲速度。

　　4、可靠性

　　軟、硬體崩潰後，InnoDB資料表驅動會利用日誌檔案重構修改。

　　可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit選項 決定什麼時候吧事務儲存到日誌裡。

開啟事務

  1. START TRANSACTION 或 BEGIN

提交事務（關閉事務）

  1. COMMIT

放棄事務（關閉事務）

  1. ROLLBACK

折返點

  1. SAVEPOINT adqoo_1
  2. ROLLBACK TO SAVEPOINT adqoo_1

　　發生在折返點 adqoo_1 之前的事務被提交，之後的被忽略

事務的終止

　　設定“自動提交”模式

　　SET AUTOCOMMIT = 0

　　每條SQL都是同一個事務的不同命令，之間由 COMMIT 或 ROLLBACK隔開

　　掉線後，沒有 COMMIT 的事務都被放棄

事務鎖定模式

　　系統預設： 不需要等待某事務結束，可直接查詢到結果，但不能再進行修改、刪除。

　　缺點：查詢到的結果，可能是已經過期的。

　　優點：不需要等待某事務結束，可直接查詢到結果。

需要用以下模式來設定鎖定模式

1、SELECT …… LOCK IN SHARE MODE（共享鎖）

　　查詢到的資料，就是資料庫在這一時刻的資料(其他已commit事務的結果，已經反應到這裡了)

　　SELECT 必須等待，某個事務結束後才能執行

2、SELECT …… FOR UPDATE（排它鎖）

　　例如 SELECT * FROM tablename WHERE id<200

　　那麼id<200的資料，被查詢到的資料，都將不能再進行修改、刪除、SELECT …… LOCK IN SHARE MODE操作

　　一直到此事務結束

　　共享鎖 和 排它鎖 的區別：在於是否阻斷其他客戶發出的 SELECT …… LOCK IN SHARE MODE命令

3、INSERT / UPDATE / DELETE

　　所有關聯資料都會被鎖定，加上排它鎖

4、防插入鎖

　　例如 SELECT * FROM tablename WHERE id>200

　　那麼id>200的記錄無法被插入

5、死鎖

　　自動識別死鎖

　　先進來的程式被執行，後來的程式收到出錯訊息，並按ROLLBACK方式回滾

　　innodb_lock_wait_timeout = n 來設定最長等待時間，預設是50秒

事務隔離模式

　　SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL

　　READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

　　1、不帶SESSION、GLOBAL的SET命令

　　只對下一個事務有效

　　2、SET SESSION

　　為當前會話設定隔離模式

　　3、SET GLOBAL

　　為以後新建的所有MYSQL連線設定隔離模式(當前連線不包括在內)

　　隔離模式

　　READ UNCOMMITTED

　　不隔離SELECT

　　其他事務未完成的修改(未COMMIT)，其結果也考慮在內

　　READ COMMITTED

　　把其他事務的 COMMIT 修改考慮在內

　　同一個事務中，同一 SELECT 可能返回不同結果

　　REPEATABLE READ(預設)

　　不把其他事務的修改考慮在內，無論其他事務是否用COMMIT命令提交過

　　同一個事務中，同一 SELECT 返回同一結果(前提是本事務，不修改)

　　SERIALIZABLE

　　和REPEATABLE READ類似，給所有的SELECT都加上了 共享鎖

出錯處理

　　根據出錯資訊，執行相應的處理

mysql事物處理例項

MYSQL的事務處理主要有兩種方法

　　1.用begin,rollback,commit來實現

　　begin開始一個事務

　　rollback事務回滾

　　commit 事務確認

　　2.直接用set來改變mysql的自動提交模式

　　mysql預設是自動提交的，也就是你提交一個query，就直接執行!可以透過

　　set autocommit = 0 禁止自動提交

　　set autocommit = 1 開啟自動提交

　　來實現事務的處理。

　　但要注意當用set autocommit = 0 的時候，你以後所有的sql都將作為事務處理，直到你用commit確認或 rollback結束，注意當你結束這個事務的同時也開啟了新的事務!按第一種方法只將當前的做為一個事務!

　　MYSQL只有 INNODB和BDB型別的資料表才支援事務處理，其他的型別是不支援的!?

  1. mysql> use test;
  2. Database changed
  3. mysql> CREATE TABLE `dbtest`(
  4. -> id int(4)
  5. -> ) TYPE=INNODB;
  6. Query OK, 0 rows affected, 1 warning (0.05 sec)
  7.  
  8. mysql> select * from dbtest
  9. -> ;
  10. Empty set (0.01 sec)
  11.  
  12. mysql> begin;
  13. Query OK, 0 rows affected (0.00 sec)
  14.  
  15. mysql> insert into dbtest values(5);
  16. Query OK, 1 row affected (0.00 sec)
  17.  
  18. mysql> insert into dbtest value(6);
  19. Query OK, 1 row affected (0.00 sec)
  20.  
  21. mysql> commit;
  22. Query OK, 0 rows affected (0.00 sec)
  23.  
  24. mysql> select * from dbtest;
  25. +------+
  26. | id |
  27. +------+
  28. | 5 |
  29. | 6 |
  30. +------+
  31. 2 rows in set (0.00 sec)
  32.  
  33. mysql> begin;
  34. Query OK, 0 rows affected (0.00 sec)
  35.  
  36. mysql> insert into dbtest values(7);
  37. Query OK, 1 row affected (0.00 sec)
  38.  
  39. mysql> rollback;
  40. Query OK, 0 rows affected (0.00 sec)
  41.  
  42. mysql> select * from dbtest;
  43. +------+
  44. | id |
  45. +------+
  46. | 5 |
  47. | 6 |
  48. +------+
  49. 2 rows in set (0.00 sec)

mysql事務處理

　　php程式碼實現事務的處理可以透過PHP預定義類mysqli的以下方法實現。

　　autocommit(boolean)：該方法用於限定查詢結果是否自動提交，如果該方法的引數為true則自動提交，如果引數為false則關閉自動提交。MySQL資料庫預設為自動提交。

　　rollback()：利用mysqli類中的該方法可以實現事務的回滾。

　　commit()：利用該方法可以實現提交所有查詢。

  1. 　<?php
  2. include_once("conn.php");
  3.  
  4. $id=$_GET[id];
  5. $conn->autocommit(false);
  6. if(!$conn->query("delete from tb_sco where id='".$id."'"))
  7. {
  8. $conn->rollback();
  9. }
  10. if(!$conn->query("delete from tb_stu where id='".$id."'"))
  11. {
  12. $conn->rollback();
  13. }
  14. $conn->commit();
  15. $conn->autocommit(true);
  16. echo "ok"
  17. ?>
  1. 　　<?php
  2. require('connectDB.php'); //建立資料庫連線
  3. MsSQL_query("BEGIN TRANSACTION DEPS02_DEL"); //開始事務
  4. $delete_dep_sql="DELETE FROM TBLDEPARTMENT WHERE DEPTID='{$_GET[deptid]}'";
  5. // echo $delete_dep_sql."<br>";
  6. mssql_query($delete_dep_sql); //運算元據庫
  7. // var_dump($del_result);
  8. $delete_result = mssql_query("select @@ROWCOUNT as id");
  9. $delete_info = mssql_fetch_array($delete_result);
  10. $delete_rows = $delete_info[0];
  11. // var_dump($delete_rows);
  12. mssql_free_result($delete_result);
  13. echo "<script language=javascript>";
  14. if(true){ //判斷是否回滾提交
  15. mssql_query("COMMIT TRANSACTION DEPS02_DEL"); //提交事務
  16. echo "alert('delete success!');";
  17. }else{
  18. mssql_query("ROLLBACK TRANSACTION DEPS02_DEL"); //回滾事務
  19. echo "alert('delete faile!');";
  20. }
  21. echo "</script>";mssql_close();
  22. ?>

　　MySQL的事務處理在處理實際問題中有著廣泛且重要的應用，最常見的應用如銀行轉賬業務、電子商務支付業務等等。但是，值得注意的是，MySQL的事務處理功能在MYSIAM儲存引擎中是不支援的，在InnoDB儲存引擎中是支援的。現在上傳一段程式碼，作為引導認識MySQL事務處理的開始，簡單的例項，但融匯思想，相信會有很大的幫助。?

  1. <?php
  2. $conn=mysql_connect('localhost','root','yourpassword')or die(mysql_error());
  3. mysql_select_db('transaction',$conn);
  4. mysql_query('set names utf8');
  5.  
  6. //建立事務
  7. mysql_query('START TRANSACTION') or die(mysql_error());
  8. $sqlA="update A set account=account-1";
  9. if(!mysql_query($sqlA)){
  10. mysql_query('ROLLBACK') or exit(mysql_error());//判斷當執行失敗時回滾
  11. exit();
  12. }
  13. $sqlB="update B set account=account+1";
  14. if(!mysql_query($sqlB)){
  15. mysql_query('ROLLBACK') or exit(mysql_error());//判斷當執行失敗時回滾
  16. exit();
  17. }
  18. mysql_query('COMMIT')or die(mysql_error());//執行事務
  19. mysql_close($conn);
  20. ?>

　　以上程式碼可以作為模擬銀行轉賬業務的事務流程。以表A、B分別表示兩個已在銀行開戶的賬戶，當賬戶A執行轉出1元給賬戶B的操作時，如果操作執行失敗，轉出將會回滾至原始狀態，不繼續向下執行動作。反之，如果操作執行成功，則賬戶B可用餘額將增加1元，否則事務回滾至原始狀態。

相關文章