Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is there any reason to downvote the question? –  LolCoder Feb 15 '13 at 5:30
add comment

1 Answer

up vote 6 down vote accepted

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.

share|improve this answer
    
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.... –  LolCoder Feb 14 '13 at 10:58
1  
@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..... –  LolCoder 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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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