The default isolation level of my database is "read committed snapshot". Please see the below screenshot.
I have the table "Tickets' with the following data in my database.
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"
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.
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.
Now in connection 1, I am initiating my transaction to update the Tickets priority.
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.
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.
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.. :)