SQL Server – When it comes to lock index while update

Its my first blog so want to dedicate someone and make it special in some ways.

You cann’t use it practically but it will clear some of your concepts.

I have a table with clustered index , they say update stmt locks data records but what about index records.

I am referring index records by root and intermediate pages and data records by leaf level pages.

I am doing practical through exteneded events and please provide me the theory for this practical so

that in the next blog i would be able to blog theory about this practical and ill ask some questions at end.

Script for Extended events

CREATE EVENT SESSION [Akash] ON SERVER
 
ADD EVENT sqlserver.latch_demoted(
 
    ACTION(sqlserver.client_connection_id,sqlserver.database_id,sqlserver.session_id)
 
    WHERE ([sqlserver].[session_id]=(58))),

ADD EVENT sqlserver.latch_promoted(
 
    ACTION(sqlserver.client_connection_id,sqlserver.database_id,sqlserver.session_id)

    WHERE ([sqlserver].[session_id]=(58))),
 
ADD EVENT sqlserver.latch_suspend_begin(

    ACTION(sqlserver.client_connection_id,sqlserver.database_id,sqlserver.session_id)
 
    WHERE ([sqlserver].[session_id]=(58))),
 
ADD EVENT sqlserver.latch_suspend_end(
 
    ACTION(sqlserver.client_connection_id,sqlserver.database_id,sqlserver.session_id)
 
    WHERE ([sqlserver].[session_id]=(58))),
 
ADD EVENT sqlserver.latch_suspend_warning(
 
   ACTION(sqlserver.client_connection_id,sqlserver.database_id,sqlserver.session_id)

    WHERE ([sqlserver].[session_id]=(58))),
 
ADD EVENT sqlserver.page_reference_tracker(
 
    ACTION(sqlserver.client_connection_id,sqlserver.database_id,sqlserver.session_id)
	 
    WHERE ([sqlserver].[session_id]=(58))),
 
ADD EVENT sqlserver.page_split(
	 
    ACTION(sqlserver.client_connection_id,sqlserver.database_id,sqlserver.session_id)
 
    WHERE ([sqlserver].[session_id]=(58)))
 
ADD TARGET package0.ring_buffer
 
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
 
GO

Below Image is updating my table which has one clustered index

Please notice page number and lock aquired ,page number is 346689 and lock aquired is SH

Please ignore first two lines in live data sorry 🙂

Please notice page number and lock aquired ,page number is 235050 and lock aquired is EX

DBCC page for 346689 shows it as a Index record

DBCC page for 235050 shows it as a Primary record or data record

Locking on Indexes are different as there are root and intermediate pages are also involved and it becomes even better when you have lot of page split invloved on leaf lavel ,ill update you the theory on next blog..till then keep reading 🙂

Regards

Akash Gautam

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook