Index Locking Operation Questiong

Given a table with a clustered index and several other nonclustered indexes
which references the column defined in the clustered index, what type of
locks should I expect to be placed on all of nonclustered index? For example,
create table tblTest
(
col1 int not null,
col2 varchar(10) not null,
col3 varchar(10) not null
)
create unique clustered index (idx1_name) on tblTest (col1)
create nonclustered index (idx2_name) on tblTest(col1, col2)
create nonclustered index (idx3_name) on tblTest(col3)
Assuming the table has been populated with some data, I execute the
following statement within an explicit transaction.
begin tran
update t
set col1 = col1
from tblTest t
where col1 = 1234
end tran
(Yes, I know...as silly as this looks this is what our vendor product does
based on the profile trace we captured.)
Given this situation, I expect that SQL server would exclusive lock TAB,
PAG, RID on the tblTest object. Where PAG implicitly is locking the
(idx1_name) which is the clustered index. Also, I would expect an exclusive
lock on (idx2_name) since it needs to be "updated" however I do not expect
any lock on (idx3_name) since it would not be needed in the transaction. Is
this correct? I would really like to understand the locking mechanism in
this situation.
Thanks,
Mike MHello, Mike
In a table that has a clustered index, any non-clustered index contains
(at the leaf-level) the clustered index key, as a row locator. This is
why you see the exclusive lock for the idx3_name index. For more
informations, see "nonclustered indexes, architecture" in Books Online.
Razvan|||Ok, thank you for the info.
Mike M