Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unhappy Unanswered: Rapid deletes cause a deadlock

    Hello all.

    Please help me shed light on the following problem:

    I am deleting from 2 tables in a transaction. I experience a deadlock occasionally when deleting.
    Here are the tables:

    create table TABLE_1 (
    ID integer not null,
    TYPE_CD varchar(50) not null,
    STATUS_ID integer not null,
    NAME varchar(500) not null,
    COMMENT varchar(500) null,
    CREATED_DATE datetime not null,
    CREATED_BY varchar(15) not null,
    LASTMOD_DATE datetime null,
    LASTMOD_BY varchar(15) null,
    constraint PK_TABLE_1 primary key nonclustered (ID)

    create table TABLE_2 (
    TASK_ID integer not null,
    TABLE_1_ID integer not null,
    COMMENT varchar(500) null,
    constraint PK_TABLE_2 primary key nonclustered (TASK_ID, TABLE_1_ID)

    Table table_2 is an intersection table between table_1 and another lookup table (called task).
    Whenever I delete from table_1 I need to delete from table_2 to maintain integrity.
    The reason I do not have clustered indexes on the tables is b/c they start out empty (every day a housekeeping task deletes from them. Unfortunately I cannot change this...).

    In my (web/jsp) application before I delete, I select from table_1 to get data I need to remember after the delete.
    In order to avoid the "select for update problem", I use the "UPDLOCK" hint in the select like this:

    select *
    from table_1 WITH (UPDLOCK),
    table_2 WITH (UPDLOCK)
    where = table_2.table_1_id "
    and = ?
    and table_2.task_id = ?

    I then issue my deletes in the following order:

    delete from table_1
    where task_id = ?
    and table_1_id = ?

    and then,

    delete from table_1 where id = ?

    The application sets the isolation level to READ COMMITTED.
    The above 3 sql statements are executed as part of a transaction. Occasionally, when I drive the application, I get a deadlock. This happens intermittently. I have been able to reproduce the problem by attempting to delete records really fast, one after the other from the (web) front end.
    When the deadlock occurs these are the locks that are held by the running processes:

    Blocked SPID (10):

    Object Lock Type Mode Status Owner Index Resource
    app DB S GRANT Sess TABLE_2
    app.dbo.TABLE_1 TAB IX GRANT Xact TABLE_2
    app.dbo.TABLE_1 KEY U GRANT Xact PK_TABLE_2 (a400f609034c)
    app.dbo.TABLE_1 RID U GRANT Xact TABLE_2 1:157:3
    app.dbo.TABLE_1 PAG IU GRANT Xact PK_TABLE_2 1:443
    app.dbo.TABLE_1 PAG IU GRANT Xact TABLE_2 1:157
    app.dbo.TABLE_2 KEY S WAIT Xact PK_TABLE_2 (6501eda29ac1)
    app.dbo.TABLE_2 KEY S GRANT Xact PK_TABLE_2 (ef007b1066ea)
    app.dbo.TABLE_2 TAB IS GRANT Xact TABLE_2
    app.dbo.TABLE_2 PAG IS GRANT Xact PK_TABLE_2 1:252

    Blocking SPID (12):
    Object Lock Type Mode Status Owner Index Resource
    app DB S GRANT Sess TABLE_2
    app.dbo.TABLE_2 KEY X GRANT Xact PK_TABLE_2 (6501eda29ac1)
    app.dbo.TABLE_2 RID X GRANT Xact TABLE_2 1:176:3
    app.dbo.TABLE_2 TAB IX GRANT Xact TABLE_2
    app.dbo.TABLE_2 PAG IX GRANT Xact PK_TABLE_2 1:252
    app.dbo.TABLE_2 PAG IX GRANT Xact TABLE_2 1:176

    I could not figure out what the problem is. Please help me with this.
    Any help will be much appreciated.

    Thanks in advance

  2. #2
    Join Date
    Oct 2003
    That transaction isolation level is probably not sufficient. I don't think that "WITH(UPDLOCK)" is going to hold beyond the completion of that query.

    If the request is to delete from two tables in turn, you need to be sure that you have free-and-clear access to both of them before you begin. And I think you're going to have to do that on the transaction level, with an isolation greater than READ COMMITTED. Otherwise, as you're seeing, "sporadically" the computer is able to finish its work on the first table, only to discover a conflicting lock on the second.

    Obviously if the problem happens enough to be a noticeable 'problem' you may need to re-think your approach: there is obviously a lot of contention on that second table.

    As I see it, "READ COMMITTED" implies that you are reading, not that you are writing. And you're not only writing, you're deleting ... Intrusive though it may be, I think you're going to have to use higher levels.
    Last edited by sundialsvcs; 05-12-04 at 22:12.
    ChimneySweep(R): fast, automatic
    table repair at a click of the

  3. #3
    Join Date
    May 2004

    Thumbs up Isolation level

    Thank you for your response. I'll set the isolation level to REPEATABLE READ for the transaction and re-test. (Thinking about it a bit more I think your suggestion will solve my problem I'll post again to let you know my test results.

    Best regards!

Posting Permissions

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