Database Reference
In-Depth Information
Next, modify the
AdventureWorks2012
database so that
READ_COMMITTED_SNAPSHOT
is turned on.
ALTER DATABASE AdventureWorks2012
SET READ_COMMITTED_SNAPSHOT ON;
Now imagine a business situation. The first connection and transaction will be pulling data from the
Production.Product
table, acquiring the color of a particular item.
BEGIN TRANSACTION;
SELECT p.Color
FROM Production.Product AS p
WHERE p.ProductID = 711;
A second connection is made with a new transaction that will be modifying the color of the same item.
BEGIN TRANSACTION ;
UPDATE Production.Product
SET Color = 'Coyote'
WHERE ProductID = 711;
SELECT p.Color
FROM Production.Product AS p
WHERE p.ProductID = 711;
Running the
SELECT
statement after updating the color, you can see that the color was updated. But if you switch
back to the first connection and rerun the original
SELECT
statement (don't run the
BEGIN TRAN
statement again),
you'll still see the color as
Blue
. Switch back to the second connection and finish the transaction.
COMMIT TRANSACTION;
Switching again to the first transaction, commit that transaction, and then rerun the original
SELECT
statement.
You'll see the new color updated for the item,
Coyote
. You can reset the isolation level on
AdventureWorks2012
before continuing.
ALTER DATABASE AdventureWorks2012
SET READ_COMMITTED_SNAPSHOT OFF;
■
if the
tempdb
is filled, data modification using row versioning will continue to succeed, but reads may fail since
the versioned row will not be available. if you enable any type of row versioning isolation within your database, you must
take extra care to maintain free space within
tempdb
.
Note
Repeatable Read
The Repeatable Read isolation level allows a
SELECT
statement to retain its (S) lock until the end of the transaction,
thereby preventing other transactions from modifying the data during that time. Database functionality may
implement a logical decision inside a transaction based on the data read by a
SELECT
statement within the
