資料列版本控制隔離等級範例
下例示範快照隔離交易與使用資料列版本控制之讀取認可交易之間的行為差異。
[!附註]
閱讀下列範例之前，請務必瞭解資料列版本控制隔離等級。請參閱＜瞭解以資料列版本控制為基礎的隔離等級＞與＜使用資料列版本控制式的隔離等級＞。
範例
A. 使用快照隔離
此範例中，在快照隔離下執行的交易會讀取接著由另一個交易修改的資料。快照交易不會封鎖另一個交易執行的更新作業，而會繼續從版本控制資料列中讀取資料，忽略資料的修改。不過，當快照交易嘗試修改意見由另一個交易修改過的資料時，快照交易會產生錯誤且結束。
在工作階段 1 上：
USE AdventureWorks2008R2;
GO
-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2008R2
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRANSACTION;
-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在工作階段 2 上：
USE AdventureWorks2008R2;
GO
-- Start a transaction.
BEGIN TRANSACTION;
-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;
-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在工作階段 1 上：
-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在工作階段 2 上：
-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO
在工作階段 1 上：
-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;
-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION
GO
B. 使用使用資料列版本控制的讀取認可
在此範例中，使用資料列版本控制的讀取認可交易與另一個交易同時執行。讀取認可交易的運作方式和快照交易不同。與快照交易類似的是，讀取認可交易即使在其他交易修改資料後還是會讀取版本控制資料列。不過，與快照交易不同的是，讀取認可交易將會：
在其他交易認可資料變更後，會讀取已修改的資料
可以更新由其他交易修改的資料，但是快照交易無法做到。
在工作階段 1 上：
USE AdventureWorks2008R2;
GO
-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2008R2
-- database.
ALTER DATABASE AdventureWorks2008R2
SET READ_COMMITTED_SNAPSHOT ON;
GO
-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION;
-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在工作階段 2 上：
USE AdventureWorks2008R2;
GO
-- Start a transaction.
BEGIN TRANSACTION;
-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;
-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在工作階段 1 上：
-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在工作階段 2 上：
-- Commit the transaction.
COMMIT TRANSACTION;
GO
在工作階段 1 上：
-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;
-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO