Professional Web Applications Themes

Deadlocking problem - Microsoft SQL / MS SQL Server

Hi, We have an application that does updates and inserts to a database, which multiple users could be using at once. We are encountering a deadlocking problem, when 2 users are trying to do something at the same time. These are just very simple inserts/updates. They are going to the same table, but different rows. These are all running in a transaction, which may effect 1 or 2 tables. However, for one of the users, the transaction is failing due to deadlocking. I have tried using the "with (rowlock)" clause to tell the insert/update statement to only lock on the ...

Sponsored Links
  1. #1

    Default Deadlocking problem

    Hi,

    We have an application that does updates and inserts to a database, which
    multiple users could be using at once. We are encountering a deadlocking
    problem, when 2 users are trying to do something at the same time.

    These are just very simple inserts/updates. They are going to the same
    table, but different rows. These are all running in a transaction, which
    may effect 1 or 2 tables. However, for one of the users, the transaction is
    failing due to deadlocking.

    I have tried using the "with (rowlock)" clause to tell the insert/update
    statement to only lock on the rows they are effecting, and not the entire
    table, but this hasn't helped.

    Any ideas what needs to be done, so each user can perform his/her actions
    without interfering with each other?


    Sponsored Links
    Marina Guest

  2. #2

    Default Re: Deadlocking problem

    Could you please post the entire DDL for the table(s) involved - including DRI and indexes? My guess is that you don't have a PK, so it locks al of the rows in the table.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Marina" <hotmail.com> wrote in message news:phx.gbl...
    Hi,

    We have an application that does updates and inserts to a database, which
    multiple users could be using at once. We are encountering a deadlocking
    problem, when 2 users are trying to do something at the same time.

    These are just very simple inserts/updates. They are going to the same
    table, but different rows. These are all running in a transaction, which
    may effect 1 or 2 tables. However, for one of the users, the transaction is
    failing due to deadlocking.

    I have tried using the "with (rowlock)" clause to tell the insert/update
    statement to only lock on the rows they are effecting, and not the entire
    table, but this hasn't helped.

    Any ideas what needs to be done, so each user can perform his/her actions
    without interfering with each other?



    Tom Guest

  3. #3

    Default Re: Deadlocking problem

    Marina,

    Are you using store procedures. Is SQL code embedded in your app?
    Using with rowlock might not help much since SQL Server automatically
    escalades the lock level.

    Usually you get deadlocks if two connections are accesing resoruces in a
    different order. For example Conn1 INSERTs into Table1 then UPDATEs Table2
    and Conn2 INSERTs into Table2 then UPDATEs Table1.

    Maybe you can write an example that simulates the order of the SQL
    statements and how you are dealing with the transactions.

    Otherwise look into BOL for "deadlocks". A good article is "Deadlocks
    Involving Locks":
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_8v5f.asp

    Danut

    "Marina" <hotmail.com> wrote in message
    news:phx.gbl... 
    is 


    Danut Guest

  4. #4

    Default Re: Deadlocking problem

    The code generates SQL statements, and executes them directly against the
    server. I tried using 'with (rowlock)' earlier, but this did not help.
    Also, this is a SQL2000 database, and from what I understand, this is the
    way it works by default. Either way, tried that.

    The way it is accessed, is actually cyclical:

    Table1, Table2, Table2, Table1, Table2, Table2, etc

    However, shouldn't only the relevant rows be locked? Not the entire table!

    And the 2 users are working on completely different rows.

    Also, I have found that this often fails on an INSERT for Table2.

    "Danut" <com> wrote in message
    news:phx.gbl... 
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_8v5f.asp [/ref]
    which [/ref]
    deadlocking [/ref]
    which [/ref]
    transaction [/ref]
    entire [/ref]
    actions 
    >
    >[/ref]


    Marina Guest

  5. #5

    Default Re: Deadlocking problem

    Looking at your reply to Danut, I'd say that you may be getting locking because they are accessing the tables in the order you mentioned:

    Table1, Table2, Table2, Table1, Table2, Table2, etc

    Are you using explicit or implicit transactions? How often are you committing work? How many indexes are on each table?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Marina" <hotmail.com> wrote in message news:phx.gbl...
    Fine, the update statement is something like:

    UPDATE MyTable Set value=value WHERE column_id=column_id and record_id=record_id and row_num_id=rowID

    Then, all the parameter values are supplied. The 3 columns in the WHERE clause comprise the primary key.

    The problem often occurs on INSERT statements to this table as well.


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Well, that certainly puts a limit on what we can do for you. I guess you don't want to post the update statements either?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Marina" <hotmail.com> wrote in message news:phx.gbl...
    I am sorry, I am unable to post that.

    However, I assure you that all tables involved do have a PK defined.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Could you please post the entire DDL for the table(s) involved - including DRI and indexes? My guess is that you don't have a PK, so it locks al of the rows in the table.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Marina" <hotmail.com> wrote in message news:phx.gbl...
    Hi,

    We have an application that does updates and inserts to a database, which
    multiple users could be using at once. We are encountering a deadlocking
    problem, when 2 users are trying to do something at the same time.

    These are just very simple inserts/updates. They are going to the same
    table, but different rows. These are all running in a transaction, which
    may effect 1 or 2 tables. However, for one of the users, the transaction is
    failing due to deadlocking.

    I have tried using the "with (rowlock)" clause to tell the insert/update
    statement to only lock on the rows they are effecting, and not the entire
    table, but this hasn't helped.

    Any ideas what needs to be done, so each user can perform his/her actions
    without interfering with each other?



    Tom Guest

  6. #6

    Default Re: Deadlocking problem

    There is an ADO.NET transaction running, and it is getting commited once at the end.

    However, shouldn't only the pertinent rows be getting locked? It looks as if though the entire table is getting locked by one of the transactions. And why is inserting a problem?

    I would like 2 users to do work on the same table, but different rows at the same time. Shouldn't this be possible without having to resort to something complex?

    There are 4 indexes: one with all 3 components of the PK, 2 more with 2 of them, and one more on the column_id column.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Looking at your reply to Danut, I'd say that you may be getting locking because they are accessing the tables in the order you mentioned:

    Table1, Table2, Table2, Table1, Table2, Table2, etc

    Are you using explicit or implicit transactions? How often are you committing work? How many indexes are on each table?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Marina" <hotmail.com> wrote in message news:phx.gbl...
    Fine, the update statement is something like:

    UPDATE MyTable Set value=value WHERE column_id=column_id and record_id=record_id and row_num_id=rowID

    Then, all the parameter values are supplied. The 3 columns in the WHERE clause comprise the primary key.

    The problem often occurs on INSERT statements to this table as well.


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Well, that certainly puts a limit on what we can do for you. I guess you don't want to post the update statements either?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Marina" <hotmail.com> wrote in message news:phx.gbl...
    I am sorry, I am unable to post that.

    However, I assure you that all tables involved do have a PK defined.
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Could you please post the entire DDL for the table(s) involved - including DRI and indexes? My guess is that you don't have a PK, so it locks al of the rows in the table.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Marina" <hotmail.com> wrote in message news:phx.gbl...
    Hi,

    We have an application that does updates and inserts to a database, which
    multiple users could be using at once. We are encountering a deadlocking
    problem, when 2 users are trying to do something at the same time.

    These are just very simple inserts/updates. They are going to the same
    table, but different rows. These are all running in a transaction, which
    may effect 1 or 2 tables. However, for one of the users, the transaction is
    failing due to deadlocking.

    I have tried using the "with (rowlock)" clause to tell the insert/update
    statement to only lock on the rows they are effecting, and not the entire
    table, but this hasn't helped.

    Any ideas what needs to be done, so each user can perform his/her actions
    without interfering with each other?


    Marina Guest

  7. #7

    Default Re: Deadlocking problem

    Well, maybe moving all the SQL code into stored procedures might help. I
    personally prefer stored procedures then SQL embedded.

    The transactions need to be as small as possible.

    Read the articles on the MS site about what flags to set on the SQL server
    to debug the deadlocks (1204 and 1205). You will see in the SQL log all the
    info you need to identify where exactly the deadlock occurs, I mean what
    table, what operation, etc.

    SQL Server escalades the locks so if you are updating many records into a
    table then it gets escalated to a table lock.

    Another simple way to fix deadlocks is to set the transactions isolation
    leve to serializable but this will kill the scalability.

    Also in the code you use to handle the db errors you could figure out that
    there was a deadlock and restart the transaction after a small random amount
    of time. Doing so the user will now know it was a deadlock but it may slow
    down your application.

    If you need help "debugging" the SQL log for deadlocks just post them here
    and I can help you understand what exaclty happens.

    Danut

    "Marina" <hotmail.com> wrote in message
    news:phx.gbl... [/ref]
    Table2 
    >[/ref]
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_8v5f.asp [/ref]
    > which [/ref]
    > deadlocking [/ref]
    > which [/ref]
    > transaction [/ref][/ref]
    insert/update [/ref]
    > entire [/ref]
    > actions 
    > >
    > >[/ref]
    >
    >[/ref]


    Danut Guest

  8. #8

    Default Re: Deadlocking problem

    Danut, thanks for the response.

    We have already put a lot of work into this project, rewriting it into
    stored procedures is just not an option given the time frame. Additionally,
    this works with several 3rd party databases, and we do not want to mess with
    those. I am also not clear on how this will solve the deadlocking.

    The transactions are as small as possible given our requirements.

    I have done a trace on the server, and there were lock escalations detected.

    Scalability is a big issue, thus we cannot do anything to kill that. Already
    as is, with several users logged in, almost all of them will get this
    deadlocking issue. So already there is no scalability, because of this
    locking. As of now, only one person can use the application at time as far
    as updates are concerned. And this is a web app, with potentitially hundreds
    of users logged in at once.

    I will see about getting some sql logs from our DBA to see if we can find
    more clues on this.

    "Danut" <com> wrote in message
    news:%phx.gbl... 
    the 
    amount [/ref]
    the [/ref]
    the [/ref]
    table! [/ref][/ref]
    a [/ref]
    > Table2 
    > >[/ref]
    >[/ref]
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_8v5f.asp 
    > > which 
    > > deadlocking [/ref][/ref]
    same 
    > > which 
    > > transaction [/ref]
    > insert/update 
    > > entire 
    > > actions 
    > >
    > >[/ref]
    >
    >[/ref]


    Marina Guest

  9. #9

    Default Re: Deadlocking problem

    I do know what you are talking about. I was part of a time that developed a
    web app about 2 years ago. We used MS DNA + SQL Server. I was one of the
    guys who wrote some COM components, one as a wrapper for OLE DB to allow the
    ASP code access the stored procedures from the database. I also profiled
    many stored procedures, did performance tests and fixed over 50 deadlock in
    some cases by rewriting the stored procedures.

    The first version of the app developed about 43 years ago used embedded SQL
    into C++ and was a total failure (there were other issues other then this).

    I'm telling you all of this just to show that I feel your pain.

    Stored procedures improve performance. When you have several SQL statments
    embedded into C++,C#, VB code it takes time to send the code to the SQL
    server that needs to compiled and then executed.

    Also in my opinion the transactions you can set inside the SQL server stored
    procedures are easier to maintain and better than what you can do in MTS or
    any other tool.

    I guess embedded SQL is one of the prices you have to pay for using other
    databases like Oracle or IBM DB2 althought it is possible to wrote stored
    procedures for this databases so the client code works without modification
    the same way it works with MS SQL Server.

    Send the log and I'll try to help you. Also some SQL similar to what you
    have will help a lot.

    Danut

    "Marina" <hotmail.com> wrote in message
    news:phx.gbl... 
    Additionally, 
    with 
    detected. 
    Already 
    far 
    hundreds [/ref]
    server 
    > the [/ref]
    a [/ref]
    that 
    > amount [/ref]
    slow [/ref]
    here [/ref]
    > the [/ref][/ref]
    help. [/ref]
    > the [/ref]
    > table! [/ref][/ref]
    automatically [/ref][/ref]
    in 
    > > Table2 [/ref][/ref]
    "Deadlocks 
    > >[/ref]
    >[/ref]
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_8v5f.asp [/ref][/ref]
    database, [/ref]
    > same [/ref][/ref]
    transaction, 
    > > insert/update 
    > >
    > >[/ref]
    >
    >[/ref]


    Danut Guest

  10. #10

    Default Re: Deadlocking problem


    "Danut" <com> wrote in message
    news:%phx.gbl... 
    SQL 
    this).

    I wonder on which platform it was...No wonder about total failure. We feel
    consequences till now...


    AlexS Guest

  11. #11

    Default Re: Deadlocking problem

    It had nothing to do with the platform. It was bad design! The second
    version, a totally different design on the same platform performed very
    well.

    Danut

    "AlexS" <ca> wrote in message
    news:phx.gbl... 
    > SQL 
    > this).
    >
    > I wonder on which platform it was...No wonder about total failure. We feel
    > consequences till now...[/ref]


    Danut Guest

  12. #12

    Default Re: Deadlocking problem

    Marina Guest

  13. #13

    Default Re: Deadlocking problem

    Tom Guest

Similar Threads

  1. Deadlocking in cfclient cookie database
    By SilverSean in forum Coldfusion Server Administration
    Replies: 2
    Last Post: August 13th, 12:56 PM
  2. Excessive Deadlocking, can anyone help me out?
    By hih in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 30th, 02:25 PM
  3. CFMX7/SQL Server Client Variable Deadlocking
    By eblackey101 in forum Macromedia ColdFusion
    Replies: 1
    Last Post: February 17th, 03:41 PM
  4. Replies: 2
    Last Post: July 17th, 07:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139