Post reply

Database Locks

  • February 13, 2008 at 7:21 am

    #124014

    Hi All

    I am facing an issue with database updates, i got a stored procedure which updates a table, this process takes about a minute to process, by this time the table is been locked by this SP, other users cant use this to select or to insert a particular record, i am using SQL 2005, is there any ways to overcome this situation, if so how can i do it?? please let me know

    Cheers

    Suji

  • February 13, 2008 at 7:35 am

    #777818

    Yeah, in order to have selects blaze through your update statements, you need to either use with (nolock), or with (readpast). If you're running several of these updates at once though, it's best to run them with (updlock) so they won't block each other. Inserts require exclusive locks on their rows though, so they'll just have to be blocked while the update is going on if they're trying to access the same rows.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 13, 2008 at 7:35 am

    #777819

    Check the new feature of SQL Server Snapshot Isolation; if that can help.

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • February 13, 2008 at 7:42 am

    #777825

    Isolation can help, but you can also cause more problems by putting that stress in tempdb. So just be careful with it because unless you've designed for it, you could be in trouble.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • February 13, 2008 at 7:45 am

    #777831

    Suji (2/13/2008)

    Hi All

    I am facing an issue with database updates, i got a stored procedure which updates a table, this process takes about a minute to process, by this time the table is been locked by this SP, other users cant use this to select or to insert a particular record, i am using SQL 2005, is there any ways to overcome this situation, if so how can i do it?? please let me know

    Cheers

    Suji

    Hello Suji,

    This is true that SQL Server applies exclusive lock on the table when any update statement is given without a filter condition. You should be passing some input value to the stored procedure so that when the underlying update statement is executed it only locks the set of rows which are needed.

    Hope this helps.

    Thanks


    Lucky

  • February 13, 2008 at 7:59 am

    #777849

    Have you tried a ROWLOCK hint in your update?

    Tommy

    Follow @sqlscribe

  • February 13, 2008 at 8:30 am

    #777871

    Hi Cever

    Updlock is not working, i cant also use snapshot isloation since there is not enough space, is there any othere way to over come this problem??

    Thanks

    Sujith

  • February 13, 2008 at 8:31 am

    #777873

    Hi Crever

    Thanks for the reply

    Thanks

    Sujith

  • February 13, 2008 at 8:41 am

    #777881

    Can't you go with the readcommited isolation level. Did you find out what is the level of lock being held. Also try using READPAST hint in the query. so that you are never a cause of blocking.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • February 13, 2008 at 9:03 am

    #777897

    What does the update statement do?

    Can it be optimised to take less time? Can the indexes be tweaked so that it doesn't have to lock the entire table?

    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

  • February 13, 2008 at 9:29 am

    #777916

    Hi Sugesh

    Thanks mate, but my DB's are on read committed isloation level, but it still blocks, i dont know what to do??

    Cheers

    Sujith

  • February 13, 2008 at 9:33 am

    #777917

    Hi Gail

    The update statement checks some table in a remote server using linked servers and then updates the table, this process takes 1 minute, we use threads iin java applications, so there are hell a lot number of transcations going on per second ( Java application only inserte into the table)

    Thanks

    Sujith

  • February 13, 2008 at 1:26 pm

    #778058

    Can you post the code, the schema and indexes on the table and an approx row count please?

    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

  • February 13, 2008 at 1:49 pm

    #778079

    Follow Gails lead for now as it seems your SP can probably be optimised, but one thing maybe worth considering is bring the data from the remote server into a staging/holding table first and then run the SP from local data. If its the fact the server is remote that's causing most of the slowdown then this solution will overcome that.

  • February 13, 2008 at 9:55 pm

    #778191

    Absolutely. While it wouldn't speed up the entire process, it would shorten the length of the locks on the updated table.

    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 15 posts - 1 through 15 (of 23 total)

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