Table locks while inserting into the table

  • komal145

    SSCrazy Eights

    Points: 9874

    Hi ,

    I am facing a deadlock while i am trying to insert data into a table. At the same time we have app users working on the application , which inserts data in our table.

    SO, in order to avoid deadlock , can i use table lock? if so , which table lock to use and how?

    I have insert statement within the transaction something like below. Can i use hold lock hint ? will that work? i am not familiar using this lock . Please suggest

     

    BEGIN TRY

    BEGIN TRANSACTION t_Transaction

    INSERT INTO LargeTable

    SELECT *

    FROM viewLargeView

    WITH (HOLDLOCK)

     

    COMMIT TRANSACTION t_Transaction

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION t_Transaction

    END CATCH

     

     

     

    • This topic was modified 2 days, 9 hours ago by  komal145.
  • Phil Parkin

    SSC Guru

    Points: 243988

    A table lock is different from a deadlock. Which one do you mean?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • John Mitchell-245523

    SSC Guru

    Points: 148626

    So you are truncating a large table and then inserting a large amount of data into it, and all the time other users are trying to use the table?  Do the users who are inserting data into the table know that you are clearing that data out?

    Without understanding the architecture of your application, my best suggestions are:

    1. Tune the query in the definition of viewLargeView
    2. Schedule your work for out of hours, when there are no users

    John

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    my 2 cents on this - why are you truncating a table and replacing it with the view data. - why not just reference the view?

     

    MVDBA

  • Grant Fritchey

    SSC Guru

    Points: 396209

    If you lock it, no one can access it. That will stop the deadlocks. But then, the phone calls start "WHY CAN'T I GET MY DATA".

    I'm with Mike. What is this process satisfying?

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21453

    an alternative (if you can't use the view because it points to a linked server) is to use transactional replication and update the table in small chunks, rather than a big one time hit

    but Grant hit the nail on the head... why are you doing this?

    MVDBA

  • Jeff Moden

    SSC Guru

    Points: 995608

    You can do this with no chance of deadlocks nor even blocking AND the total "offline" time will be measured in milliseconds.

    You're replacing the entire table each time.  So... create two tables.  For example, if your table was originally created as "SomeTable", drop that and create two tables... one called "SomeTableA" and another called "SomeTableB".  Populate SomeTableA as you normally would and then create a SYNONYM called "SomeTable" (which was the name of the original table" and point it at the now populated "SomeTableA".

    When you need to update the table with new data, populate "SomeTableB".  You don't need a transaction for this.  If you do it right, it can even be a minimally logged evolution.  Once you've verified that "SomeTableB" has been correctly populated, drop the "SomeTable" synonym and immediately rebuild it but pointing to the most recent table, which is "SomeTableB".  You can then truncate the now unused "SomeTableA".  Total downtime will be measured in milliseconds.

    When it comes time for the next update, simply reverse the process.  Populate the currently empty "SomeTableA", repoint the synonym as previous described, then truncate "SomeTableB".

    Wash, rinse, repeat as time wears on.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

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