Deadlock on update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deadlock on update

Author

Topic

Miguel_Hughes
Starting Member

2 Posts

Posted - 2007-12-13 : 15:36:19

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.
Does anyone know why i'm getting a deadlock and how to solve it? Thanks in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-13 : 15:56:38

my guess would be that your deadlock happens when your locks convert from IU (Intent update) to X (exclusive) mode.
plus you're updating a clustered index key which isn't a good idea.

so first stop updating the clustered index key.
second you can try to increase the isolation level for this update or use the (HoldLock) hint.
this of course depends on your business requirements and can't be generalized.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

Miguel_Hughes
Starting Member

2 Posts

Posted - 2007-12-13 : 16:26:11

Thanks for replying!

quote:

my guess would be that your deadlock happens when your locks convert from IU (Intent update) to X (exclusive) mode.

Here is a debug output on that:

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

So its deadlocking when attempting to obtain a U lock, right? Anyways I don't understand how can it be waiting for a lock on the key if it already has that lock...
Is this what this log means?

quote:

plus you're updating a clustered index key which isn't a good idea.
so first stop updating the clustered index key.
second you can try to increase the isolation level for this update or use the (HoldLock) hint.
this of course depends on your business requirements and can't be generalized.

Thing is, the table is already being used by our client and I don't think they'll be very happy if with us adding or changing the indexes... Anyways I'll keep your suggestion in mind for future implementations
I'll try the hint or changing the isolation level and see how it goes. Thanks a bunch!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources