none
How to lock a table during the whole transaction? RRS feed

  • Question

  • In my procedure, I need first to run a merge table T using table S and then truncate table S. And other session might insert data into the table S during my execution of the procedure. So I'd like to lock the table in exclusive mode for the whole transaction, but how to do that?

     

    If I only lock the table during the merge phase, if after merge completed but before truncate begin, another session inserted some data into it, then the data in S will be missing when run the procedure again.

     

    begin

      merge T using S .....

     truncate table S;

    end

     

     

    • Moved by Tom PhillipsModerator Monday, September 20, 2010 2:33 PM TSQL Question (From:SQL Server Database Engine)
    Monday, September 20, 2010 7:33 AM

Answers

  • I would add 2 other possible solutions to this problem that would work for some systems

    1. If you have control over how other sessions insert the data to table S (for example, if there is the stored procedure which is used for inserts), you can use application lock ( sp_getapplock ) and serialize access to those stored procedures/code instead of table lock. Benefit of this method vs. table lock that merge statement tablockx would not be blocked by shared locks other readers can held against S.

    2. How many records do you typically have in S? If the number is not huge, you can use OUTPUT statement with the merge, collect ID of the records from S which were processed to the temporary table and next issue the delete S statement against that list instead of truncate.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, September 20, 2010 1:57 PM

All replies

  • You can use table hint in MERGE statement

     

    create

     

    table t(id int identity(1,1), name char(2), cnt int)

    create

     

    table s(id int identity(1,1), name char(2), cnt int)

    insert

     

    into t(name, cnt) values('KK',1)

    insert

     

    into s(name, cnt) values('KK',10)

    insert

     

    into s(name, cnt) values('ZZ',20)

    begin

     

    transaction

    merge

     

    t using s with (tablockx) on (t.id = s.id)

    when

     

    matched then

     

    update set t.cnt = s.cnt

    when

     

    not matched then

     

    insert(name, cnt) values(s.name, s.cnt);

    truncate

     

    table s

    commit

     

    transaction


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    • Marked as answer by Daniel Wu Monday, September 20, 2010 8:54 AM
    • Unmarked as answer by Daniel Wu Monday, September 20, 2010 8:55 AM
    Monday, September 20, 2010 8:32 AM
  • You can also use Serializable isolation level for that transaction.
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, September 20, 2010 8:53 AM
  • Hi Zieja,

     

    Using your solution.

     

       session 1             session 2

    1: merge the table      trying to insert,but blocked

    2: merge completed      insert can go forward, and insert complete

    3: truncate

     

     

    But in step 3, it also truncate the data inserted in session 2.

     

     

    What I needed is

     

       session 1             session 2

    1: merge the table      trying to insert,but blocked

    2: merge completed       ==>I want to block it here

    3: truncate   begin      ==>I want to block it here

    3: truncate   complete   ==>insert is unblocked.

     

    will  Serializable isolation level  work?

    Monday, September 20, 2010 9:00 AM
  • Under serializable until the transaction completes the table will have range locks on the rows or the extents or the page or the entire table will be locked...during that time there cannot be a select , update , insert ,delete , drop , truncate on that table by any other session.

    you can achieve it by using Serializable as well as tablock hint. in the example below ,I tried this and did not commit the transaction .Serializable alone would not help here as per your need.

    set transaction isolation level serializable
    begin tran
    insert into blocker1 with (tablock) select * from blocker2
    --go
    truncate table blocker2
    --select * from blocker1
    --rollback
    --commit

    If I remove the tablock hint , the insert to blocker1 is possible and not on blocker2.So we need the tablock hint here.

    Try and see if it helps ..

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, September 20, 2010 9:24 AM
  • I would add 2 other possible solutions to this problem that would work for some systems

    1. If you have control over how other sessions insert the data to table S (for example, if there is the stored procedure which is used for inserts), you can use application lock ( sp_getapplock ) and serialize access to those stored procedures/code instead of table lock. Benefit of this method vs. table lock that merge statement tablockx would not be blocked by shared locks other readers can held against S.

    2. How many records do you typically have in S? If the number is not huge, you can use OUTPUT statement with the merge, collect ID of the records from S which were processed to the temporary table and next issue the delete S statement against that list instead of truncate.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, September 20, 2010 1:57 PM
  • Inside your transaction use the ISOLATION level it will maintain the loking concept in your transaction

     

    I hope according the isolation level you can restrict the other user

     

    SET TRANSACTION ISOLATION LEVEL {READ COMMITED | READ UNCOMMITED | REPEATABLE | SERILIZABLE}

    Monday, September 20, 2010 3:10 PM
  • Serializable isolation level is not the best choice here - it would basically block table T. Daniel wants to serialize access to table S only.


    Thank you!

    My blog: http://aboutsqlserver.com

    Monday, September 20, 2010 3:34 PM
  • I have no control over the other session, so solution 1 is not possible

     

    Looks like solution 2 is pretty good. The data merged is less than 1M. And even if the number is in S is huge, it still can be used, right?  If not, what's the disadvantage?

    Wednesday, September 22, 2010 7:30 AM
  • thanks, it works also.

    Wednesday, September 22, 2010 7:49 AM
  • If your using delete then there are lots of options, but truncate makes it tricky. You are probably using truncate because its a massive table that you need a quick delete from to minimize downtime. I do not believe any of the locking hints above help in the examples given as the base table is not actually getting updated so no lock gets taken out on it. I've had success by doing the following:

    BEGIN TRAN
        --Locks the table with a quick bogus update to ensure nothing gets inserted between the backup and the truncate
        UPDATE TOP(1) base_table SET something = something

        INSERT INTO backup_table
        SELECT  *
        FROM base_table

        -- nothing can get inserted here due to the bogus update to base

        TRUNCATE TABLE base_table

    COMMIT TRAN


    Friday, October 19, 2012 9:49 PM