Post reply

nolock inside a transaction

  • August 22, 2014 at 8:04 am

    #397243

    I have a code that does something like below

    Begin transaction

    Insert into Table A

    Select * from Table B

    Commit Transaction

    Now during the time the insert takes place the tables get lock and cant be even read..

    Is there a way to be able to at least read the data in table B when the query is running

    There is no process that could read or write into Table A during the transaction . Also there could be no process that could write data into Table B during the transaction. I could only have reads for table B while transaction runs.. Also I cannot go and change the read process because and apply nowait there.. So is there a way i could run this transaction in a way that the read on table B remains unlocked

  • August 22, 2014 at 8:22 am

    #1740251

    try WITH(READPAST)

    http://www.techrepublic.com/article/using-nolock-and-readpast-table-hints-in-sql-server/

  • August 22, 2014 at 8:55 am

    #1740280

    No, you can't insert data into a table without some locking being applied. That's all part of the ACID properties of a relational database. You can look at trying out read committed snapshot isolation on your database. That will allow people to read from tables while modifications are being made, but, in the case of inserts, that may not help.

    Are you trying to read from the table while you're inserting the data that you're trying to read? If so, you may need to break apart your inserts into smaller chunks to arrive at a readable version of the data from an empty table.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • August 22, 2014 at 12:35 pm

    #1740374

    mishrakanchan86 (8/22/2014)

    So is there a way i could run this transaction in a way that the read on table B remains unlocked

    Snapshot or read committed snapshot isolation levels should work. You do need to test first though.

    Just please don't go and blindly apply the WITH (POTENTIALLYINCONSISTENDDATA) hint that is mentioned in the thread title

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply