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 way of knowing if there are more rows in the table that satisfy the query.
It also has an effect on the locking strategy employed under different isolation levels that can result in dramatically reduced concurrency on a table. Of course in the general case a table with a clustered index is preferred to a heap so this should not be a day to day issue, but its a moderately interesting situation.
To test this I created a DB with two tables, one a heap and one a b-tree, and a view that shows the locks for a connection. I also referred to the Lock Compatibility page on more than one occasion.
create database locks;
go
use locks
go
create table noindex
(
col1 int ,
col2 varchar(2)
)
go
insert into noindex
values
(1,'a');
insert into noindex
values
(2,'b');
create table indexed
(
col1 int primary key,
col2 varchar(2)
)
go
insert into
indexed
select
*
from
noindex;
go
create view
vwLocks
as
select
cast(request_mode as varchar(5)) as request_mode ,
cast(request_status as varchar(10)) as request_status ,
cast(resource_type as varchar(15)) as resource_type,
case
when resource_type = 'OBJECT' then
cast(object_name(resource_associated_entity_id) as varchar(20))
else
cast(resource_associated_entity_id as varchar(20))
end as resource_name,
cast(resource_description as varchar(15)) as resource_description
from
sys.dm_tran_locks
where
request_session_id = @@spid
go
Each test used the same basic query below, changing the table (from ‘noindex’ to ‘indexed’) and the isolation level.
set transaction isolation level read committed
begin transaction
select
*
from
noindex
where
col1 = 1
print 'after select'
select
*
from
vwLocks
insert into
noindex
values
(3,'c')
print 'after insert'
select
*
from
vwLocks
update
noindex
set
col2 = 'x'
where
col1 = 2
print 'after update'
select
*
from
vwLocks
rollback
Heap and READ COMMITTED isolation
Under READ COMMITTED isolation no locks were maintained after the select. After the insert however an Exclusive (X) lock is held against the newly inserted row identified by its Row ID (RID) 1:152:2. The first number in the RID is the File number, the next the Page number and the last the “slot” number in the page which can be though of as the row index. There are also Intent the Exclusive (IX) locks that were taken on the Table and then the Page before the row was inserted.
After the update the only additional locking is an Exclusive lock on the updated row, again identified by its RID (1:152:1).
after select request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks after insert request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 X GRANT RID 72057594038321152 1:152:2 IX GRANT PAGE 72057594038321152 1:152 IS GRANT OBJECT vwLocks IX GRANT OBJECT noindex after update request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 X GRANT RID 72057594038321152 1:152:2 IX GRANT PAGE 72057594038321152 1:152 IS GRANT OBJECT vwLocks IX GRANT OBJECT noindex X GRANT RID 72057594038321152 1:152:1
If the previous query was run and the transaction left running (remove the rollback statement) another query could still read from the table under certain circumstances. The query would need to be constructed so that it did not to access the locked rows, however all rows may need to be accessed to check if they satisfy the query. For example the next query will not block behind the open transaction.
select top 1 * from noindex where col1 = 1
The first row in the table has col1 equal to 1, and the top 1 operator tells the query engine it only needs to return one row so there is no need to scan for further rows. The next query will block as it must access all rows to test if col1 equals 1, if the rows were ordered by col 1 as soon as it found the row with col2 = 2 the query could stop (as 2 > 1 so any value >= 2 must also be > 1).
select * from noindex where col1 = 1
The point here is that there is still room for other queries to read from the table. If there were Non Clustered indexes on the table they would identify the rows in the heap using the RID and would not need to scan the heap to find candidate rows. Instead the non clustered index would be scanned and the RID values used to index directly to the row in the page. Yes there would be locking on the NC index that could block the scan, but I’ll look at that another day.
Heap and SERIALIZABLE isolation
Under a serializable transaction a table level Shared (S) lock is held after the select that prevents all updates on the table. Under serializable isolation the results of the select need to be protected from Phantom Reads and Unrepeatable Reads. As there is no index on col1 another record could be inserted or modified to have a value of 1, which would invalidate the result of the select. So the only way to support the isolation level is to prevent all changes on the table by holding the shared lock. Other connections can still read from the table, but they will block when trying to insert or update any row.
The insert statement leaves an Exclusive lock on the RID the new row is added to and the accompanying Intent Exclusive lock taken on the Page the row was added to. The Shared lock left in place by the select is promoted to a Shared Intent Exclusive (SIX) lock, this seems to act like having both a S and an IX lock on the table. Other connections may still take an Intent Shared lock on the table and then read rows that do not have Exclusive locks on them. However other connections may not take an Intent Exclusive lock on the table that would be the first step in locking down to the row (RID) level to insert or update a row. Thats may understanding, it’s covered in the Lock Modes page.
So after the select and the insert other connections may still read from the table, but any form of update is prohibited. This is the same as the read committed transaction, however running the update changes things somewhat.
The update promotes the Intent Exclusive lock on the table up to a full Exclusive lock preventing all access from other connections. Right now I cannot crack the logic of why this would happen. If only the update query is run in the transaction the the Exclusive lock is also taken.
after select request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks S GRANT OBJECT noindex after insert request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 X GRANT RID 72057594038321152 1:152:2 IX GRANT PAGE 72057594038321152 1:152 IS GRANT OBJECT vwLocks SIX GRANT OBJECT noindex after update request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 X GRANT RID 72057594038321152 1:152:2 IX GRANT PAGE 72057594038321152 1:152 IS GRANT OBJECT vwLocks X GRANT OBJECT noindex
B-Tree and READ COMMITTED isolation
As a contrast below is the locks taken when using the indexed table under read committed isolation.
At all times other connections may read from the indexed table so long as they do not attempt to read data inserted or updated by the transaction. This is because the highest lock taken on the table and page is an Intent Exclusive. Which is compatible with the Intent Shared lock a query would take on the same object before attempting to lock down to the key level.
after select request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks after insert request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IX GRANT PAGE 72057594038386688 1:154 IS GRANT OBJECT vwLocks X GRANT KEY 72057594038386688 (03000d8f0ecc) IX GRANT OBJECT indexed after update request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IX GRANT PAGE 72057594038386688 1:154 IS GRANT OBJECT vwLocks X GRANT KEY 72057594038386688 (03000d8f0ecc) IX GRANT OBJECT indexed X GRANT KEY 72057594038386688 (020068e8b274)
B-Tree and SERIALIZABLE isolation
The locks taken below under a serializable isolation level are slightly different owing to the additional isolation guarantees. However the effect on the ability of another connection to read from the table is the same.
after select request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT PAGE 72057594038386688 1:154 IS GRANT OBJECT vwLocks IS GRANT OBJECT indexed S GRANT KEY 72057594038386688 (010086470766) after insert request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IX GRANT PAGE 72057594038386688 1:154 IS GRANT OBJECT vwLocks X GRANT KEY 72057594038386688 (03000d8f0ecc) IX GRANT OBJECT indexed S GRANT KEY 72057594038386688 (010086470766) after update request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IX GRANT PAGE 72057594038386688 1:154 IS GRANT OBJECT vwLocks X GRANT KEY 72057594038386688 (03000d8f0ecc) IX GRANT OBJECT indexed X GRANT KEY 72057594038386688 (020068e8b274) S GRANT KEY 72057594038386688 (010086470766)
Conclusion
So if there is a Heap table and its modified inside a Serializable transaction you may well end up with the entire table locked for all other connections. Another reason I guess to avoid Heaps.
Post a Comment