question

sakuraime avatar image
0 Votes"
sakuraime asked ErlandSommarskog commented

Select with updlock under read snapshot isolation level

If my database is under "Is read committed snapshot"

select col1 from tablea where col2=1 with (UPDLOCK) .

this will read the version store records or put an updlock directly to the records ?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered sakuraime commented

Hi @sakuraime,

Quote from MS document.

No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server.


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar thread.




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

in this case , it will still put an uplock to the records, I just did a test. thanks

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

It will not. UPDLOCK implies REPEATABLE READ, so it will read directly from the data page and take a lock.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

so that lock is a U lock .

0 Votes 0 ·

Correct.

0 Votes 0 ·