search

zikato / blogcomments

0 stars 0 forks source link

IS Lock in RCSI Enabled Database - StraightforwardSQL #10

Open utterances-bot opened 2 years ago

utterances-bot commented 2 years ago

IS Lock in RCSI Enabled Database - StraightforwardSQL

I was investigating a mysterious Intent Shared lock in a deadlock report. Especially confusing, since the database was using an Optimistic Concurrency.

https://straightforwardsql.com/posts/is-lock-in-rcsi-enabled-database/

tonyfountain commented 2 years ago

This was a very informative article. Did you confirm the connections were using the default isolation level of READ COMMITTED? That is one of the requirements for RCSI to work, otherwise it defaults to the older concurrency model.

zikato commented 2 years ago

Hi Tony - once I enable the RCSI, it is the default isolation level for that database. I have tested it by running SELECT * FROM dbo.MainTable and it didn't take any IS locks on the table. I just skipped the XE screenshot, because it was empty.