Unexpected lock in SQL Server

Unexpected lock in SQL Server

                                Yesterday I have an issue with unexpected lock in SQL Server. I have open MSDN forum post regarding this.  

My SQL Server Info,

 

First create table and insert some data as follows,

--Script 01

USE [DATABASEUSERGROUP]
GO
DROP TABLE [dbo].[tblChild];
GO
CREATE TABLE [dbo].[tblChild]([id] [int] IDENTITY(1,1) NOT NULL,[Parentid] [int] NULL,[category] [int] NULL,[Childname] [varchar](100) NULL,[cdate] [datetime] NULL,[statusof] [bit] NULL);
GO
INSERT INTO [dbo].[tblChild]([Parentid],[category],[Childname],[cdate],[statusof])
SELECT id,xtype,name,GETDATE(),0 FROM sys.syscolumns;
GO 2000
GO
CREATE CLUSTERED INDEX [CIX_tblChild_Parentid] ON [dbo].[tblChild]([Parentid] ASC);
--CREATE CLUSTERED INDEX [CIX_tblChild_category] ON [dbo].[tblChild]([category] ASC);
--CREATE CLUSTERED INDEX [CIX_tblChild_id] ON [dbo].[tblChild]([id] ASC);
GO

Next execute following Update command in one SPID(Ex: SPID=51),

--Script 02

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT session_id,Transaction_Isolation_Level FROM sys.dm_exec_sessions WHERE session_id=51;

BEGIN TRAN
SELECT  [id]
      ,[Parentid]
      ,[category]
      ,[Childname]
      ,[cdate]
      ,[statusof]
  FROM [DATABASEUSERGROUP].[dbo].[tblChild] WHERE id=1;

UPDATE [DATABASEUSERGROUP].[dbo].[tblChild] SET [Childname]='test' WHERE id=1;
--ROLLBACK TRAN
--COMMIT TRAN

Note : do not commit or rollback above Update and open another window and hit update again as follows,(Ex: SPID=54),

--Script 03

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT session_id,Transaction_Isolation_Level FROM sys.dm_exec_sessions WHERE session_id=54

BEGIN TRAN
SELECT  [id]
      ,[Parentid]
      ,[category]
      ,[Childname]
      ,[cdate]
      ,[statusof]
  FROM [DATABASEUSERGROUP].[dbo].[tblChild] WHERE id=2;

UPDATE [DATABASEUSERGROUP].[dbo].[tblChild] SET [Childname]='test1' WHERE id=2;

--ROLLBACK TRAN
--COMMIT TRAN

Hope now you will notice your second window is lock by first update.But after change Clustered Index to ID lock behavior totally different.  (Like First SQL script 01) still wondering why above lock behavior...