Tables without a clustered index store their data in a Heap data structure. The (in row) data is added to the heap as its encountered without any ordering, rather than ordering by the columns in a clustered index. This makes finding particular rows a little tricky and a lot slower as there is normally no [...]
Monthly Archives: May 2008
How long do READ COMMITTED transactions hold shared locks
Friday, May 23, 2008
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 [...]
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 [...]
Shared locks in standard transaction isolations
Tuesday, May 6, 2008
While there are many types of locks in SQL Server 2005 issues generally revolve around Shared (read), Exclusive (write) and Update (read now to write later) locks on resources. And given that the majority of operations on a database are read operations, the taking and holding of shared locks is often the centre of attention.
An [...]