Thanks for your reply Ken.

What you said is fair enough. But if you can please look into the second topic from MSDN which I have mentioned. Please check the underlined lines which is confusing and contradict with the last conclusion marked in bold:

Begins a transaction.

Updates a table containing a clustered index.

Issues a SELECT statement that references the same table.

The UPDATE statement acquires these locks:

Exclusive (X) locks on the updated data rows.

Intent exclusive (IX) locks on the clustered index pages containing those rows.

An IX lock on the clustered index and another on the table.

The SELECT statement acquires these locks:

Shared (S) locks on all data rows it reads, unless the row is already protected by an X lock from the UPDATE statement.

Intent Share locks on all clustered index pages containing those rows, unless the page is already protected by an IX lock.

No lock on the clustered index or table because they are already protected by IX locks."

If the SELECT statement acquires enough locks to trigger lock escalation and the escalation succeeds, the IX lock on the table is converted to an X lock, and all the row, page, and index locks are freed. Both the updates and reads are protected by the X lock on the table

Please advice!

Thanks n Regards,

Dev