1

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

  • Is there any reason to downvote the question? – Akash KC Feb 15 '13 at 5:30
11

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.

  • Thanx for the reply....I've no problem during TRUNCATE operation....After Truncate operation made on that table, the READ UNCOMMITTED doesn't work..... But if I made Delete operation instead of TRUNCATE, dirty reads can be done.... – Akash KC Feb 14 '13 at 10:58
  • 2
    @LolCoder - DELETE doesn't take a SCH-M lock on the table. It will take X locks on rows, pages, extents or even the whole object. That doesn't conflict with the SCH-S lock needed for the read. The only thing that blocks SCH-S is SCH-M. See Lock compatibility matrix – Martin Smith Feb 14 '13 at 11:00
  • I've updated the question..Please have a glance on updated question..... – Akash KC Feb 14 '13 at 11:08
  • 1
    The update doesn't make any difference to this answer or preceding comment. Are you still unclear as to why TRUNCATE causes blocking and DELETE doesn't? – Martin Smith Feb 14 '13 at 11:13
  • 1
    Your question was about why TRUNCATE doesn't work with dirty reads not general differences between the two. Other differences apart from locking is that TRUNCATE is minimally logged so for large tables is likely to be quicker than DELETE. TRUNCATE deallocates the last page in the table and DELETE doesn't. – Martin Smith Feb 14 '13 at 11:18

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.