MySQL 事務的學習整理

 

事務是資料庫區別檔案系統的重要特徵之一。在檔案系統中，如果你正在寫檔案，但是作業系統突然崩潰了，這個時候檔案有可能會被損壞的，當然也會有一些機制讓檔案恢複到某一個時間點，比如依靠原有的備份等。

 

1 引入事務的目的

資料庫系統引入事務的主要目的：事務會把資料庫從一種一致狀態轉換成另外一種狀態。在資料庫提交工作時，可以確保其要麼所有修改都已經儲存了，要麼所有修改都不儲存。

 

2 ACID簡介

InnoDB儲存引擎中的事務完全符合ACID的特性。ACID是如下：

       原子性是指只有使事務中所有的資料庫操作執行都成功，才算整個事務成功。只要有一個

    資料庫在事務開始之前和結束之後，資料庫的完整性條件約束沒有被破壞。

      

       資料庫

 

3 事務的實現

       隔離性 (isolation)通過鎖來實現，其他3個原子性atomicity，一致性consistency，持久性durability通過資料庫的redo和undo來完成。

 

3.1  redo

         在Innodb儲存引擎中，交易記錄通過redo日至檔案和Innodb儲存引擎的日誌緩衝(InnoDBLog Buffer)來實現。當開始一個事務時候，會紀錄該事務的一個LSN（Log Sequence Number記錄序號）；當事務執行時候，會往InnoDB儲存引擎的日誌緩衝裡插入交易記錄；當事務提交時，必須將InnoDB儲存引擎的日誌緩衝寫入disk（預設的實現，即innodb_flush_log_at_trx_commit=1）。也就是在寫資料之前，需要先寫日誌，這種方式稱為預寫記錄檔方式WAL（Write-Ahead Logging）。

         InnoDB儲存引擎通過預寫記錄檔的方式來保證事務的完整性。這意味著disk上儲存的資料頁和記憶體緩衝池中的頁是不同步的，對於記憶體緩衝池中頁的修改，先是寫入redo記錄檔，然後再寫入磁碟，因此是一種非同步方式，可以通過show engine innodb status;來觀察當前disk和log的差距：

Create table z (a int,primary key(a))engine=innodb;

 

DELIMITER $$

Use test $$

DROP PROCEDURE IF EXISTS`test`.`load_test`$$

CREATE PROCEDURE load_test(COUNT INT)

BEGIN

 DECLARE i INT UNSIGNED DEFAULT 0;

 START TRANSACTION;

 WHILE i < COUNT DO

         REPLACEINTO z SELECT i;  -- 用replace是因為需要重複調用，避免主鍵重複insert報錯。

     SET i=i+1;

         ENDWHILE;

 COMMIT;

END $$

DELIMITER ;

 

3.2 undo

undo的記錄正好與redo的相反，insert變成delete，update變成相反的update，redo放在redo file裡面。而undo放在一個內部的一個特殊segment上面，儲存與共用資料表空間內（ibdata1或者ibdata2中）。參考：http://blog.csdn.net/mchdba/article/details/8664943

，因為它是通過執行相反的dml語句來實現的。而且不會回收因為insert和upate而新增加的page頁的，undo頁的回收是通過masterthread線程來實現的。

 

 

4 事務型控制語句

在mysql命令列的預設下，事務都是自動認可的，sql語句提交後馬上會執行commit操作。因此開啟一個事務必須使用begin，start transaction，或者執行 set autocommit=0; 可以使用的事務控制語句

starttransction | begin : 顯示的開啟一個事務，參考http://blog.csdn.net/mchdba/article/details/8690935

    隱式提交的sql語句：（1）ddl語句，alter database…,alter event, alter procedure,alter table ,alterview,create table,drop table,rename table ,truncate table等；（2）修改mysql架構的語句，createuser,drop user,grant,rename user,revoke,set password。（3）管理語句，analyze table,cache index,check table,load index into cache,optimizetable,repair table等。

    [truncate table 語句是ddl，不能rollback的，這點和mssqlserver不同。]

5 對事務操作的統計

Innodb支援事務的，因此對於InnoDB儲存引擎的應用，在考慮每秒請求數（Question Per Second，QPS）的同時，我們更應該關注TPS，即是每秒交易處理的能力（Transaction Per Second，TPS）。

         計算TPS的方法（com_commit+com_rollback）/tim。這種計算方法必須有一個前提條件是：所有的事務必須是顯示提交的，如果存在隱式的提交和會滾（預設autocommit=1，或者value為on）

mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON   |+---------------+-------+1 row in set (0.00 sec) mysql>mysql> show global status like'com_commit';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_commit    | 0    |+---------------+-------+1 row in set (0.00 sec) mysql> use test;Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| t              || t1             || t3             || z              |+----------------+4 rows in set (0.00 sec) mysql> insert into z select 1;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0  Warnings: 0 mysql> select * from z;+---+| a |+---+| 1 |+---+1 row in set (0.00 sec) mysql> show global status like'com_commit';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_commit    | 0    |+---------------+-------+1 row in set (0.00 sec) mysql>

 

         另外mysql還有2個參數 handler_commit和handler_rollback，這2個參數在mysql5.1種可以很好的統計innodb的顯示和隱式的事務提交操作，而在innodb plugin中這2個參數的統計值有些怪異，不能很好的統計事務的次數。

mysql> show global status like'Handler_commit';+----------------+-------+| Variable_name  | Value |+----------------+-------+| Handler_commit | 17    |+----------------+-------+1 row in set (0.00 sec) mysql> show global status like'Handler_rollback';+------------------+-------+| Variable_name    | Value |+------------------+-------+| Handler_rollback | 0     |+------------------+-------+1 row in set (0.00 sec) mysql>