Here is the condensed version of my question: why do I get a deadlock
when multiple threads are updating different rows in the same table?

Details: I have a table with a clustered index spread across multiple
columns.  I run multiple threads, each of which accesses a separate row
in the table.  Nevertheless, I see deadlocks.

SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X      and
is
            requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U

SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X      and
is
            requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S

It is my understanding that the KEY locks are essentially row-level
locks because with clustered indexes the data are leaf nodes of the
index.  As you can see, each thread is requesting a lock held by the
other.  The locks are on the same index but different rows (the long
hex numbers are hashes related to rows, e.g. 83033c6fb2c1).

I can't understand why different threads updating distinct rows would
ever want to lock the same rows.  Granted, the rows may be adjacent,
but should that cause the acquisition of locks on rows other than the
one being updated?  I could understand a broader locking if INSERTs
were happening, but that is not the case.

Thanks for any help you can offer.

Is the index unique?  Please post the complete table DDL and UPDATE
statement.

Show quoteHide quote

Dan,

Thanks for your reply.  Index is unique. Here is the table definition:

create TABLE [Sum_Item_Revenue] (
    [tendered_business_period_dim_id] [int] NOT NULL ,
    [posted_business_period_dim_id] [int] NOT NULL ,
    [event_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___event__5B78929E] DEFAULT (0),
    [profit_center_dim_id] [int] NOT NULL ,
    [misc_period_dim_id] [int] NOT NULL ,
    [pay_type_dim_id] [int] NOT NULL ,
    [emp_dim_id] [int] NOT NULL ,
    [item_dim_id] [int] NOT NULL ,
    [total_sales_gross_amount] [decimal](18, 4) NULL ,
    [total_discount_amount] [decimal](18, 4) NULL ,
    [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___order__45FE52CB] DEFAULT (0),
    CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY  CLUSTERED
    (
        [tendered_business_period_dim_id],
        [posted_business_period_dim_id],
        [event_dim_id],
        [profit_center_dim_id],
        [misc_period_dim_id],
        [pay_type_dim_id],
        [emp_dim_id],
        [item_dim_id],
        [ordered_profit_center_dim_id]
    ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY]

It turns out it's not just a straight UPDATE but a stored procedure.  I
realize the stored procedure has the potential to do INSERTs but for
the testing I've been doing, it's all been updates, because the records
already exist.  Here is the stored proc:

create procedure InsertUpdate_Sum_Item_Revenue
     @tendered_business_period_dim_id     int   ,
     @posted_business_period_dim_id       int   ,
     @profit_center_dim_id               int   ,
     @ordered_profit_center_dim_id    int = 0,
     @misc_period_dim_id                int   ,
     @pay_type_dim_id                 int   ,
     @emp_dim_id            int   ,
     @item_dim_id                      int   ,
     @total_sales_gross_amount          decimal(18, 4),
     @total_discount_amount        decimal(18, 4)
AS
    Declare @count                       int

    SELECT  @count = count(*)
    FROM Sum_Item_Revenue
    WHERE
        tendered_business_period_dim_id =
@tendered_business_period_dim_id AND
    posted_business_period_dim_id   = @posted_business_period_dim_id   AND
    profit_center_dim_id            = @profit_center_dim_id            AND

    misc_period_dim_id              = @misc_period_dim_id              AND
    pay_type_dim_id               = @pay_type_dim_id               AND
    emp_dim_id            = @emp_dim_id           AND
        item_dim_id                    = @item_dim_id           AND
    ordered_profit_center_dim_id    = @ordered_profit_center_dim_id

    IF @count = 0

      INSERT INTO Sum_Item_Revenue
      (  tendered_business_period_dim_id ,
    posted_business_period_dim_id   ,
    profit_center_dim_id            ,
    ordered_profit_center_dim_id    ,
    misc_period_dim_id              ,
    pay_type_dim_id               ,
    emp_dim_id        ,
    item_dim_id                ,
    total_sales_gross_amount        ,
    total_discount_amount
      )
      VALUES
      (  @tendered_business_period_dim_id ,
    @posted_business_period_dim_id   ,
    @profit_center_dim_id            ,
    @ordered_profit_center_dim_id      ,
    @misc_period_dim_id              ,
    @pay_type_dim_id               ,
    @emp_dim_id          ,
    @item_dim_id                ,
    @total_sales_gross_amount        ,
    @total_discount_amount
      )


    ELSE

      UPDATE Sum_Item_Revenue  SET
    total_sales_gross_amount    = total_sales_gross_amount +
@total_sales_gross_amount    ,
    total_discount_amount        = total_discount_amount +
@total_discount_amount

     WHERE
        tendered_business_period_dim_id =
@tendered_business_period_dim_id AND
    posted_business_period_dim_id   = @posted_business_period_dim_id   AND
    profit_center_dim_id            = @profit_center_dim_id            AND

    misc_period_dim_id              = @misc_period_dim_id              AND

    pay_type_dim_id               = @pay_type_dim_id               AND
    emp_dim_id            = @emp_dim_id           AND
        item_dim_id                    = @item_dim_id           AND
    ordered_profit_center_dim_id    = @ordered_profit_center_dim_id

I've removed some non-essential columns from the table for the purposes
of this posting, to remove clutter.

I call this routine from multiple threads, where each thread passes in
a unique profit_center_dim_id.  Other values of the key are similar.
So, when I call this it is doing SELECTs and UPDATEs.  I'm assuming the
SELECT is manifested by one of my SPIDs above attempting to obtain a
shared lock.

Thanks, rand

Try this:

BEGIN TRAN
IF EXISTS(SELECT 1 FROM...WITH(UPDLOCK, HOLDLOCK) WHERE...)
BEGIN
    UPDATE...
    --error handling here
END
ELSE
BEGIN
    INSERT...
    --error handling here
END
COMMIT TRAN


WITH(UPDLOCK,HOLDLOCK) places an update range-lock on the table that is
about to be modified.  This does not affect select concurrency, because
other transactions can obtain shared locks on rows that already have an
update lock.  It only affects insert/update concurrency and not by much.  It
will eliminate the deadlock that you're encountering.

The construct below doesn't take into account the fact that another
transaction can obtain a lock on the row to be updated between the SELECT
and the UPDATE or INSERT.



Show quoteHide quote

Brian, Thanks I'll give it a try.  I should add that the lack of
transaction semantics within my stored procedure is because this
procedure is invoked from .NET code within BeginTransaction() and
Commit() using the default isolation level of Read Committed.  Slightly
bigger picture: I'm multi-threading code that has heretofore been
single-threaded.  The thing that has me baffled is why there is any
lock contention at all, given that different threads should be
accessing different rows.  Unless my assumption is incorrect and the
locks I see are really not row-level, but are table- or page-level.

First, I prefer to handle transaction processing within the stored
procedure.  This makes it a lot easier to troubleshoot deadlocks and to
change code--for example, to implement optimistic concurrency.

Second, READ COMMITTED is good for reporting; for modifications, it is a
disaster waiting to happen.  You should use REPEATABLE READ or preferably
SERIALIZABLE if the information you're reading will be used in a subsequent
modification within the same transaction.  As a rule, Rows selected that may
be updated should have an update lock applied and held until the transaction
commits; rows selected that will not be updated but whose value will be used
either directly or indirectly as values that will be inserted or updated
should have a shared lock applied and held.  This is extremely important to
keep garbage out of your database.  Any change to the source data between
the SELECT and the UPDATE/INSERT renders the results you've just read out
stale, which can introduce incorrect information into the database.  If the
update involves inserting or updating summary information, then you should
use SERIALIZABLE because an INSERT will cause the results to become stale.
READ COMMITTED doesn't prevent changes from occuring between the SELECT and
the UPDATE/INSERT, and REPEATABLE READ doesn't prevent new rows that meet
the criteria used for summarization from being inserted.


Show quoteHide quote

I see that the event_dim_id column is part of the primary key but is not
included in the where clause of the SELECT or UPDATE.  This could increase
the likelihood of your deadlocks.

Brian pointed out that you are vulnerable to changes between the SELECT and
INSERT/UPDATE.  Since you run the proc is run as part of a transaction,
below is another 'UPSERT' technique that I like to use.  I hard-coded a zero
value for event_dim_id in this example.

alter procedure InsertUpdate_Sum_Item_Revenue
     @tendered_business_period_dim_id  int   ,
     @posted_business_period_dim_id    int   ,
     @profit_center_dim_id            int   ,
     @ordered_profit_center_dim_id int = 0,
     @misc_period_dim_id                int   ,
     @pay_type_dim_id                 int   ,
     @emp_dim_id   int   ,
     @item_dim_id             int   ,
     @total_sales_gross_amount          decimal(18, 4),
     @total_discount_amount  decimal(18, 4)
AS
    SET NOCOUNT ON

    INSERT INTO Sum_Item_Revenue
    (
        tendered_business_period_dim_id,
        posted_business_period_dim_id,
        profit_center_dim_id,
        ordered_profit_center_dim_id,
        misc_period_dim_id,
        pay_type_dim_id,
        emp_dim_id,
        item_dim_id,
        total_sales_gross_amount,
        total_discount_amount
    )
    SELECT
        @tendered_business_period_dim_id,
        @posted_business_period_dim_id,
        @profit_center_dim_id,
        @ordered_profit_center_dim_id,
        @misc_period_dim_id,
        @pay_type_dim_id,
        @emp_dim_id,
        @item_dim_id,
        @total_sales_gross_amount,
        @total_discount_amount
    WHERE NOT EXISTS
    (
    SELECT *
    FROM Sum_Item_Revenue WITH (UPDLOCK, HOLDLOCK)
    WHERE
        tendered_business_period_dim_id =@tendered_business_period_dim_id
AND
        posted_business_period_dim_id   = @posted_business_period_dim_id
AND
        profit_center_dim_id            = @profit_center_dim_id
AND
        misc_period_dim_id              = @misc_period_dim_id
AND
        pay_type_dim_id               = @pay_type_dim_id               AND
        emp_dim_id   = @emp_dim_id     AND
        item_dim_id                 = @item_dim_id     AND
        ordered_profit_center_dim_id = @ordered_profit_center_dim_id AND
        event_dim_id = 0
    )

    IF @@ROWCOUNT = 0
    BEGIN
        UPDATE Sum_Item_Revenue
        SET
            total_sales_gross_amount = total_sales_gross_amount +
                @total_sales_gross_amount,
            total_discount_amount = total_discount_amount +
                @total_discount_amount
        WHERE
            tendered_business_period_dim_id
=@tendered_business_period_dim_id AND
            posted_business_period_dim_id   = @posted_business_period_dim_id
AND
            profit_center_dim_id            = @profit_center_dim_id
AND
            misc_period_dim_id              = @misc_period_dim_id
AND
            pay_type_dim_id               = @pay_type_dim_id
AND
            emp_dim_id   = @emp_dim_id     AND
            item_dim_id                 = @item_dim_id     AND
            ordered_profit_center_dim_id = @ordered_profit_center_dim_id AND
            event_dim_id = 0
    END
GO

Show quoteHide quote

Thanks Dan & Brian.  I will experiment.  Any idea why different threads
accessing different rows should even be contending for resources at
all?  Dan, event_dim_id is not significant since it is not used and
always has a default value of 0.

Look at the lock information in your original post.  It's not enough that
you're only modifying one row at a time.  Your procedure also reads rows:
that's why you get deadlocks.  Both SPIDs have exclusive locks on one row,
but before the transactions commit, they also are trying to obtain shared or
update locks on the other transaction's row.   What's strange is that the
locks represented in the original post don't match what you would get from
your procedure.  I suspect that there is another procedure involved.  You
have an update lock, but the procedure you posted doesn't have
WITH(UPDLOCK).  It is my understanding that update locks are only obtained
when an explicit locking hint is specified.

Is it possible that other statements are issued by the application.  There
are many other things that could be causing the deadlocks.  That's why I
prefer to encapsulate database updates in procedures, and whenever possible,
to handle any transaction processing within those procedures.  It makes
troubleshooting much, MUCH easier.

Show quoteHide quote

The event_dim_id column might not be significant from your perspective but
SQL Server can't make the assumption that only one row will be returned
unless you include it in your WHERE clause.  Also, the column is badly
needed to use the primary key index effectively.  Check out the details of
the SEEK operator in the query plan without and with event_dim_id:

--without event_dim_id:  scans all values with specified
tendered_business_period_dim_id
--and posted_business_period_dim_id
SEEK:([Sum_Item_Revenue].[tendered_business_period_dim_id]=[@tendered_business_period_dim_id]
AND
    [Sum_Item_Revenue].[posted_business_period_dim_id]=[@posted_business_period_dim_id]),
WHERE:((((([Sum_Item_Revenue].[profit_center_dim_id]=[@profit_center_dim_id]
AND
    [Sum_Item_Revenue].[misc_period_dim_id]=[@misc_period_dim_id]) AND
    [Sum_Item_Revenue].[pay_type_dim_id]=[@pay_type_dim_id]) AND
    [Sum_Item_Revenue].[emp_dim_id]=[@emp_dim_id]) AND
    [Sum_Item_Revenue].[item_dim_id]=[@item_dim_id]) AND
    [Sum_Item_Revenue].[ordered_profit_center_dim_id]=[@ordered_profit_center_dim_id])
ORDERED FORWARD)

