讀書筆記 SQL 事務理解

標籤：

事務的ACID屬性

Atomicity 原子性

每個事務作為原子單元工作（即不可以再拆分），也就是說所有資料庫變動事務，要麼成功要麼不成功。

SQL Server把每個DML或者 DDL命令都當做一個事務。不允許任何命令只是部分成功。 比如一個UPDATE語句更新500行，除非500行全部更新，否則有任何情況阻止該命令更新。SQL Server會終止該命令更新，並且復原事務。

Consistency 一致性

每個事務，不論成功或失敗，資料庫中定義的約束狀態必須一致，否則會復原。

比方說一個事務企圖插入一個無效的外鍵，這會被SQL Server見到到違反約束，並且產生一個錯誤提示。

Isolation 隔離性

事務的執行看上去是互不干擾的，隔離的程度基於隔離等級設定。比方說，兩個事務要更改同一個資料，其中之一必須等另外一個完成以後才能去修改。

SQL Server用‘鎖’來達到事務的隔離的目的。 通常有兩種鎖 Shared locks 共用鎖定 用作讀取資料  Exclusive locks 排它鎖 用作變更資料

Durability 持久性

事務操作結果都會被儲存下來（交易記錄 database transaction log）。每個資料庫變動（資料修改語句或者DDL語句）首先會把原始版本的資料（updates和deletes）寫到交易記錄，當事務提交，並且所有一致性檢查都通過以後，事件成功提交的事實就會寫入交易記錄。如果資料庫此之前意外當機，那麼再次啟動後，資料會復原。

 

阻塞 Blocking

如果兩個seesion在同樣的資源上申請排它鎖 ，當其中一個產生排它鎖以後，另外一個必須等第一個釋放後（commmit 或者 roll back）才能申請。也就是說同一時間內，兩個會話無法寫入同一個資源， 這樣，一個寫入阻塞了另外一個寫入。這就叫阻塞

除了同一資源申請排它鎖會造成堵塞之外，一個獨佔鎖定也會阻止其他事務讀取同樣的資源。因為排它鎖和共用鎖定是不相容的。

 

死結 Deadlocking

如果有兩個或多個Session互相阻塞，這就會造成死結。當SQL Server 檢測到以後，會中斷其中一個，然後返回錯誤資訊 1205.

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012; BEGIN TRAN;
UPDATE HR.Employees SET Region = N‘10004‘ WHERE empid = 1
	UPDATE Production.Suppliers SET Fax = N‘555-1212‘ WHERE supplierid = 1
UPDATE Production.Suppliers SET Fax = N‘555-1212‘ WHERE supplierid = 1
<blocked>	UPDATE HR.Employees SET phone = N‘555-9999‘ WHERE empid = 1
	<blocked>

發生死結以後其中一個事務會完成，而另外一個會被中斷，並且顯示1205錯誤資訊

Msg 1205, Level 13, State 51, Line 1
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

最後可以把成功執行交易回復

IF @@TRANCOUNT > 0 ROLLBACK

IF @@TRANCOUNT > 0 ROLLBACK

 @@TRANCOUNT

用來返回在當前串連上執行的 BEGIN TRANSACTION 語句的數目。

如果返回0則表示當前不在一個事務裡面， 1表示在一個事務裡面，大於1則表示在一個嵌套的事務裡面。

注意一個事務只能包含一個ROLLBACK 命令，他會復原整個事務，然後重設 @@TRANCOUNT 為0

實際例子如下

1. COMMIT TRAN

USE TSQL2012;SELECT @@TRANCOUNT; -- = 0BEGIN TRAN;    SELECT @@TRANCOUNT; -- = 1    BEGIN TRAN;        SELECT @@TRANCOUNT; -- = 2        -- Issue data modification or DDL commands here    COMMIT    SELECT @@TRANCOUNT; -- = 1COMMIT TRAN;SELECT @@TRANCOUNT; -- = 0

2. ROLLBACK TRAN

USE TSQL2012;SELECT @@TRANCOUNT; -- = 0BEGIN TRAN;    SELECT @@TRANCOUNT; -- = 1    BEGIN TRAN;        SELECT @@TRANCOUNT; -- = 2        -- Issue data modification or DDL command here    ROLLBACK; -- rolls back the entire transaction at this pointSELECT @@TRANCOUNT; -- = 0

 交易隔離等級

READ COMMITTED 

這個是預設隔離等級，只有資料更改被提交以後才能被讀取們所有SELECT語句會企圖取得一個共用鎖定，修改資料的另外一個事務會話的獨佔鎖定會阻塞READ COMMITTED 會話。

在查詢語句加入 WITH (NOLOCK) 或 WITH (READUNCOMMITTED)可以直接讀取

注意現在 WITH (NOLOCK) 不推薦使用，在新的SQL 版本中 Update和Delete 語句裡面不允許用這個選項了。去而代之的是 WITH (READUNCOMMITTED)

SELECT lastname, firstnameFROM HR.Employees WITH (READUNCOMMITTED);

