Sql-server – Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction

isolation-levelsnapshot-isolationsql server

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.. 🙂

Best Answer

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.

Related Solutions

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Sql-server – How do these snapshot isolation level configurations interact on a SQL Server 2005 instance

As @AlexKuznetsov noted, SNAPSHOT and READ_COMMITTED_SNAPSHOT are two different isolation levels. As such, snapshot_isolation_state indicates the former, while is_read_committed_snapshot_on the latter. Here are a few comments that I found that summarize the differences between the two:

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.

~Bill Paetzke here

and ...

[READ_COMMITTED_SNAPSHOT] differs from the SNAPSHOT isolation level in that instead of providing a reader with the last committed version of the row that was available when the transaction started (SNAPSHOT ISOLATION), a reader gets the last committed version of the row that was available when the statement started (READ_COMMITTED_SNAPSHOT).

~Uri Dimant (emphasis mine), here

Best Answer

Related Solutions

Postgresql – Read Committed Isolation Level

Sql-server – How do these snapshot isolation level configurations interact on a SQL Server 2005 instance

Related Question