This was one of the questions I did not answer in my previous look at Shared locks in standard transaction isolations. In that post the lowest level lock the READ COMMITTED test took was an Intent Shared (IS) lock on the single data page in the table. But I do remember reading somewhere that READ [...]
Tag Archives: transaction
Discovering the Isolation Level
Thursday, May 22, 2008
A few times I’ve wondered how to detect the ISOLATION level and have discovered a few.
DBCC USEROPTIONS
DBCC USEROPTIONS
Set Option Value ——————————————————————————- textsize [...]
Locking without a Transaction
Friday, May 9, 2008
Well, not really without a transaction. But I was wondering what locking schemes are used when a query is run outside of an explicit (BEGIN / COMMIT) transaction. What impact does changes to the isolation level have?
The simple answer is its the same when compared to the locking examples I previously identified when running under [...]
Snapshot Isolation Levels and Phantom Reads
Sunday, April 20, 2008
I’m trying to study for the next MS SQL Server 2005 exam and keep getting annoyed by practice questions that are wrong. The latest one to bother me was this statement in one of the answers:
“Both read committed snapshot isolation and snapshot isolation can produce phantom records.”
Wikipedia has a nice definition of the problem of [...]
More Snapshot dmv’s
Sunday, January 27, 2008
Following on from yesterdays post on sys.dm_tran_active_snapshot_database_transactions here is a look at four more dynamic management views that contain information about snapshot transactions.
sys.dm_tran_current_snapshot displays the transaction sequence number (XSN) for each transaction that was active when the current snapshot transaction started. sys.dm_tran_transactions_snapshot displays the all of the transactions with a XSN that were active when [...]