您好,登錄后才能下訂單哦!
本文主要給大家簡單講講Mysql數(shù)據(jù)庫事務(wù)的特性及運用,相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下,這里就不涉獵了,我們就直奔主題吧,希望Mysql數(shù)據(jù)庫事務(wù)的特性及運用這篇文章可以給大家?guī)硪恍嶋H幫助。
2.1.多事務(wù)同時執(zhí)行:
彼此之間互不影響的方式進行并行;事務(wù)之間交互,通過數(shù)據(jù)集。
START TRANSACTION: 啟動事務(wù)命令 數(shù)據(jù)庫只有啟動了事務(wù)才允許回滾撤銷等操作。
且數(shù)據(jù)的engine引擎必須是innodb,才支持事務(wù)
ROLLBACK : 回滾事務(wù),mysql只要沒有提交開啟了事務(wù)都可以進行回滾操作。
COMMIT: 事務(wù)提交,事務(wù)提交后無法進行回滾操作。
若未明確啟動事務(wù):autocommit 能實現(xiàn)自動提交,每一個操作都直接提交;
強烈建議:明確使用事務(wù),否則所有操作都被當成一個事務(wù)來處理,并關(guān)閉自動提交。
否則浪費mysql很多IO操作,每寫一條語句都執(zhí)行提交至持久性存儲,很浪費資源
2.2.事務(wù)的特性:
2.2.1.Atomicity: 原子性
事務(wù)所引起的數(shù)據(jù)庫操作,要不都完成,要么都不執(zhí)行;
2.2.2.Consistency: 一致性
2.2.3.Isolation: 隔離性
事務(wù)調(diào)度: 事務(wù)之間影響最小
MVCC: 多版本并發(fā)控制
2.2.4.Durability: 持久性
一旦事務(wù)成功完成,系統(tǒng)必須保證任何故障都不會引起事務(wù)表示出不一致性;
1、事務(wù)提交之前就已經(jīng)寫出數(shù)據(jù)至持久性存儲;
2、結(jié)合事務(wù)日志完成;
事務(wù)日志:順序IO
數(shù)據(jù)文件:隨機IO
2.3.事務(wù)的狀態(tài):
活動的: active
部分提交的: 最后一條語句執(zhí)行后
失敗的:
終止的:
提交的:
狀態(tài)間的轉(zhuǎn)換過程
2.4.事務(wù)并發(fā)執(zhí)行的優(yōu)勢:
1、提高吞吐量和資源利用率 2、減少等待時間
2.5.事務(wù)調(diào)度: 1、可恢復(fù)調(diào)度 2、無極聯(lián)調(diào)度
3.1. 設(shè)置自動提交開關(guān)
mysql> SELECT @@AUTOCOMMIT; #自動提交的狀態(tài)1為開啟,0為關(guān)閉;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> SET AUTOCOMMIT=0; #設(shè)定自動提交關(guān)閉
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@AUTOCOMMIT; #查詢自動提交的狀態(tài)1為開啟,0為關(guān)閉;
+--------------+
| @@AUTOCOMMIT |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
3.2. 回滾事務(wù)
mysql> SET AUTOCOMMIT=0; #設(shè)定自動提交關(guān)閉
Query OK, 0 rows affected (0.00 sec)
mysql> commit #將之前的事務(wù)全部提交
mysql> DELETE FROM student WHERE Name LIKE 'Li%'; #刪除Name字段包含Li的行
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM student; #Li哪行已經(jīng)被刪除
+-----+------------+-----+-----+
| SID | Name | Age | CID |
+-----+------------+-----+-----+
| 2 | Cheng Long | 0 | 2 |
| 3 | Yang Guo | 0 | 3 |
| 4 | Guo Jing | 0 | 4 |
+-----+------------+-----+-----+
3 rows in set (0.00 sec)
mysql> ROLLBACK; #回滾事務(wù),自動提交關(guān)閉后,默認就開啟了事務(wù),可以實現(xiàn)回滾等操作
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM student; #刪除的行恢復(fù)了
+-----+------------+-----+-----+
| SID | Name | Age | CID |
+-----+------------+-----+-----+
| 1 | Li Lianjie | 0 | 1 |
| 2 | Cheng Long | 0 | 2 |
| 3 | Yang Guo | 0 | 3 |
| 4 | Guo Jing | 0 | 4 |
+-----+------------+-----+-----+
4 rows in set (0.00 sec)
3.2. 保存點:恢復(fù)到所定義的那個保存點SAVEPOINT,保存點名稱不能為純數(shù)字。
保存點:SAVEPOINT savepoint_name; 保存以上操作為該保存點名稱
回滾保存點:ROLLBACK TO savepoint_name; 回滾到該保存點之前的狀態(tài)
3.2. 1.保存點實驗一
mysql> START TRANSACTION; #啟動事務(wù)
Query OK, 0 rows affected (0.00 sec)
mysql> SAVEPOINT a; #該保存點student表數(shù)據(jù)都存在
Query OK, 0 rows affected (0.00 sec)
mysql> select * FROM student;
+-----+------------+-----+-----+
| SID | Name | Age | CID |
+-----+------------+-----+-----+
| 1 | Li Lianjie | 0 | 1 |
| 2 | Cheng Long | 0 | 2 |
| 3 | Yang Guo | 26 | 3 |
| 4 | Guo Jing | 53 | 4 |
+-----+------------+-----+-----+
4 rows in set (0.00 sec)
mysql> DELETE FROM student WHERE SID=4; #刪除student表的SID為4的行
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM student;
+-----+------------+-----+-----+
| SID | Name | Age | CID |
+-----+------------+-----+-----+
| 1 | Li Lianjie | 0 | 1 |
| 2 | Cheng Long | 0 | 2 |
| 3 | Yang Guo | 26 | 3 |
+-----+------------+-----+-----+
3 rows in set (0.00 sec)
mysql> SAVEPOINT b; #該保存點student表數(shù)據(jù)的SID為4的行不存在了
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM student WHERE SID=3;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM student;
+-----+------------+-----+-----+
| SID | Name | Age | CID |
+-----+------------+-----+-----+
| 1 | Li Lianjie | 0 | 1 |
| 2 | Cheng Long | 0 | 2 |
+-----+------------+-----+-----+
2 rows in set (0.00 sec)
mysql> SAVEPOINT c; #該保存點student表的SID為3和4的行都不存在
Query OK, 0 rows affected (0.00 sec)
mysql> ROLLBACK TO b; #回滾至保存點b,即student表,SID為4的行不存在的行
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM student;
+-----+------------+-----+-----+
| SID | Name | Age | CID |
+-----+------------+-----+-----+
| 1 | Li Lianjie | 0 | 1 |
| 2 | Cheng Long | 0 | 2 |
| 3 | Yang Guo | 26 | 3 |
+-----+------------+-----+-----+
3 rows in set (0.00 sec)
mysql> ROLLBACK TO a; #即所有數(shù)據(jù)都存在的那個點
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM student;
+-----+------------+-----+-----+
| SID | Name | Age | CID |
+-----+------------+-----+-----+
| 1 | Li Lianjie | 0 | 1 |
| 2 | Cheng Long | 0 | 2 |
| 3 | Yang Guo | 26 | 3 |
| 4 | Guo Jing | 53 | 4 |
+-----+------------+-----+-----+
4 rows in set (0.00 sec)
3.2.2.保存點實驗二
3.2.2.1.設(shè)置a2、a4、a6、a8四個保存點
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
9 rows in set (0.00 sec)
mysql> delete from tutors where TID = 2 ;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a2 ;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from tutors where TID = 4 ;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a4;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from tutors where TID = 6 ;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a6;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from tutors where TID = 8 ;
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a8;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 3 | Miejueshitai | F | 72 |
| 5 | YiDeng | M | 90 |
| 7 | Jinlunfawang | M | 67 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
5 rows in set (0.00 sec)
3.2.2.回滾到a4保存點OK
mysql> rollback to a4;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 3 | Miejueshitai | F | 72 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
7 rows in set (0.00 sec)
3.2.3.再回滾到a6保存點失敗,該保存點已不存在
mysql> rollback to a6;
ERROR 1305 (42000): SAVEPOINT a6 does not exist
3.2.4.但再回滾到a2保存點OK
mysql> rollback to a2;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
8 rows in set (0.00 sec)
3.2.5.但再回滾到最原始保存點OK
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutors;
+-----+--------------+--------+------+
| TID | Tname | Gender | Age |
+-----+--------------+--------+------+
| 1 | HongQigong | M | 93 |
| 2 | HuangYaoshi | M | 63 |
| 3 | Miejueshitai | F | 72 |
| 4 | OuYangfeng | M | 76 |
| 5 | YiDeng | M | 90 |
| 6 | YuCanghai | M | 56 |
| 7 | Jinlunfawang | M | 67 |
| 8 | HuYidao | M | 42 |
| 9 | NingZhongze | F | 49 |
+-----+--------------+--------+------+
9 rows in set (0.00 sec)
3.2.6.回滾到最原始保存點后,但再回滾a8、a2R失敗
mysql> rollback to a8;
ERROR 1305 (42000): SAVEPOINT a8 does not exist
mysql> rollback to a2;
ERROR 1305 (42000): SAVEPOINT a2 does not exist
Mysql數(shù)據(jù)庫事務(wù)的特性及運用就先給大家講到這里,對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。
免責聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。