上篇文章說了acid四個事務的特性,原子性保證要不兩個sql一起執行,要麼不執行,隔離性,兩個事務之間必須互不幹擾,一緻性,兩邊的資料必須保持一緻,可以說一緻性的前提是原子性和隔離性必須正常,但原子性和隔離性都正常,就能保證一緻性嗎?并不是,還必須滿足其他一些限制,比如金額不能為負數。持久性就是必須持久化到磁盤才算事務成功。
transaction (1)—mysql進階(五十七)
Mysql中事務文法
前面我們重點介紹了理論知識,那麼我們在mysql裡如何使用呢?
開啟事務
我們可以用指令開啟事務:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
複制
Begin開啟事務或者start transaction開啟事務。
不過start transaction 語句後面可以跟随幾個修飾符,就是他們幾個,
Read only:表示目前事務是隻能讀取資料,不能修改資料。(如果我們使用create temporary table建立的臨時表,由于他們在目前會話可見,是以在隻讀事務裡,也可以對臨時表增,删,改)
Read write:表示目前事務是一個讀寫事務,也可以修改表。
With consistent snapshot :啟動一緻性讀。
//開啟一個事務
START TRANSACTION READ ONLY;
//開啟多個事務
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
注意:一個事務不能同時設定隻讀和讀寫,這樣就有沖突了,另外,如果我們沒有顯式指定開啟什麼通路模式,那麼預設就是讀寫通路模式。
送出事務
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE account SET balance = balance - 10 WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE account SET balance = balance + 10 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings:
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
複制
一個完整的事務過程,開啟之後,必須commit之後,才會重新整理到磁盤。
手動中止事務
如果我們在執行sql的時候,寫到一半,發現錯了怎麼辦呢?比如狗給貓轉10元,但是寫成了轉100,這時候我們隻要吧上面的commit換成rollback就好。
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
值得注意的是,如果在平時開發中,遇到異常,mysql會自動幫我們復原,這裡的復原是我們自己手動的。
支援事務的存儲引擎
我們前面說了,mySIAM是不支援事務的,而支援事務的存儲引擎是innoDB,如果開啟事務後,mySIAM存儲引擎insert into一條資料,然後rollback,這時候 資料不會復原,查詢會查到剛那條資料。但如果是innoDB開啟事務後,insert into一條資料,然後rollback,這時候資料會復原,不會查詢到那條資料。
自動送出
Mysql有個系統變量autocommit,
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
複制
這裡預設是on,但這個on代表的是每條sql語句有一個獨立的事務,這種特性稱為事務的自動送出。加入狗給貓轉了10元錢,貓加了10元錢,這兩個sql語句是在不同的獨立事務進行的。
當然如果想關閉這種自動送出,讓他們在同一個事務裡,可以顯式用begin或者start straction啟動,或者set autocommit 為off。
這樣我們寫的sql都在同一個事務裡面,直到我們顯式commit或者rollback。
隐式送出
前面說過,當我們顯式start transaction 和begin ,或者吧系統變量autocommit設定成off,則不會自動送出事務,但如果我們 沒有寫commit或者rollback,有的語句也會觸發送出事務,這些就稱為隐式送出。
當我們定義或者修改資料庫對象的資料定義語言(DDL):所謂的資料庫對象,指的是資料庫,表,視圖,存儲過程等等這些東西,當我們使用create ,alter,drop等文法對這些資料庫對象進行操作的時候,就會隐式送出事務。
BEGIN;
SELECT ... # 事務中的一條語句
UPDATE ... # 事務中的一條語句
... # 事務中的其它語句
複制
CREATE TABLE ... # 此語句會隐式的送出前邊語句所屬于的事務
隐式使用或修改mysql資料庫的表
當我們使用alert user,create user,drop user,grant,rename user,revoke,set password等語句也會隐式送出這些内容。
事務控制或關于鎖定的語句
當我們事務開沒送出,在sql之後,又寫了一個start transaction或者begin,會隐式送出前面的sql。
如果吧autocommit 改為on,也會送出事務。
如果有lock tables,unlock tables,也會送出事務。
Mysql複制的語句
使用start slave,stop slave,reset slave,change master to 等語句也會觸發送出。
還有很多其他語句也會觸發。
儲存點
當我們寫了一大堆sql,發現其中一個sql寫錯,然後就rollback,全部恢複,這樣總有一夜回到解放前的感覺,怎麼解決這個困境呢?
我們可以在sql執行完一段時候,寫一個儲存點,然後rollback to 儲存點。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set uid = '55' where id = '3';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint s1;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set uid = '55' where id = '4';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback to s1;
Query OK, 0 rows affected (0.00 sec)
複制
大家可以看到,這裡就回到了儲存點,儲存點前面的sql全部都執行了。
RELEASE SAVEPOINT 儲存點名稱;