MySQL 事務概念與用法深入詳解
更新時間：2020年05月12日 10:36:21 作者：張德Talk
這篇文章主要介紹了MySQL 事務概念與用法,結合實例形式深入分析了MySQL 事務基本概念、原理、用法及操作注意事項,需要的朋友可以參考下
本文實例講述了MySQL 事務概念與用法。分享給大家供大家參考，具體如下：
事務的概念
MySQL事務是一個或者多個的數據庫操作，要么全部執(zhí)行成功，要么全部失敗回滾。事務是通過事務日志來實現(xiàn)的，事務日志包括：redo log和undo log。
事務的狀態(tài)
活動的（active）
事務對應的數據庫操作正在執(zhí)行過程中時，我們就說該事務處在活動的狀態(tài)。
部分提交的（partially committed）
當事務中的最后一個操作執(zhí)行完成，但由于操作都在內存中執(zhí)行，所造成的影響并沒有刷新到磁盤時，我們就說該事務處在部分提交的狀態(tài)。
失敗的（failed）
當事務處在活動的或者部分提交的狀態(tài)時，可能遇到了某些錯誤（數據庫自身的錯誤、操作系統(tǒng)錯誤或者直接斷電等）而無法繼續(xù)執(zhí)行，或者人為的停止當前事務的執(zhí)行，我們就說該事務處在失敗的狀態(tài)。
中止的（aborted）
如果事務執(zhí)行了半截而變?yōu)槭〉臓顟B(tài)，撤銷失敗事務對當前數據庫造成的影響，我們把這個撤銷的過程稱之為回滾。當回滾操作執(zhí)行完畢時，也就是數據庫恢復到了執(zhí)行事務之前的狀態(tài)，我們就說該事務處在了中止的狀態(tài)。
提交的（committed）
當一個處在部分提交的狀態(tài)的事務將修改過的數據都同步到磁盤上之后，我們就可以說該事務處在了提交的狀態(tài)。
從圖中大家也可以看出了，只有當事務處于提交的或者中止的狀態(tài)時，一個事務的生命周期才算是結束了。對于已經提交的事務來說，該事務對數據庫所做的修改將永久生效，對于處于中止狀態(tài)的事務，該事務對數據庫所做的所有修改都會被回滾到沒執(zhí)行該事務之前的狀態(tài)。
事務的作用
事務主要是為了保證復雜數據庫操作數據的一致性，尤其是在并發(fā)訪問數據時。
MySQL 事務主要用于處理操作量大，復雜度高的數據。
事務的特點
原子性（Atomicity，又稱不可分割性）
事務的數據操作，要么全部執(zhí)行成功，要么全部失敗回滾到執(zhí)行之前的狀態(tài)，就像這個事務從來沒有執(zhí)行過一樣。
隔離性（Isolation，又稱獨立性）
多個事務之間是相互隔離，互不影響的。數據庫允許多個并發(fā)事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發(fā)執(zhí)行時由于交叉執(zhí)行而導致數據的不一致。
四種隔離狀態(tài)：
1. 讀未提交（Read uncommitted）
2. 讀提交（Read committed）
3. 可重復讀（Repeatable read）
4. 串行化（Serializable）
一致性（Consistency）
在事務操作之前和之后，數據都是保持一個相同的狀態(tài)，數據庫的完整性沒有被破壞。
原子性和隔離性，對一致性有著至關重要的影響。
持久性（Durability）
當事務操作完成后，數據會被刷新到磁盤永久保存，即便是系統(tǒng)故障也不會丟失。
事務的語法
數據
#創(chuàng)建數據表：
create table account(
-> id int(10) auto_increment,
-> name varchar(30),
-> balance int(10),
-> primary key (id));
#插入數據：
insert into account(name,balance) values('老王媳婦',100),('老王',10);
mysql> select * from account; +----+--------------+---------+ | id | name | balance | +----+--------------+---------+ | 1 | 老王媳婦 | 100 | | 2 | 老王 | 10 | +----+--------------+---------+
老王媳婦有100元存在自己的微信賬戶上了，專門給老王每個月發(fā)零花錢用的，表現(xiàn)好給的多，老王也有自己的小金庫，目前已經攢到了10元零花錢了，哈哈哈。
begin
事務啟動方式1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> 事務操作SQL......
start transaction [修飾符]
修飾符： 1. read only //只讀 2. read write //讀寫 默認 3. WITH CONSISTENT SNAPSHOT //一致性讀
事務啟動方式2
mysql> start transaction read only; Query OK, 0 rows affected (0.00 sec) mysql> 事務操作SQL......
#如設置read only后，對數據進行修改會報錯： mysql> start transaction read only; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance=banlance+30 where id = 2; ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
commit
事務執(zhí)行提交，提交成功則刷新到磁盤
mysql> commit; Query OK, 0 rows affected (0.00 sec)
rollback
事務執(zhí)行回滾，回到事務操作之前的狀態(tài)。
mysql> rollback; Query OK, 0 rows affected (0.00 sec)
這里需要強調一下，ROLLBACK語句是我們程序員手動的去回滾事務時才去使用的，如果事務在執(zhí)行過程中遇到了某些錯誤而無法繼續(xù)執(zhí)行的話，事務自身會自動的回滾。
完整的提交例子
1月份，老王的表現(xiàn)很不錯，老王媳婦給他獎勵20元零花錢。
執(zhí)行步驟：
1. 從老王媳婦賬戶讀取數據
2. 從老王媳婦賬戶上減掉20元
3. 從老王賬戶讀取數據
4. 給老王賬戶增加20元
5. 執(zhí)行提交成功
6. 此時老王媳婦賬戶只有80元啦，而老王賬戶有30元啦，老王高興不得了咯
mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> update account set balance=balance-20 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update account set balance=balance+20 where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.01 sec)
#賬戶余額： mysql> select * from account; +----+--------------+---------+ | id | name | balance | +----+--------------+---------+ | 1 | 老王媳婦 | 80 | | 2 | 老王 | 30 | +----+--------------+---------+
完整的回滾例子
2月份，老王本來表現(xiàn)得很棒，堅持干家務活和遛狗，老王媳婦要給他25元的零花錢，可老王不經夸啊，老王媳婦正在給老王轉零花錢時，突然看到桌子上老王手機收到一條小女生發(fā)來的微信：親愛的王哥....，老王媳婦特別生氣，一怒之下撤回了轉賬，取消這個月的零花錢。
執(zhí)行步驟：
1. 從老王媳婦賬戶讀取數據
2. 從老王媳婦賬戶上減掉25元
3. 從老王賬戶讀取數據
4. 給老王賬戶增加25元
5. 此時老王媳婦撤回之前的操作
6. 此時，老王和老王媳婦的賬戶余額還是保持操作之前的數目
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance=balance-25 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update account set balance=balance+25 where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> rollback; Query OK, 0 rows affected (0.00 sec)
#賬戶余額： mysql> select * from account; +----+--------------+---------+ | id | name | balance | +----+--------------+---------+ | 1 | 老王媳婦 | 80 | | 2 | 老王 | 30 | +----+--------------+---------+
事務支持的存儲引擎
1. InnoDB 2. NDB
不支持的存儲引擎，比如在MyISAM上操作事務，事務不會生效，SQL語句直接自動執(zhí)行提交，所以回滾對于不支持事務的存儲引擎是無效的。
create table tb1(
-> id int(10) auto_increment,
-> name varchar(30),
-> primary key (id)
-> )engine=myisam charset=utf8mb4;
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb1(name) values('Tom');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)
mysql> rollback;//回滾無效
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)
事務的設置與查看
#查看事務開啟情況： mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+
默認是事務自動提交的，每執(zhí)行一條SQL就自動提交。此時需要操作事務，則需要顯式開啟（begin or start transaction）和提交（commit）或回滾（rollback）。
如設置成OFF，則需要執(zhí)行提交（commit）或回滾（rollback）操作時才會真正執(zhí)行事務。
關閉自動提交方式
第一種
顯式的的使用START TRANSACTION或者BEGIN語句開啟一個事務。
第二種
把系統(tǒng)變量autocommit的值設置為OFF。
SET autocommit = OFF;
隱式提交情況
當我們使用START TRANSACTION或者BEGIN語句開啟了一個事務，或者把系統(tǒng)變量autocommit的值設置為OFF時，事務就不會進行自動提交，但是如果我們輸入了某些語句之后就會悄悄的提交掉，就像我們輸入了COMMIT語句了一樣，這種因為某些特殊的語句而導致事務提交的情況稱為隱式提交
定義或修改數據庫對象的數據定義語言（Data definition language，縮寫為：DDL）
所謂的數據庫對象，指的就是數據庫、表、視圖、存儲過程等等這些東西。當我們使用CREATE、ALTER、DROP等語句去修改這些所謂的數據庫對象時，就會隱式的提交前邊語句所屬于的事務。
BEGIN; SELECT ... # 事務中的一條語句 UPDATE ... # 事務中的一條語句 ... # 事務中的其它語句 CREATE TABLE ... # 此語句會隱式的提交前邊語句所屬于的事務
隱式使用或修改mysql數據庫中的表
隱式使用或修改mysql數據庫中的表。當我們使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等語句時也會隱式的提交前邊語句所屬于的事務。
事務控制或關于鎖定的語句
事務控制或關于鎖定的語句。當我們在一個事務還沒提交或者回滾時就又使用START TRANSACTION或者BEGIN語句開啟了另一個事務時，會隱式的提交上一個事務。
BEGIN; SELECT ... # 事務中的一條語句 UPDATE ... # 事務中的一條語句 ... # 事務中的其它語句 BEGIN; # 此語句會隱式的提交前邊語句所屬于的事務
或者當前的autocommit系統(tǒng)變量的值為OFF，我們手動把它調為ON時，也會隱式的提交前邊語句所屬的事務。或者使用LOCK TABLES、UNLOCK TABLES等關于鎖定的語句也會隱式的提交前邊語句所屬的事務。
加載數據的語句
比如我們使用LOAD DATA語句來批量往數據庫中導入數據時，也會隱式的提交前邊語句所屬的事務。
關于MySQL復制的一些語句
使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等語句時也會隱式的提交前邊語句所屬的事務。
其它的一些語句
使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等語句也會隱式的提交前邊語句所屬的事務。
事務的保存點
概念
在事務對應的數據庫語句中打幾個點，我們在調用ROLLBACK語句時可以指定會滾到哪個點，而不是回到最初的原點。有了事務的保存點，我們在進行復雜的事務操作時，我們不用擔心一出錯直接回滾到最初狀態(tài)，就如一夜回到解放前。
使用語法
1. SAVEPOINT 保存點名稱;//標記保存點
2. ROLLBACK TO [SAVEPOINT] 保存點名稱;//回滾到某一個保存點
3. RELEASE SAVEPOINT 保存點名稱;//刪除
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance=balance-20 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> savepoint action1; Query OK, 0 rows affected (0.02 sec) mysql> select * from account; +----+--------------+---------+ | id | name | balance | +----+--------------+---------+ | 1 | 老王媳婦 | 60 | | 2 | 老王 | 30 | +----+--------------+---------+ mysql> update account set balance=balance+30 where id = 2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> rollback to action1;//回滾到action1保存點 Query OK, 0 rows affected (0.00 sec) mysql> select * from account; +----+--------------+---------+ | id | name | balance | +----+--------------+---------+ | 1 | 老王媳婦 | 60 | | 2 | 老王 | 30 | +----+--------------+---------+
更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL事務操作技巧匯總》、《MySQL索引操作技巧匯總》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》
希望本文所述對大家MySQL數據庫計有所幫助。
相關文章
這篇文章主要介紹了MySQL常見內存不足啟動失敗的完美解決方法,需要的朋友可以參考下2018-03-03
這篇文章主要介紹了mysql中如何查看表是否被鎖問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
這篇文章主要介紹了mysql查詢如何去掉多余零問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
mysqlsla是一款幫助語句分析、過濾、和排序的功能，能夠處理MySQL慢查詢日志、二進制日志等。整體來說, 功能非常強大. 能制作SQL查詢數據報表，分析包括執(zhí)行頻率, 數據量, 查詢消耗等2014-05-05
MYSQL啟動報1067錯誤，系統(tǒng)日志中是“服務 mysql 意外停止” Mysql日志中則是：Plugin 'FEDERATED' is disabled,下面給出解決方法2014-02-02
MySQL對JSON類型字段數據進行提取和查詢的實現(xiàn)
本文主要介紹了MySQL對JSON類型字段數據進行提取和查詢的實現(xiàn)，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-04-04
作為流行的開源數據庫管理系統(tǒng)，MySQL的使用者眾多，為了維護數據安全性，數據備份是必不可少的。本文就為大家介紹幾種適用于企業(yè)的數據備份方法,需要的朋友可以參考下2018-10-10
這篇文章主要介紹了MySQL緩存的查詢和清除命令使用詳解,對于一些不常改變數據且有大量相同sql查詢的表,查詢緩存會顯得比較有用一些,需要的朋友可以參考下2015-12-12
在實際應用中，有時候，我們需要在同一臺服務器上安裝兩個甚至多個mysql數據庫，那么，如何來操作呢，今天我們就來探討下這個問題2014-07-07
mysql報錯：1406 Data too long for colu
這篇文章給大家介紹了多種解決mysql報錯：1406, Data too long for column的解決方法,如果有遇到相同問題的朋友可以參考閱讀本文,對解決問題有一定的幫助,需要的朋友可以參考下2023-09-09