MySQL 事務的學習整理

 

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

 

1 引入事務的目的

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

 

2 ACID簡介

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

原子性 (atomicity)

       原子性是指一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。只有使事務中所有的資料庫操作執行都成功，才算整個事務成功。只要有一個sql語句執行失敗，那麼在這個事務中已經執行的sql語句都必須撤銷，資料庫狀態應該退回到執行事務前的狀態。

一致性(consistency)

    事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。在事務開始之前和結束之後，資料庫的完整性約束沒有被破壞。

隔離性 (isolation)

       一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的資料對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾，這些透過鎖來實現。

永續性(durability) 

       持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障（比如說當機等）不應該對其有任何影響。

 

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

undo不是物理恢復，是邏輯恢復，因為它是透過執行相反的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 changed

mysql> 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>

可以看出來Com_commit 始終為0 ，這就是隱式提交的時候，不計入com_commit和com_rollback中的。

 

         另外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>

因此如果都是顯示控制事務的提交和會滾得話，可以用com_commit com和rollback這2個引數，否則情況就有些複雜。