讀書筆記 SQL 事務理解

2024-07-21 02:47:36

字體：大中小

來源：轉載

供稿：網友

讀書筆記 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

@@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>