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

Script for Extended events

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 :)



Akash Gautam

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

Follow me on TwitterFollow me on FaceBook