Sql-server – Does TRUNCATE TABLE affects isolation level in Sql Server

isolation-levelsql servertransactiontruncate

As I am working on the project which has SQL SERVER as a database….I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project….

As I have to make some dirty read, I am querying it with setting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED..I've implemented it in stored proc…Here is short version of my stored proc

ALTER PROCEDURE [dbo].[proc_ReadDirtyData]
@id INT,
@mode VARCHAR(5)

AS
BEGIN
    --Read Dirty Data
    IF (@mode = 'RDD')
    BEGIN
            SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
            SELECT * FROM tbl_transaction
            WHERE id = @id;--@id foreign key in tbl_transaction.
                           --This read has been done during the transaction from C# Apps
    END
END

In my C# apps, following structure is maintained

snapshot transaction start

stage1 : tblmaster --generates id
stage2 :tblchild --inserts the data with above id as foreign key.After successfull inserting,
          --dirty read goes here applying where equal to above id for other operation

if(operationSuccessful)
{
     transactionCommit()
}
else
{
     transactionRollback();
}

snapshot transaction end

I am getting problem in stage2 after TRUNCATE operation on operated table…

I've been checking whether it makes correct dirty reads,so I used to clear the data of that specific table where dirty read has been occured…

But, the problem comes when I use TRUNCATE for clearing the table data….If I use TRUNCATE, I can't make dirty reads where as if I use DELETE, it perfectly works for dirty read….

Here, I want to make clear that I'm not making dirty read during TRUNCATE/DELETE operation…After TRUNCATE/DELETE operation only, I go for dirty read of fresh data……

Now, I'm curious that whether TRUNCATE affects internal isolation of that table or not….

Best Answer

TRUNCATE table will take a SCH-M lock on the table.

Even at READ UNCOMMITTED level the SELECT query will need to take a SCH-S lock. This won't be possible until the TRUNCATE transaction has ended and the conflicting SCH-M lock has been released.

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.

Transaction Isolation Level Snapshot vs. TRUNCATE in SQL Server

The list of 'DDL' operations listed is not comprehensive (and TRUNCATE TABLE is not the only omission from that list). Whether TRUNCATE TABLE is DML or DDL is a fraught question in SQL Server, with persuasive examples on both sides of the debate, and entries both ways in Books Online.

From the point of view of a snapshot isolation transaction, truncate has the essential quality of taking a Sch-M lock, which explains the blocking ( because RCSI and SI still acquire Sch-S locks); and it also bumps the internal metadata version (for internal reasons*) resulting in error 3961.

So, the behaviour you are seeing is expected, just not documented very well.

* _{The current implementation of TRUNCATE TABLE does not generate row versions. Bumping the metadata version is the simplest way to ensure correct behaviour.}

Best Answer

Related Solutions

Postgresql – Read Committed Isolation Level

Transaction Isolation Level Snapshot vs. TRUNCATE in SQL Server

Related Question