原文: SQL Server 表的管理_關于事務的處理的詳解(案例代碼)
一、SQL 事務
1.1SQL 事務
●事務是在資料庫上按照一定的邏輯順序執行的任務序列,既可以由使用者手動執行,也可以由某種資料庫程式自動執行。
●事務實際上就是對資料庫的一個或者多個更改。當你在某張表上建立更新或者删除記錄的時,你就已經在使用事務了。控制事務以保證資料完整性,并對資料庫錯誤做出處理,對資料庫來說非常重要。
實踐中,通常會将很多 SQL 查詢組合在一起,并将其作為某個事務一部分來執行。
1.2事務的屬性:
事務具有以下四個标準屬性,通常用縮略詞 ACID 來表示:
- 原子性:保證任務中的所有操作都執行完畢;否則,事務會在出現錯誤時終止,并復原之前所有操作到原始狀态。
- 一緻性:如果事務成功執行,則資料庫的狀态得到了進行了正确的轉變。
- 隔離性:保證不同的事務互相獨立、透明地執行。
- 持久性:即使出現系統故障,之前成功執行的事務的結果也會持久存在。
1.3事務控制:
有四個指令用于控制事務:
- COMMIT:送出更改;
- ROLLBACK:復原更改;
- SAVEPOINT:在事務内部建立一系列可以 ROLLBACK 的還原點;
- SET TRANSACTION:命名事務;
1.4COMMIT 指令:
COMMIT 指令用于儲存事務對資料庫所做的更改。
COMMIT 指令會将自上次 COMMIT 指令或者 ROLLBACK 指令執行以來所有的事務都儲存到資料庫中。
COMMIT 指令的文法如下所示:
COMMIT;
示例:
考慮 CUSTOMERS 表,表中的記錄如下所示:
下面的示例将會删除表中 age=25 的記錄,然後将更改送出(COMMIT)到資料庫中。
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> COMMIT;
上述語句将會從表中删除兩行記錄,再執行 SELECT 語句将會得到如下結果:
1.5ROLLBACK 指令:
ROLLBACK 指令用于撤銷尚未儲存到資料庫中的事務。
ROLLBACK 指令隻能撤銷自上次 COMMIT 指令或者 ROLLBACK 指令執行以來的事務。
ROLLBACK 指令的文法如下所示:
ROLLBACK;
下面的示例将會從表中删除所有 age=25 的記錄,然後復原(ROLLBACK)對資料庫所做的更改。
SQL> DELETE FROM CUSTOMERS
WHERE AGE = 25;
SQL> ROLLBACK;
結果是删除操作并不會對資料庫産生影響。現在,執行 SELECT 語句将會得到如下結果:
1.6SAVEPOINT 指令:
SAVEPOINT 是事務中的一個狀态點,使得我們可以将事務復原至特定的點,而不是将整個事務都撤銷。
SAVEPOINT 指令的記錄如下所示:
SAVEPOINT SAVEPOINT_NAME;
該指令隻能在事務語句之間建立儲存點(SAVEPOINT)。ROLLBACK 指令可以用于撤銷一系列的事務。
復原至某一儲存點的文法如下所示:
ROLLBACK TO SAVEPOINT_NAME;
下面的示例中,你計劃從 CUSTOMERS 表中删除三條不同的記錄,并在每次删除之前建立一個儲存點(SAVEPOINT),進而使得你可以在任何任何時候復原到任意的儲存點,以恢複資料至其原始狀态。
操作序列如下所示:
SQL> SAVEPOINT SP1;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=1;
1 row deleted.
SQL> SAVEPOINT SP2;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=2;
1 row deleted.
SQL> SAVEPOINT SP3;
Savepoint created.
SQL> DELETE FROM CUSTOMERS WHERE ID=3;
1 row deleted.
現在,三次删除操作已經生效了,如果此時你改變主意決定復原至名字為 SP2 的儲存點,由于 SP2 于第一次删除操作之後建立,是以後兩次删除操作将會被撤銷。
SQL> ROLLBACK TO SP2;
Rollback complete.
注意,由于你将資料庫復原至 SP2,是以隻有第一次删除真正起效了:
SQL> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
6 rows selected.
1.7RELEASE SAVEPOINT 指令:
RELEASE SAVEPOINT 指令用于删除先前建立的儲存點。
RELEASE SAVEPOINT 的文法如下所示:
RELEASE SAVEPOINT SAVEPOINT_NAME;
儲存點一旦被釋放,你就不能夠再用 ROLLBACK 指令來撤銷該儲存點之後的事務了。
1.8SET TRANSACTION 指令:
SET TRANSACTION 指令可以用來初始化資料庫事務,指定随後的事務的各種特征。
例如,你可以将某個事務指定為隻讀或者讀寫。
SET TRANSACTION 指令的文法如下所示:
SET TRANSACTION [ READ WRITE | READ ONLY ];
補充:來源 :https://blog.csdn.net/qq_33290787/article/details/51924963
了解事務的4種隔離級别
資料庫事務的隔離級别有4種,由低到高分别為Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且,在事務的并發操作中可能會出現髒讀,不可重複讀,幻讀。下面通過事例一一闡述它們的概念與聯系。
Read uncommitted
讀未送出,顧名思義,就是一個事務可以讀取另一個未送出事務的資料。
事例:老闆要給程式員發工資,程式員的工資是3.6萬/月。但是發工資時老闆不小心按錯了數字,按成3.9萬/月,該錢已經打到程式員的戶口,但是事務還沒有送出,就在這時,程式員去檢視自己這個月的工資,發現比往常多了3千元,以為漲工資了非常高興。但是老闆及時發現了不對,馬上復原差點就送出了的事務,将數字改成3.6萬再送出。
分析:實際程式員這個月的工資還是3.6萬,但是程式員看到的是3.9萬。他看到的是老闆還沒送出事務時的資料。這就是髒讀。
那怎麼解決髒讀呢?Read committed!讀送出,能解決髒讀問題。
Read committed
讀送出,顧名思義,就是一個事務要等另一個事務送出後才能讀取資料。
事例:程式員拿着信用卡去享受生活(卡裡當然是隻有3.6萬),當他埋單時(程式員事務開啟),收費系統事先檢測到他的卡裡有3.6萬,就在這個時候!!程式員的妻子要把錢全部轉出充當家用,并送出。當收費系統準備扣款時,再檢測卡裡的金額,發現已經沒錢了(第二次檢測金額當然要等待妻子轉出金額事務送出完)。程式員就會很郁悶,明明卡裡是有錢的…
分析:這就是讀送出,若有事務對資料進行更新(UPDATE)操作時,讀操作事務要等待這個更新操作事務送出後才能讀取資料,可以解決髒讀問題。但在這個事例中,出現了一個事務範圍内兩個相同的查詢卻傳回了不同資料,這就是不可重複讀。
那怎麼解決可能的不可重複讀問題?Repeatable read !
Repeatable read
重複讀,就是在開始讀取資料(事務開啟)時,不再允許修改操作
事例:程式員拿着信用卡去享受生活(卡裡當然是隻有3.6萬),當他埋單時(事務開啟,不允許其他事務的UPDATE修改操作),收費系統事先檢測到他的卡裡有3.6萬。這個時候他的妻子不能轉出金額了。接下來收費系統就可以扣款了。
分析:重複讀可以解決不可重複讀問題。寫到這裡,應該明白的一點就是,不可重複讀對應的是修改,即UPDATE操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入INSERT操作,而不是UPDATE操作。
什麼時候會出現幻讀?
事例:程式員某一天去消費,花了2千元,然後他的妻子去檢視他今天的消費記錄(全表掃描FTS,妻子事務開啟),看到确實是花了2千元,就在這個時候,程式員花了1萬買了一部電腦,即新增INSERT了一條消費記錄,并送出。當妻子列印程式員的消費記錄清單時(妻子事務送出),發現花了1.2萬元,似乎出現了幻覺,這就是幻讀。
那怎麼解決幻讀問題?Serializable!
Serializable 序列化
Serializable 是最高的事務隔離級别,在該級别下,事務串行化順序執行,可以避免髒讀、不可重複讀與幻讀。但是這種事務隔離級别效率低下,比較耗資料庫性能,一般不使用。值得一提的是:大多數資料庫預設的事務隔離級别是Read committed,比如Sql Server , Oracle。Mysql的預設隔離級别是Repeatable read。