Post reply

Resolving deadlock

  • February 7, 2005 at 8:49 am

    #92227

    I have an application that has 4 instances of an application running at 4 different sites. Each application is collecting and updating a centralized database (MSSQL2K server). I am parsing data at each remote site and updating the database by inserting a record(s) using ADODB.

    Due to network latencies, some of the updates appear to be locking the data table and preventing the other sites from doing their update, causing the deadlock to occur.

    My question is: would using a stored procedure resolve this? Also, if 2 sites try to call the stored procedure at similar times, how will the server react to the current request being processed and the one trying to access the SP?

    What is the best method of handling this problem?

    system info:

    app is written in MSVC/C++, ADODB, local database MSDE2K, server is MSSQL2K, all service packs applied.

    Please be patient, I am new at this and am trying to write clean, tight code. Your input is very important and appreciated.

  • February 8, 2005 at 11:49 am

    #541209

    I'm new to this as well.

    Do all the off-site instances of the application interact with the same central DB?

    If they do, do you need to update the central DB live?

    If so, I would think you would need to set up replication capabilities.

    Otherwise, perhaps consider nightly jobs to consilidate and add the new data to the central DB.

    Peter

  • February 8, 2005 at 12:14 pm

    #541220

    Thanks for the input Peter.

    All updates need to be live and near real time. I would consider replication techniques via MSSQL but the client is using the MSDE2K database and DTS and other replication require the full SQL 2K install.

    For the most part, everything seems to work fine except for the intermittant deadlock that occurs. I need a technique to help eliminate them. There must be a way to do this.

  • February 8, 2005 at 1:59 pm

    #541261

    Stored procedures may not solve the deadlocks 100% but you may have a considerable improvement. I think, If you have a server running with just one processor, SQL Server will handle this sequentially.

  • February 8, 2005 at 2:14 pm

    #541266

    that's what I thought it would do, but was not sure.

    Thanks!!

  • February 9, 2005 at 11:34 am

    #541434

    You might try adjusting the indexes on the tables that are being deadlocked. I have had issues with this in the past, and have had some success in solving it this way.

    If you don't know which tables are being locked MS has a good utility called PSSDIAG that can diagnose it.

  • February 14, 2005 at 4:03 am

    #542018

    Hi!

    I think you can look to that problem from 2 levels: sql or application.

    You can always implement (besides sp approach, sql level), some kind of "retrying system" at application level...

    something like: "run Insert/Update; if it fails -- just read stored procedure ERROR output -- sleep 2 secs and retry; upon 3 consequent fails, throw an error (exit graccefuly)".

    it is not an elegant solution, but it works for me (vb6 application)

    holpe it helps!


    LabDev

    labdev@iol.pt

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

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