例子： 寫入阻塞寫入

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012;
UPDATE HR.Employees SET postalcode = N‘10004‘ WHERE empid = 1;	UPDATE HR.Employees SET phone = N‘555-9999‘ WHERE empid = 1;
<more work>	<blocked>
COMMIT TRAN;
	<results returned>

寫入阻塞讀取

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012;
UPDATE HR.Employees SET postalcode = N‘10005‘ WHERE empid = 1	SELECT lastname, firstname FROM HR.Employees
	<blocked>
COMMIT TRAN;
	<results returned>

READ UNCOMMMITED

這個隔離等級允許reader讀取未提交的資料，這個設定使得SELECT 語句不用申請共用鎖定，不會被writer阻塞。然而被讀取的資料在隨後可能會復原到原來的狀態，這回導致髒讀（reading dirty data)

例子

Session 1	Session 2
USE TSQL2012; BEGIN TRAN;	USE TSQL2012; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
UPDATE HR.Employees SET region = N‘1004‘ WHERE empid = 1;
	SELECT lastname, firstname, region FROM HR.Employees
	<results returned: region = 1004 for empid = 1>
ROLLBACK TRAN;
<region for empid = 1 rolled back to original value>	SELECT lastname, firstname, region FROM HR.Employees;
	<results returned: region = original value for empid = 1>

READ COMMITTED SNAPSHOT 這個實際上不是一個新的隔離等級，這是 READ COMMITTED的一個選項，該隔離等級有以下特性：

    ●使用tempdb來儲存被修改資料的原始版本。 這樣當reader 讀取資料的時候讀取的是原始版本 ，不需要共用鎖定，也不會被writer阻塞。  即讀取 （原始）提交資料。

    ●READ COMMITTED SNAPSHOT 選項可以針對每個資料庫設定

    ●RCSI（READ COMMITTED SNAPSHOT ） 不是獨立的隔離等級，與READ COMMITTED的區別僅僅是防止writer阻塞reader。

    ●RCSI 是Windows Azure SQL Database的預設隔離等級

REPEATABLE READ   在事務結束之前，該事務中的每個讀取操作都是可重複的。所有被讀取的資料都會被共用鎖定鎖定，讀取過程中其他更新或刪除的事務無法對這些資料做更改。 不過事務讀取完成以後，可能會有新的資料行加入進來，導致幻讀（phantom read）

SNAPSHOT 該隔離等級同樣使用tempdb來空置行的版本控制，事務只能識別在其開始之前提交的資料修改。 在當前事務中執行的語句將看不到在當前事務開始以後由其他事務所做的資料修改。所以不存在幻讀（phantom read） ，事務中的語句所擷取的已提交資料快照對應於該資料在事務開始時的狀態。SNAPSHOT 隔離等級不需要共用鎖定

SERIALIZABLE  最強的隔離。在一個SELECT事務完成之前，其他事務無法向表中插入新行。

 

事務模式 Transaction Modes

Autocommit

單個資料修改或者DDL 被執行以後，如果執行成功會自動認可。

Implicit transaction

隱式聲明，先要開啟隱式聲明，然後每個語句執行以後不會自動認可或者復原，需要手動處理。例子如下：

USE TSQL2012;SET IMPLICIT_TRANSACTIONS ON;SELECT @@TRANCOUNT; -- 0SET IDENTITY_INSERT Production.Products ON;-- Issue DML or DDL command hereINSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)    VALUES(101, N‘Test2: Bad categoryid‘, 1, 1, 18.00, 0);SELECT @@TRANCOUNT; -- 1COMMIT TRAN;SET IDENTITY_INSERT Production.Products OFF;SET IMPLICIT_TRANSACTIONS OFF;-- Remove the inserted rowDELETE FROM Production.Products WHERE productid = 101; -- Note the row is deleted

Explicit transaction mode

顯式聲明，就是用BEGIN TRANSACTION 或者 BEGIN TRAN 命令開始一個事務。例子如下：

USE TSQL2012;SELECT @@TRANCOUNT; -- 0BEGIN TRAN;    SELECT @@TRANCOUNT; -- 1    SET IDENTITY_INSERT Production.Products ON;    INSERT INTO Production.Products(productid, productname, supplierid, categoryid,     unitprice, discontinued)        VALUES(101, N‘Test2: Bad categoryid‘, 1, 1, 18.00, 0);    SELECT @@TRANCOUNT; -- 1    SET IDENTITY_INSERT Production.Products OFF;COMMIT TRAN;

     

參考文檔

sys.dm_tran_database_transactions

https://msdn.microsoft.com/en-us/library/ms186957(v=sql.90).aspx

Write-Ahead Transaction Log

http://msdn.microsoft.com/en-us/library/ms186259(SQL.105).aspx

@@TRANCOUNT

https://msdn.microsoft.com/zh-cn/library/ms187967.aspx

Detecting and Ending Deadlocks

https://msdn.microsoft.com/en-us/library/ms178104(SQL.105).aspx

交易隔離等級

https://msdn.microsoft.com/zh-cn/library/dn133175.aspx

讀書筆記 SQL 事務理解