Summary:
·
Read
Committed uses locks. The concurrency is low. Transaction 1 (T1) has to wait
for T2 completes the modification to read the updates, but you get the newest
consistent data.
·
Read_Committed_Snapshot uses row-version snapshot of the data just before
modification statement in T2. T1 will read the snapshot when T2 is doing the
update. If T2 commits the modification, T1 will be able to read the updated
value. T1 also can modify the same row by T2 after T2 commits the transaction.
The drawbacks are: (1) inconsistent data comparing with read committed, (2)
maybe much more snapshots needed in temp table depending on how many
modification statements in other transactions, comparing to SNAPSHOT.
·
SNAPSHOT
transactions isolation level also does not use locks and uses snapshot as Read_Committed_Snapshot. SQL Server creates a snapshot of
the data just before the T2 wants to modify the data T1 is reading. Thus, the
number of snapshots is generally less than that for Read_Committed_Snapshot.
T1 only reads data from the SNAPSHOT while its transaction is open, even after
T2 commits the changes. T1 does not see it. Also T1 cannot modify the same rows
committed by T2. Thus, the SNAPSHOT transactions isolation level has the
largest lag on data consistency. But it keep the data consistent at the
transaction level.
·
So
the call is primarily upon the business requirement. Then, test and compare the
options in your technology environment.
1. Read_Committed_Snapshot and SNAPSHOT are turned on in different ways:
-- For READ_COMMITTED_SNAPSHOT, default is
OFF
ALTER DATABASE AdventureWorks2008R2
SET READ_COMMITTED_SNAPSHOT ON;
--for SNAPSHOT isolation
SET TRANSACTION ISOLATION LEVEL
SNAPSHOT
--But before that, you may need to make the
DB allow SNAPSHOT isolation
ALTER DATABASE AdventureWorks2012
SET ALLOW_SNAPSHOT_ISOLATION ON;
--------------
Background Information--------------------------------
· SQL Server has another option to allow/disallow to set the transaction isolation level to SNAPSHOT.
· The default for a user database is OFF. If it is OFF, you cannot set SNAPSHOIOT as the T. I. L for a transaction.
· You have to turn it ON for a database if you want to use SNAPSHOIOT as the T. I. L for transactions.
/*When ON is
specified, transactions can specify the SNAPSHOT transaction isolation level.
When a
transaction runs at the SNAPSHOT isolation level, all statements see a snapshot
of
data as it exists at the start of the transaction.
When OFF is
specified, transactions cannot specify the SNAPSHOT transaction isolation
level.
*/
ALTER DATABASE AdventureWorks2012
SET ALLOW_SNAPSHOT_ISOLATION ON;
--Let's test it.
--First, find the current value
of snapshot_isolation_state. It is supposed to be OFF
as the default.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO
/*** It is OFF initially. More
information on the value of snapshot_isolation_state
name
snapshot_isolation_state description
AdventureWorks2012
0
OFF
snapshot_isolation_state = State of snapshot-isolation
transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
0 = Snapshot
isolation state is OFF (default). Snapshot isolation is disallowed.
1 = Snapshot
isolation state ON. Snapshot isolation is allowed.
2 = Snapshot
isolation state is in transition to OFF state. All transactions have their
modifications versioned. Cannot start new transactions
using snapshot isolation. The database remains in the transition to OFF state
until all transactions that were active when ALTER DATABASE was run can be completed.
3 = Snapshot
isolation state is in transition to ON state. New transactions have their
modifications versioned.
Transactions cannot use snapshot isolation until the snapshot isolation state
becomes 1 (ON).
The database remains in the transition to ON state until all update
transactions that were active when
ALTER DATABASE was run can be completed.
*/
--Let’s first
make the T.I.L to the default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--Let's check the
current transaction isolation level
--Option 1
SELECT CASE
WHEN transaction_isolation_level = 1
THEN 'READ
UNCOMMITTED'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 1
THEN 'READ
COMMITTED SNAPSHOT'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 0 THEN 'READ
COMMITTED'
WHEN transaction_isolation_level = 3
THEN 'REPEATABLE
READ'
WHEN transaction_isolation_level = 4
THEN 'SERIALIZABLE'
WHEN transaction_isolation_level = 5
THEN 'SNAPSHOT'
ELSE NULL
END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d
WHERE session_id = @@SPID
AND d.database_id = DB_ID();
--Option 2: use DBCC USEROPTIONS - Returns the
SET options active (set) for the current connection.
DBCC USEROPTIONS
--isolation level: read committed
--Option 3
SELECT transaction_isolation_level FROM sys.dm_exec_sessions
WHERE session_id=58 --the current session ID
--make sure the snapshot_isolation_state
is still OFF
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO
/**** It is still
OFF
name
snapshot_isolation_state description
AdventureWorks2012
0
OFF
*/
--Can we set the
transaction isolation level to SNAPSHOT? Yes, you can!!!???.
--No error or
warning message!!!
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
--Moreover, it
seems you have changed it to SNAPSHOT.
DBCC USEROPTIONS
--isolation level:
snapshot!!!
--Is the snapshot_isolation_state still OFF? Yes, it is OFF.
SELECT name, snapshot_isolation_state,
snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO
/******* It is
still OFF
name
snapshot_isolation_state description
AdventureWorks2012
0
OFF
*/
--What happened?
Well, you have to test it with a transaction! Run code below
BEGIN TRAN
SELECT [BusinessEntityID],[VacationHours] FROM [HumanResources].[Employee] WHERE [BusinessEntityID]=4
/*
Msg
3952, Level 16, State 1, Line 4
Snapshot isolation
transaction failed accessing database 'AdventureWorks2012' because snapshot
isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot
isolation.
*/
2. Control
the data consistency at different level
1) READ_COMMITTED_SNAPSHOT – at the statement level.
2) SNAPSHOT – at the transaction level
3. Demonstrate
the different between READ COMMITTED, SNAPSHOT, and READ_COMMITTED_SNAPSHOT
1) Read Committed
A read committed
transaction read the data, and another transaction modifies the same data.
The update must
wait. After the read is finished, the update can start. While the update is on,
other reads will be blocked until the update is committed or rolled back.
--A 普通已提交事务
--在此示例中，一个普通read committed事务将读取数据，然后由另一事务修改此数据。执行
--完的读操作不阻塞由其他事务执行的更新操作。但是，在其他事务已经做了更新操作后，读
--操作会被阻塞住，直到更新操作事务提交为止
*******************************************************************************************/
--==========================In
Window 1 ===================================--
USE [AdventureWorks2012]
GO
--Make sure it is
Read Committed. If not, set it to be. You may need to close all of the
--other
connections, and use ALTER DATABASE AdventureWorks2012 SET READ_COMMITTED_SNAPSHOT
OFF
SELECT CASE WHEN transaction_isolation_level = 1 THEN 'READ
UNCOMMITTED'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 1
THEN 'READ
COMMITTED SNAPSHOT'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 0 THEN 'READ
COMMITTED'
WHEN transaction_isolation_level = 3 THEN 'REPEATABLE
READ'
WHEN transaction_isolation_level = 4 THEN 'SERIALIZABLE'
WHEN transaction_isolation_level = 5 THEN 'SNAPSHOT'
ELSE NULL
END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d
WHERE session_id = @@SPID
AND d.database_id = DB_ID();
BEGIN TRAN
--Query 1
--Return the initial 48 vacation hours for
this employee.
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--*********************In
Window 2 *****************************************--
USE [AdventureWorks2012]
GO
BEGIN TRAN
--Update 1
--Update will not be blocked as the S lock is
released after read is done
--Please note that the S lock in Read
Committed is at the statement level, not at the transaction level
--So although Transaction 1 is still open,
the S lock is released after the read.
UPDATE [HumanResources].[Employee]
SET [VacationHours] = [VacationHours] - 8
WHERE [BusinessEntityID] = 4
--make sure the update is effective in
transaction 2 = 40 hours
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--========================In
Window 1 ===================================--
--However, the
query in transaction 1 will be blocked as the update in T2 is not committed or rolledback.
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--*************************In
Window 2 *****************************************--
COMMIT
GO
--============================In
Window 1 ===================================--
--Now the query
returns 40 hours after the change in Window 2 is committed
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--If you update
the same table on another column, it will be OK as the update in T2 does not
affect it.
--But it will not be OK in the SNAPSHOT
transaction isolation level as demonstrated below.
BEGIN TRAN
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours] = [SickLeaveHours] - 8
WHERE [BusinessEntityID] = 4
--It should RETURN 80-8=72
SELECT [SickLeaveHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--Rollback the update in T1. The update
in T2 has been committed, and not affected.
ROLLBACK TRAN
2) SNAPSHOT ISOLATION
In Snapshot, one
transaction read the data, another transaction tries to modify the data.
SQL Server will
take a snapshot of the data in the beginning of T2, T1 will read the data
from the row-version snapshot. T2 can update the real table.
So the data being acquired is
that just before the modification. Moreover, when T1 tries
to modify the same row being updated by T2, conflicts occur, and the
modifications in T1 are rolled back
*******************************************************************************************/
--B 使用快照隔离
--此示例中，在快照隔离下运行的事务将读取数据，然后由另一个事务修改此数据。快照事务
--不阻塞由其他事务执行的更新操作，他忽略数据的修改继续从版本化的行读取数据。也就是
--说，读取到的是数据修改前的版本。但是，当快照事务尝试修改已由其他事务修改的数据时，
--他将生成错误并终止
--============================In
Window 1 ===================================--
USE [AdventureWorks2012]
GO
--First, let's put the data back to the original
value
UPDATE [HumanResources].[Employee]
SET [VacationHours] = 48
WHERE [BusinessEntityID] = 4
--Allows the SNAPSHOT isolation for the
database
ALTER DATABASE [AdventureWorks2012] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
--Now set
the TRANSACTION ISOLATION LEVEL to SNAPSHOT
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
GO
BEGIN TRAN
--make sure the initial value is 48 hours
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--*************************In
Window 2 *****************************************--
USE [AdventureWorks2012]
GO
BEGIN TRAN
--Update 1
--Update will not be blocked by T1
UPDATE [HumanResources].[Employee]
SET [VacationHours] = [VacationHours] - 8
WHERE [BusinessEntityID] = 4
--Make sure the data in the table has
decreased to 40 hours
SELECT [VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--============================In
Window 1 ===================================--
--Run the query again in T1
--It should return the original 48 hours
before the update as T1 read data from the snapshot before T2
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--******************************In
Window 2 *****************************************--
--提交事务
COMMIT TRAN
GO
--==============================In
Window 1 ===================================--
--Query it again
after T2 is committed.
--It should be still
48 as it read the data from the snapshot before transaction 2.
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--Update 2. As this row has been modified T2, any
updates on the row by T1 will be aborted and rolled back.
--Error
3960: Msg 0, Level 16, State 2, Line 1
/*
Snapshot isolation transaction aborted due to update conflict. You cannot use
snapshot isolation to access table 'HumanResources.Employee'
directly or indirectly in database 'AdventureWorks2012' to update, delete, or
insert the row that has been modified or deleted by another transaction.
Retry the transaction or change the isolation level for the update/delete
statement.
*/
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours] = [SickLeaveHours] - 8
WHERE [BusinessEntityID] = 4
--The update is automatically rolledback, still 80 hours
SELECT [SickLeaveHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--But
modification in T2 will not be rolled back. The data value in the table should
still be 40 hours.
--Roll back the tran in T1 if it is still open
ROLLBACK TRAN
GO
3) READ_COMMITTED_SNAPSHOT ON
When
READ_COMMITTED_SNAPSHOT is ON, T1 read the data. If T2 tries to modify the
data, T1 will read the data from the row-version snapshot just before the
modification statement of T2 (Not the transaction!!!), so T2
can modify the data. Different from the SNAPSHOT isolation, after T2 finishes
the update, T1 can get the updated value of the table. Additionally, T1 can
modify the same row after T2 has committed.
--C 使用行版本控制的已提交读
--在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。已提交读事务的行为与快照事务的行为
--有所不同。与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。
--与快照事务不同的是，已提交读将执行下列操作：
--（1）在其他事务提交数据更改之后，读取修改的数据
--（2）能够更新由其他事务修改的数据，而快照事务不能
***************************************************************************************/
--==============================In
Window 1 ===================================--
USE [AdventureWorks2012]
GO
--First, let's put the data back to the
original value
UPDATE [HumanResources].[Employee]
SET [VacationHours] = 48
WHERE [BusinessEntityID] = 4
--Set TRANSACTION ISOLATION LEVEL to READ
COMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
--SET READ_COMMITTED_SNAPSHOT ON
--You have to close all of the other
connections while executing it. If necessary, use sp_who
and kill
ALTER DATABASE [AdventureWorks2012] SET READ_COMMITTED_SNAPSHOT ON
GO
--Let’s double check it is
READ_COMMITTED_SNAPSHOT ON
SELECT CASE WHEN transaction_isolation_level = 1 THEN 'READ
UNCOMMITTED'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 1
THEN 'READ
COMMITTED SNAPSHOT'
WHEN transaction_isolation_level = 2
AND is_read_committed_snapshot_on = 0 THEN 'READ
COMMITTED'
WHEN transaction_isolation_level = 3 THEN 'REPEATABLE
READ'
WHEN transaction_isolation_level = 4 THEN 'SERIALIZABLE'
WHEN transaction_isolation_level = 5 THEN 'SNAPSHOT'
ELSE NULL
END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions AS s
CROSS JOIN sys.databases AS d
WHERE session_id = @@SPID
AND d.database_id = DB_ID();
--Make sure the initial value is 48 hours
BEGIN TRAN
SELECT BusinessEntityID ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE BusinessEntityID = 4
--*************************************In
Window 2 *****************************--
USE [AdventureWorks2012]
GO
BEGIN TRAN
--Update 1
--The update should not be locked by the open
tran in T1
UPDATE [HumanResources].[Employee]
SET [VacationHours] = [VacationHours] - 8
WHERE BusinessEntityID = 4
--It should be 40
SELECT [VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--============================In
Window 1 ===================================--
--Run it again in T1
--It should still be 48 as T2 has not
committed the transaction yet. T1 still get the data
--from the snapshot before T2 takes place.
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--****************************In
Window 2 *****************************************--
--提交事务
COMMIT TRAN
GO
--================================In
Window 1 ===================================--
--Now T1 should get the updated value 40 after
T2 has committed the transaction.
SELECT [BusinessEntityID] ,
[VacationHours]
FROM [HumanResources].[Employee]
WHERE [BusinessEntityID] = 4
--Also the update on the same row is allowed for
T1 after T2 has committed the change. No 3960 error.
BEGIN TRAN
UPDATE [HumanResources].[Employee]
SET [SickLeaveHours] = [SickLeaveHours] - 8
WHERE [BusinessEntityID] = 4
--We can rollback T1 for the update.
--The modification by T2 has committed and
become permanent.
ROLLBACK TRAN
GO
4. Summary
of Differences in Read Committed, SNAPSHOT, and Read_Committed_Snapshot(使用行版本控制隔离级别后的不同)
|
Transaction (T1) |
T2 |
Result |
||
|
A. Read
Committed |
B. Snapshot |
C. Read_Committed_Snapshot |
||
|
BEGIN TRAN Query1 |
48 |
|||
|
BEGIN TRAN Update1 |
OK |
|||
|
Query1 |
40 |
|||
|
Query2 |
Blocked |
48 |
||
|
COMMIT TRAN |
Q2 in
T1returns 40 |
|||
|
Query3 |
40 |
48 |
40 |
|
|
Update2 ROLLBACK TRAN |
OK |
Failed |
OK |
|
