MySQL 事務(wù)概念與用法深入詳解
本文實例講述了MySQL 事務(wù)概念與用法。分享給大家供大家參考，具體如下：
事務(wù)的概念MySQL事務(wù)是一個或者多個的數(shù)據(jù)庫操作，要么全部執(zhí)行成功，要么全部失敗回滾。事務(wù)是通過事務(wù)日志來實現(xiàn)的，事務(wù)日志包括：redo log和undo log。
事務(wù)的狀態(tài)活動的（active）事務(wù)對應(yīng)的數(shù)據(jù)庫操作正在執(zhí)行過程中時，我們就說該事務(wù)處在活動的狀態(tài)。部分提交的（partially committed）當事務(wù)中的最后一個操作執(zhí)行完成，但由于操作都在內(nèi)存中執(zhí)行，所造成的影響并沒有刷新到磁盤時，我們就說該事務(wù)處在部分提交的狀態(tài)。失敗的（failed）當事務(wù)處在活動的或者部分提交的狀態(tài)時，可能遇到了某些錯誤（數(shù)據(jù)庫自身的錯誤、操作系統(tǒng)錯誤或者直接斷電等）而無法繼續(xù)執(zhí)行，或者人為的停止當前事務(wù)的執(zhí)行，我們就說該事務(wù)處在失敗的狀態(tài)。中止的（aborted）如果事務(wù)執(zhí)行了半截而變?yōu)槭〉臓顟B(tài)，撤銷失敗事務(wù)對當前數(shù)據(jù)庫造成的影響，我們把這個撤銷的過程稱之為回滾。當回滾操作執(zhí)行完畢時，也就是數(shù)據(jù)庫恢復(fù)到了執(zhí)行事務(wù)之前的狀態(tài)，我們就說該事務(wù)處在了中止的狀態(tài)。
提交的（committed）當一個處在部分提交的狀態(tài)的事務(wù)將修改過的數(shù)據(jù)都同步到磁盤上之后，我們就可以說該事務(wù)處在了提交的狀態(tài)。
四種隔離狀態(tài)：1. 讀未提交（Read uncommitted）2. 讀提交（Read committed）3. 可重復(fù)讀（Repeatable read）4. 串行化（Serializable）
一致性（Consistency）在事務(wù)操作之前和之后，數(shù)據(jù)都是保持一個相同的狀態(tài)，數(shù)據(jù)庫的完整性沒有被破壞。原子性和隔離性，對一致性有著至關(guān)重要的影響。持久性（Durability）當事務(wù)操作完成后，數(shù)據(jù)會被刷新到磁盤永久保存，即便是系統(tǒng)故障也不會丟失。事務(wù)的語法數(shù)據(jù)#創(chuàng)建數(shù)據(jù)表：create table account( -> id int(10) auto_increment, -> name varchar(30), -> balance int(10), -> primary key (id));#插入數(shù)據(jù)：insert into account(name,balance) values(’老王媳婦’,100),(’老王’,10);
mysql> select * from account;+----+--------------+---------+| id | name | balance |+----+--------------+---------+| 1 | 老王媳婦 | 100 || 2 | 老王 | 10 |+----+--------------+---------+老王媳婦有100元存在自己的微信賬戶上了，專門給老王每個月發(fā)零花錢用的，表現(xiàn)好給的多，老王也有自己的小金庫，目前已經(jīng)攢到了10元零花錢了，哈哈哈。begin事務(wù)啟動方式1
mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> 事務(wù)操作SQL......start transaction [修飾符]
修飾符：1. read only //只讀2. read write //讀寫 默認3. WITH CONSISTENT SNAPSHOT //一致性讀事務(wù)啟動方式2
mysql> start transaction read only;Query OK, 0 rows affected (0.00 sec)mysql> 事務(wù)操作SQL......
#如設(shè)置read only后，對數(shù)據(jù)進行修改會報錯：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事務(wù)執(zhí)行提交，提交成功則刷新到磁盤
mysql> commit;Query OK, 0 rows affected (0.00 sec)rollback事務(wù)執(zhí)行回滾，回到事務(wù)操作之前的狀態(tài)。
mysql> rollback;Query OK, 0 rows affected (0.00 sec)這里需要強調(diào)一下，ROLLBACK語句是我們程序員手動的去回滾事務(wù)時才去使用的，如果事務(wù)在執(zhí)行過程中遇到了某些錯誤而無法繼續(xù)執(zhí)行的話，事務(wù)自身會自動的回滾。完整的提交例子1月份，老王的表現(xiàn)很不錯，老王媳婦給他獎勵20元零花錢。
執(zhí)行步驟：1. 從老王媳婦賬戶讀取數(shù)據(jù)2. 從老王媳婦賬戶上減掉20元3. 從老王賬戶讀取數(shù)據(jù)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: 0mysql> update account set balance=balance+20 where id = 2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> commit;Query OK, 0 rows affected (0.01 sec)
#賬戶余額：mysql> select * from account;+----+--------------+---------+| id | name | balance |+----+--------------+---------+| 1 | 老王媳婦 | 80 || 2 | 老王 | 30 |+----+--------------+---------+完整的回滾例子2月份，老王本來表現(xiàn)得很棒，堅持干家務(wù)活和遛狗，老王媳婦要給他25元的零花錢，可老王不經(jīng)夸啊，老王媳婦正在給老王轉(zhuǎn)零花錢時，突然看到桌子上老王手機收到一條小女生發(fā)來的微信：親愛的王哥....，老王媳婦特別生氣，一怒之下撤回了轉(zhuǎn)賬，取消這個月的零花錢。
執(zhí)行步驟：1. 從老王媳婦賬戶讀取數(shù)據(jù)2. 從老王媳婦賬戶上減掉25元3. 從老王賬戶讀取數(shù)據(jù)4. 給老王賬戶增加25元5. 此時老王媳婦撤回之前的操作6. 此時，老王和老王媳婦的賬戶余額還是保持操作之前的數(shù)目
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: 0mysql> update account set balance=balance+25 where id = 2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> rollback;Query OK, 0 rows affected (0.00 sec)
#賬戶余額：mysql> select * from account;+----+--------------+---------+| id | name | balance |+----+--------------+---------+| 1 | 老王媳婦 | 80 || 2 | 老王 | 30 |+----+--------------+---------+事務(wù)支持的存儲引擎
1. InnoDB2. NDB不支持的存儲引擎，比如在MyISAM上操作事務(wù)，事務(wù)不會生效，SQL語句直接自動執(zhí)行提交，所以回滾對于不支持事務(wù)的存儲引擎是無效的。
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)事務(wù)的設(shè)置與查看
#查看事務(wù)開啟情況：mysql> SHOW VARIABLES LIKE ’autocommit’;+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+默認是事務(wù)自動提交的，每執(zhí)行一條SQL就自動提交。
此時需要操作事務(wù)，則需要顯式開啟（begin or start transaction）和提交（commit）或回滾（rollback）。
如設(shè)置成OFF，則需要執(zhí)行提交（commit）或回滾（rollback）操作時才會真正執(zhí)行事務(wù)。
關(guān)閉自動提交方式第一種顯式的的使用START TRANSACTION或者BEGIN語句開啟一個事務(wù)。第二種把系統(tǒng)變量autocommit的值設(shè)置為OFF。SET autocommit = OFF;隱式提交情況當我們使用START TRANSACTION或者BEGIN語句開啟了一個事務(wù)，或者把系統(tǒng)變量autocommit的值設(shè)置為OFF時，事務(wù)就不會進行自動提交，但是如果我們輸入了某些語句之后就會悄悄的提交掉，就像我們輸入了COMMIT語句了一樣，這種因為某些特殊的語句而導(dǎo)致事務(wù)提交的情況稱為隱式提交定義或修改數(shù)據(jù)庫對象的數(shù)據(jù)定義語言（Data definition language，縮寫為：DDL）所謂的數(shù)據(jù)庫對象，指的就是數(shù)據(jù)庫、表、視圖、存儲過程等等這些東西。當我們使用CREATE、ALTER、DROP等語句去修改這些所謂的數(shù)據(jù)庫對象時，就會隱式的提交前邊語句所屬于的事務(wù)。
BEGIN;SELECT ... # 事務(wù)中的一條語句UPDATE ... # 事務(wù)中的一條語句... # 事務(wù)中的其它語句CREATE TABLE ... # 此語句會隱式的提交前邊語句所屬于的事務(wù)隱式使用或修改mysql數(shù)據(jù)庫中的表隱式使用或修改mysql數(shù)據(jù)庫中的表。
當我們使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等語句時也會隱式的提交前邊語句所屬于的事務(wù)。
事務(wù)控制或關(guān)于鎖定的語句事務(wù)控制或關(guān)于鎖定的語句。當我們在一個事務(wù)還沒提交或者回滾時就又使用START TRANSACTION或者BEGIN語句開啟了另一個事務(wù)時，會隱式的提交上一個事務(wù)。
BEGIN;SELECT ... # 事務(wù)中的一條語句UPDATE ... # 事務(wù)中的一條語句... # 事務(wù)中的其它語句BEGIN; # 此語句會隱式的提交前邊語句所屬于的事務(wù)或者當前的autocommit系統(tǒng)變量的值為OFF，我們手動把它調(diào)為ON時，也會隱式的提交前邊語句所屬的事務(wù)。
或者使用LOCK TABLES、UNLOCK TABLES等關(guān)于鎖定的語句也會隱式的提交前邊語句所屬的事務(wù)。
加載數(shù)據(jù)的語句比如我們使用LOAD DATA語句來批量往數(shù)據(jù)庫中導(dǎo)入數(shù)據(jù)時，也會隱式的提交前邊語句所屬的事務(wù)。關(guān)于MySQL復(fù)制的一些語句使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等語句時也會隱式的提交前邊語句所屬的事務(wù)。其它的一些語句使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等語句也會隱式的提交前邊語句所屬的事務(wù)。事務(wù)的保存點概念在事務(wù)對應(yīng)的數(shù)據(jù)庫語句中打幾個點，我們在調(diào)用ROLLBACK語句時可以指定會滾到哪個點，而不是回到最初的原點。有了事務(wù)的保存點，我們在進行復(fù)雜的事務(wù)操作時，我們不用擔心一出錯直接回滾到最初狀態(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: 0mysql> 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: 0mysql> rollback to action1;//回滾到action1保存點Query OK, 0 rows affected (0.00 sec)mysql> select * from account;+----+--------------+---------+| id | name | balance |+----+--------------+---------+| 1 | 老王媳婦 | 60 || 2 | 老王 | 30 |+----+--------------+---------+
更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL事務(wù)操作技巧匯總》、《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL存儲過程技巧大全》及《MySQL數(shù)據(jù)庫鎖相關(guān)技巧匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。
相關(guān)文章：
網(wǎng)公網(wǎng)安備