December 13, 2007 at 2:29 pm
Hello,
I'm getting a deadlock in an update query. The query is pretty simple, but its getting deadlocked with another thread executing the same query.
this is the query:
UPDATE INFO_LOG
SET MSG_TYPE = @p_resp_msg_type,
AUTHORIZATION_NRO = @authorization_nro,
RESPONSE_CODE = @response_code, CUENTA = @cuenta
WHERE
MSG_TYPE = @p_req_msg_type
AND PCODE = @p_pcode AND TRANS_DATE = @trans_date
AND TRANS_TIME = @trans_time
AND PROCESS_DATE = @process_date
AND ID_TERMINAL = @id_terminal
AND REFERENCE_NUMBER = @reference_number
DTD:
CREATE TABLE [dbo].[INFO_LOG](
[MSG_TYPE] [smallint] NOT NULL,
[PCODE] [int] NOT NULL,
[TRANS_DATE] [datetime] NOT NULL,
[TRANS_TIME] [int] NOT NULL,
[TRACE] [int] NOT NULL,
[LOCAL_DATE] [datetime] NOT NULL,
[LOCAL_TIME] [int] NOT NULL,
[PROCESS_DATE] [datetime] NOT NULL,
[ACQUIRER] [varchar](10),
[REFERENCE_NUMBER] [varchar](12),
[AUTHORIZATION_NRO] [varchar](6) ,
[RESPONSE_CODE] [tinyint],
[ID_TERMINAL] [varchar](16),
[ISSUER] [varchar](10),
[AMOUNT] [numeric](12, 2),
[ADQUIRIENTE] [varchar](4),
[NRO_TELEFONICO] [varchar](15),
[NRO_FACTURA] [varchar](18),
[IND_TERMINAL] [varchar](2),
[FIELD43] [varchar](40),
CONSTRAINT [PK_INFO_LOG] PRIMARY KEY CLUSTERED
(
[MSG_TYPE] ASC,
[PCODE] ASC,
[TRANS_DATE] ASC,
[TRANS_TIME] ASC,
[PROCESS_DATE] ASC,
[ID_TERMINAL] ASC,
[REFERENCE_NUMBER] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Finally, there are no triggers or other indexes on the table.
i've tried taking a look at the sql debug output, but I can't make any sense of it:
resource-list
keylock hobtid=72057594038779904 dbid=13 objectname=Procesa.dbo.INFO_LOG indexname=PK_INFO_LOG id=lock938b080 mode=U associatedObjectId=72057594038779904
owner-list
owner id=process688988 mode=U
waiter-list
waiter id=process6fcd48 mode=U requestType=wait
keylock hobtid=72057594038779904 dbid=13 objectname=Procesa.dbo.INFO_LOG indexname=PK_INFO_LOG id=lock93a9240 mode=U associatedObjectId=72057594038779904
owner-list
owner id=process6fcd48 mode=U
waiter-list
waiter id=process688988 mode=U requestType=wait
Does anyone know where's the deadlock and how to solve it? Thanks in advance!
December 13, 2007 at 9:45 pm
It is difficult to determine the exact cause for the dead lock without knowing the data that is being passed in.
I have created a small scale sample with your DDL and some test data and successfully updated the table, so I believe your update is fine and the problem may be the transaction isolation level and/or another spid has a lock on the table.
What is your transaction isolation level? It looks like there is another connection attached to the table that is blocking your update. While running the update have you gone into activity monitor and seen if the process is being blocked? In Activity monitor there is a column "blocked by."
Is it possible that the update has an explicit begin transaction and does not have a commit?
Additional info on isolation levels:
December 14, 2007 at 7:17 am
Oops! it seems that I left out an important part of the log.. the queries! Sorry about that. I'm attaching a small log extraction with more info.
I apologize for not describing the context more clearly. INFO_LOG is a table that holds a transaction log so various threads execute updates against it simultaneously. The deadlock occurs at random, and after some minutes of processing, so some transactions are lost, but the rest of the transactions continue working.
By looking at the sql debug output, isn't the deadlock occurring between two threads executing the same update query?
What is your transaction isolation level?
The isolation level is Read committed, but we don't use of transactions, the queries are executed directly.
While running the update have you gone into activity monitor and seen if the process is being blocked? In Activity monitor there is a column "blocked by."
Because of what I explained before, processing 50 transactions per second, seing something in the monitor is kind of hard.
December 14, 2007 at 8:57 am
This is a quote from the isolation link I sent about Read-Committed Isolation:
In SQL Server 2005, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:
The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
The SNAPSHOT isolation level.
READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared locks are released when the statement completes.
If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.
When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ_COMMITTED isolation level.
By looking at the sql debug output, isn't the deadlock occurring between two threads executing the same update query?
Deadlock is caused by two process trying to aquire the same resource. This may or may not be the same query.
SQL has certain functionality to remove deadlocks automatically. This is why you begin to process after a period of time. The deadlock victim is the process that is the least expensive to rollback.
You could try to change the read_committed_snapshot option to on to reduce contention. You should note that the transaction will be unaware of any of transaction making an update at the same time.
December 14, 2007 at 8:58 am
If you look at the execution plan for that query, do you see an index scan or an index seek for the table INFO_LOG?
Edit: Looked a little closer
That's a rather non-optimal clustered index. Recommendations are that clustering keys are kept narrow. Offhand suggestion, make the PK non-clustered and put the clustered index somewhere else
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2007 at 9:01 am
GilaMonster is this in the wrong thread?
December 14, 2007 at 9:07 am
GilaMonster has a valid point about indexing. What are your indexs on the table and how many records does this table have? At 50 transactions/sec I would imagine a lot. It may be taking a long time to find the record to update and in the meantime another process attempts to execute the same task and begins a pile up.
December 14, 2007 at 9:10 am
Adam Haines (12/14/2007)
GilaMonster is this in the wrong thread?
*sigh* I keep getting posts in the wrong threads when I'm replying to 2 or more at a time. Isn't consistent and I can't figure out what triggers it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2007 at 9:12 am
It happens when you SSCrazy 😀
December 14, 2007 at 10:50 am
Thank you guys for your replies. We've modified the indexes, but we still get a similar error, though with less frequency.
Here are the current indexes:
ADD CONSTRAINT [PK_INFO_LOG] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_INFO_LOG] ON [dbo].[INFO_LOG]
(
[MSG_TYPE] ASC,
[PCODE] ASC,
[TRANS_DATE] ASC,
[TRANS_TIME] ASC,
[PROCESS_DATE] ASC,
[ID_TERMINAL] ASC,
[REFERENCE_NUMBER] ASC,
[RESPONSE_CODE] ASC
)
This suggestion by SSCrazy also aided performance, so... great! thanks for that! 🙂 pitty I still can't get rid of that deadlock...
The query plan shows that it first performs an index seek on IX_INFO_LOG, and then an update on PK_INFO_LOG. (attachment can be renamed to .sqlplan)
The deadlock now occurs between two concurrent updating threads executing the same query as before, on object IX_INFO_LOG. this also can be seen on the attachment
I'll give changing the isolation levels but first I'd like to find out why the deadlock occurs in the first place. You see, as we can only reproduce the deadlock once in a while (this one was after TX 300.000), it becomes very difficult to see if the problem would be really solved...
I'm also attaching the new log.
December 17, 2007 at 8:48 am
Why dont you try using sp_getapplock. That could help with the dead lock issue.
Roy
-Roy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply