2

The default isolation level of my database is "read committed snapshot". Please see the below screenshot.

read committed snapshot

I have the table "Tickets' with the following data in my database.

enter image description here

Now in connection 1, I am starting a new transaction with isolation level as "read committed". And I'm going to update the priority to 'medium' for the #TicketID "2"

enter image description here

From Connection 2, I am trying to access the details of #TicketID "2". And its still taking the value from the version store which is created by the read committed snapshot isolation, which is not expected behavior.

enter image description here

As per documentation, if a transaction using "read committed" isolation level, the row can't be accessed by any other transaction until the transaction performs a commit/Rollback.

Can anyone tell me why it is not happening in the given scenario? I am also curious to know, why a transaction level isolation is not able to override the default isolation level (here read committed snapshot) which is set at the database level?


To give more clarity on the isolation override on the transaction scope, I'm providing the below information.

I have set my database isolation to read committed.

enter image description here

Now in connection 1, I am initiating my transaction to update the Tickets priority.

enter image description here

In connection 2, I am trying to access the record. But this will not complete since the transaction isolation level is in "read committed" mode.

enter image description here

In connection 3, I am trying to access the record with isolation level as "read uncommitted". Here we can see the record as since it will override the default isolation which is set at the database level.

enter image description here

I am expecting the same behavior when I set "Snapshot Isolation" at the database level, and overide it in another transaction scope. For me, it always returns the last committed value from the version store irrespective of the isolation level which is mentioned at the transaction scope. I hope I conveyed my concerns clearly.. :)

5

There is no way to override the database-level setting of read committed snapshot (RCSI) for a whole transaction.

When RCSI is set, all read committed transactions will use row versions, delivering the last committed version of a row.

A session-level override option has been asked for. You can vote for it on the product feedback site at Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED LOCK.

You can override the setting per-statement and per-table using the READCOMMITTEDLOCK table hint.

Alternatively, you could use snapshot isolation (SI) and turn read committed snapshot isolation (RCSI) off. Each transaction would then either SET TRANSACTION ISOLATION SNAPSHOT or SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

More information in my series of articles on isolation levels.

0

READ COMMITTED is your isolation level. Then you can a knob, at the database level, with which you can decide how this isolation level is implemented. If you want the blocking behaviour, then just configure the database accordingly (set read committed snapshot off for your database).

If you want something different for this isolation level at higher granularity, then you have to qo at the query level and use the READCOMMITTEDLOCK table hint, described here: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-2017.

And, you can of course choose some different isolation level, where the database setting for read committed snapshot makes no difference.

  • Please see the updated post. I know the difference between "read committed snapshot" and "read committed". I wanted to know is there any way to override the database level snapshot isolation in the transaction scope? – Unnikrishnan R Feb 13 at 11:53
  • Is the isolation level in question "read committed" or "snapshot"? What do you say in your SET TRANSACTION ISOLATION LEVEL command? – Tibor Karaszi Feb 13 at 11:58
  • database level isolation level is "Read Committed Snapshot", but in the transaction level I set it to Read Committed". – Unnikrishnan R Feb 13 at 12:02
  • "Read committed snapshot" is not an isolation level. It is a setting that decides how the isolation level that is named "read committed" will be implemented. On means though snapshot technology, and "off" means through blocking. – Tibor Karaszi Feb 13 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.