2

I'm having a deadlock accessing the same table from two different jobs. I'm testing it on a SQL SERVER 2012 Enterprise edition.

Running SQL Profiler I get this graph:

enter image description here

You can find the SQL Server Profiler log here: http://pastebin.com/bxcwy7RX

It seems there is a problem with two stored procedures. GetCode:

SET @code = (SELECT TOP 1 CODE
             FROM CODES 
             WHERE CODE_LEVEL = @aggregationLevel AND COMMISIONING_FLAG = 255
             order by ID_CODE)

With this other statement in stored procedure ReadCodeAuto:

UPDATE CODES SET
      COMMISIONING_FLAG = @commValue
    , SOURCE = 'PLS'
    , USERNAME = ''
    , TIMESPAN = SYSDATETIME() 
WHERE (CODE = @code) AND 
       CODE_LEVEL = @aggregationLevel 

But the strange thing think is that I get this deadlock when on CODES there are around of 41900 rows.

I don't have any transaction inside both stored procedures. And on SQL Server activity monitor I see that process 5& (GetCode) has a WRITE LOG wait type, SUSPENDED task state, and UPDATE command.

Do you know how can I fix this problem?

If you need more info, please tell me.

UPDATE:

Indexes: CODES_IS_COMM_IX

CREATE NONCLUSTERED INDEX [CODES_IS_COMM_IX] ON [dbo].[CODES]
(
    [IS_TRANSMITTED] ASC,
    [COMMISIONING_FLAG] ASC
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

CODES_IS_TRANS_COMM_FLAG_IX

CREATE NONCLUSTERED INDEX [CODES_IS_TRANS_COMM_FLAG_IX] ON [dbo].[CODES]
(
    [IS_TRANSMITTED] ASC,
    [COMMISIONING_FLAG] ASC
)
INCLUDE
(
    [ID_CODE],
    [CODE_LEVEL],
    [CODE],
    [TIMESPAN],
    [USERNAME],
    [SOURCE]
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

ID_CODE_ASC_IX

CREATE NONCLUSTERED INDEX [ID_CODE_ASC_IX] ON [dbo].[CODES]
(
    [ID_CODE] ASC
)
INCLUDE
(
    [CODE_LEVEL],
    [CODE]
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

PK_CODES_1

ALTER TABLE [dbo].[CODES] ADD  CONSTRAINT [PK_CODES_1] PRIMARY KEY CLUSTERED 
(
    [CODE_LEVEL] ASC,
    [CODE] ASC
)
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

I don't change isolation level, so I think is default.

I run this sql to get it:

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

And it is ReadCommitted.

About ROWLOCK options, I have removed all hints like WITH(NOLOCK) and HINT(ROWLOCK).

And now I see this on SQL Server monitor activity:

enter image description here

I'm this is another lock that I see in one of my runs:

enter image description here

There is a LCK_M_IX.

I get three indexes with the same object id: enter image description here

All data here.

  • Some things to check whenever there are deadlocks - what indexes are involved? What are your page and row locking options for affected indexes (including CI)? What's your isolation level? – JNK Sep 16 '14 at 12:48
  • @JNK I have updated my question with more information. But, I don't understand what you mean with: What are your page and row locking options for affected indexes (including CI)?. Thanks. – VansFannel Sep 16 '14 at 13:33
  • On indexes you set "Allow Page Locks" and "Allow Row Locks" as options. If those are OFF then then entire index gets locked. – JNK Sep 16 '14 at 13:34
  • One thing I see right away is you have redundant indexes which is not gonna help... – JNK Sep 16 '14 at 13:38
  • This is the first time I work with SQL Server, creating indexes, stored procedures, and sometimes I don't know what I'm doing. Any help will be appreciated. – VansFannel Sep 16 '14 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.