--without event_dim_id: single row retrieved via seek
SEEK:([Sum_Item_Revenue].[tendered_business_period_dim_id]=[@tendered_business_period_dim_id]
AND
    [Sum_Item_Revenue].[posted_business_period_dim_id]=[@posted_business_period_dim_id]
AND
    [Sum_Item_Revenue].[event_dim_id]=0 AND
    [Sum_Item_Revenue].[profit_center_dim_id]=[@profit_center_dim_id] AND
    [Sum_Item_Revenue].[misc_period_dim_id]=[@misc_period_dim_id] AND
    [Sum_Item_Revenue].[pay_type_dim_id]=[@pay_type_dim_id] AND
    [Sum_Item_Revenue].[emp_dim_id]=[@emp_dim_id] AND
    [Sum_Item_Revenue].[item_dim_id]=[@item_dim_id] AND
    [Sum_Item_Revenue].[ordered_profit_center_dim_id]=[@ordered_profit_center_dim_id])
ORDERED FORWARD)

Not only will the inefficient plan hurt performance, it can contribute to
the likelihood of deadlocks.

Show quoteHide quote

Bingo! Adding event_dim_id to the WHERE clauses in the stored procedure
has stopped deadlocks.  Thank you very much.  It had crossed my mind at
one point to check the query plan but the light didn't stay on long
enough to register.

No retrieving dupicate rows
Service Broker - BEGIN DIALOG CONVERSATION @dialog_handle
Performance
Would like an Exists in a Case statement
Convert Informix to SQL Server