About the rowlock xlock

• xb_ou

  SSC Enthusiast

  Points: 155

  More actions

  July 26, 2008 at 9:44 pm

  #191540

  Hi everybody,

  --Session

  use Test

  go

  if object_id('XLockShareCluster') is not null

  drop table XLockShareCluster

  go

  ;with

  L0 as (select 1 as ID union all select 1),

  L1 as (select a.ID from L0 as a ,L0 as b),

  L2 as (select a.ID from L1 as a ,L1 as b),

  L3 as (select a.ID from L2 as a ,L2 as b),

  L4 as (select a.ID from L3 as a ,L3 as b),

  L5 as (select a.ID from L4 as a ,L4 as b),

  SeqID as (select row_number()over(order by ID ) as ID from L5)

  select ID,'A' as Name,3 as data into XLockShareCluster from SeqID where ID<=5000

  alter table XLockShareCluster

  alter column ID int not null

  alter table XLockShareCluster

  add constraint PK_XLockShareCluster

  primary key clustered

  (ID)on [primary]

  create index IX_Name on XLockShareCluster(Name)

  update XLockShareCluster

  set name='B'

  where ID=1

  update XLockShareCluster

  set name='C'

  where ID=2

  go

  update statistics XLockShareCluster

  --------------------------------

  --Session1

  use Test

  go

  begin tran

  select * from XLockShareCluster with(rowlock,xLock) where ID=1

  --Session2

  use Test

  go

  select * from XLockShareCluster where ID=1

  [highlight=#ffff11]Why can i get the result ?[/highlight]

  --Session3

  use Test

  go

  select * from XLockShareCluster with(holdlock) where ID=1

  It will wait until the session1 commit the transaction.

  thanks for your help