Deadlock

Question

Deadlock

Maverick100877

SSCertifiable

Points: 5240
More actions
October 10, 2005 at 6:08 am

#165524

Hi All
There is a scenario where a situation of deadlock is happeneing.But I'm unable to make out as to why this is happening.I have even turned or the trace flag 1204 but its report is also incomplete and is not helpful.The scenario is like this
We have agents who do calling and they have been given a software for it .It is built in vb.net and the backend is SQL.Now the number is dialled and the records of the customer are shown to the agent.This whole script runs in VB caontainer.Now when the agent capture the data and click on save then only the deadlock happens on one table.
The chain of events is like this
As soon as the agent click on save
The record is inserted into table A.On this table there is a trigger for insert and it updates table B with the required records from table A.
Then the records are inserted in table C which is history table.
After this the records from the table A are inserted into table D
and finally deleted from Table A.

Now in this scenario the message that comes is for Table A where the deadlock happens.
Could somebody please help me out with this as to how to solve this.

A NewBie

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Michael David Right there with Babe Points: 788 More actions · Answer 1

Could you provide more information on your transaction control. Do you have explicit transaction?. Do all the Insert\delete happen under the same process id? Also, did you look at BOL under "Deadlocking". It has a good explanation of how dealocks occur.

Michael.

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 2

Well no they do not happen under the same process id...Yes I have gone thrugh it but could not resolve the problem.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 3

Enable 1205 and 3605 as well and then look at your deadlock output since that will shell out more information for you. The actions that occur as part of the trigger are part of the same transaction so if I understood your explanation correctly, within the same transaction, this is what the code does:

begin tran

insert into A

update B with a join with values from A (via the trigger for insert)

insert into C

insert into D

delete from A

commit/rollback

Is that correct ? Also, are multiple spids involved in the deadlock i.e. is it a deadlock chain or just between 2 sessions ? If between 2 sessions, is the second session trying to do the same set as mentioned above ? Have you evalauted the execution plans of the statements to ensure that when you are firing of a delete/update, the access is based off indexed columns to prevent blocking on indexes. Also, inserts into child tables involve a look-up on the parent table records and can cause issues as well under ceretain conditions (usage of clustered indexes for the PK)...see explanation on it in this post:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=218641#bm219257

Also, what isolation level are you using ?

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 4

Hi RSharma

Yes the transaction happens like this only and the updation happend through a trigger which is fired on insertion in table A.

Well the link u had given was really great but since I'm new to this so wasn't really able to make it out.I'm just pasting below the trace flag 1204 report and see if after seeing this you can help me out.

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:133 ECID:0 Ec0x665ED558) Value:0x20

spid4 Victim Resource Owner:

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:134 ECID:0 Ec0x724D3558) Value:0x4c

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 133 ECID: 0 Statement Type: UPDATE Line #: 16

spid4 Owner:0x207dcbe0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:133 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:89:0 CleanCnt:1 Mode: U Flags: 0x2

spid4 Node:2

spid4

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:133 ECID:0 Ec0x665ED558) Value:0x20

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 134 ECID: 0 Statement Type: UPDATE Line #: 50

spid4 Owner:0x20a468c0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:134 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:13215:9 CleanCnt:1 Mode: X Flags: 0x2

spid4 Node:1

spid4

spid4 Wait-for graph

spid4

spid4 ...

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:112 ECID:0 Ec0x20AF3558) Value:0x20

spid4 Victim Resource Owner:

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:138 ECID:0 Ec0x6ABEB558) Value:0x21

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 112 ECID: 0 Statement Type: UPDATE Line #: 37

spid4 Owner:0x23d9a9c0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:112 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:89:0 CleanCnt:1 Mode: U Flags: 0x2

spid4 Node:2

spid4

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:112 ECID:0 Ec0x20AF3558) Value:0x20

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 138 ECID: 0 Statement Type: UPDATE Line #: 50

spid4 Owner:0x1f756360 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:138 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:13098:3 CleanCnt:1 Mode: X Flags: 0x2

spid4 Node:1

spid4

spid4 Wait-for graph

spid4

spid4 ...

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:140 ECID:0 Ec0x6F9B1558) Value:0x21

spid4 Victim Resource Owner:

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:140 ECID:0 Ec0x6F9B1558) Value:0x21

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 129 ECID: 0 Statement Type: UPDATE Line #: 50

spid4 Owner:0x1f55dc80 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:129 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:13243:0 CleanCnt:1 Mode: X Flags: 0x2

spid4 Node:2

spid4

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:129 ECID:0 Ec0x61FAB558) Value:0x20

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 140 ECID: 0 Statement Type: UPDATE Line #: 37

spid4 Owner:0x1f5706c0 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:140 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:89:0 CleanCnt:1 Mode: U Flags: 0x2

spid4 Node:1

spid4

spid4 Wait-for graph

spid4

spid4 ...

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:0 Ec0x33A33558) Value:0x1f6

spid4 Victim Resource Owner:

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:0 Ec0x33A33558) Value:0x1f6

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 129 ECID: 0 Statement Type: UPDATE Line #: 50

spid4 Owner:0x1f55dc80 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:129 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:13243:0 CleanCnt:1 Mode: X Flags: 0x2

spid4 Node:3

spid4

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:140 ECID:0 Ec0x6F9B1558) Value:0x1f

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 37

spid4 Owner:0x1f820340 Mode: U Flg:0x0 Ref:0 Life:00000001 SPID:55 ECID:0

spid4 Grant List 0::

spid4 RID: 20:1:89:0 CleanCnt:2 Mode: U Flags: 0x2

spid4 Node:2

spid4

spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:129 ECID:0 Ec0x61FAB558) Value:0x20

spid4 Requested By:

spid4 Input Buf: Language Event: insert into capturecalllist( I3_RowId,PhoneNumber,Zo

spid4 SPID: 140 ECID: 0 Statement Type: UPDATE Line #: 37

spid4 Owner:0x1f5706c0 Mode: U Flg:0x0 Ref:1 Life:00000001 SPID:140 ECID:0

spid4 Wait List:

spid4 RID: 20:1:89:0 CleanCnt:2 Mode: U Flags: 0x2

spid4 Node:1

spid4

spid4 Wait-for graph

spid4

spid4 ...

Also its a deadlock chain whcih happens and the complte work comes to a standstill becasue of this.Help me out for this as it is really hampering the production....Waiting for a prompt reply.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 5

Without looking at your table structures, their indexes and the queries involved, this becomes tough but here goes:

The deadlock occurs when the update statement on B is being run (btw, this logic could have been in the application rather than in a trigger). In your trigger definition, when the update is being fired on table B, how are you selecting the records from table A ? That query seems to be qualifying the un-committed records (the other inserts into table A) from other sessions and thus waits.

Example:

create table tablea(col1 int primary key)

go

begin tran

insert into tablea values (1)

insert into tablea values (2)

commit tran

Session 1:

begin tran

insert into tablea values (3)

--do not commit yet

Session 2:

set lock_timeout 1000

select * from tablea where col1 > 1

--you will get a lock timeout after 1 second. This is under the default READ COMMITTED isolation level.

In your scenario (assuming that it is the default isolation level, all indexes are properly designed, all FKs are properly indexed), there could be two reasons for this deadlock (and deadlock chains as well):

1) The reason mentioned above i.e. your update on B is waiting on un-committed records in A from other sessions because of the way the query is written.

2) The other reason could be the INSERT issue that I had mentioned in the URL above. What is the relationship between A and B ? Is there an explicit foreign key defined between the two ?

If it is #1, then the query needs to be modified or you need to use one of the locking hints to either : a) READPAST the locked row, b) Use dirty reads (this has other issues and cannot be used in all scenarios).

If it is #2, changing the PK from clustered to non-clustered index would resolve this.

Hth

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 6

Hi

The definition of the trigger is like this

CREATE trigger Tr_Data_update ON dbo.capturecalllist

FOR INSERT AS

Declare @DispoType Varchar(255)

Declare @I3_RowID Varchar(30)

Set @DispoType=(Select DispoType From INSERTED)

Set @I3_RowID=(Select I3_RowID From INSERTED)

If Ltrim(Rtrim(@DispoType)) = '928'

Update Calllist Set Status='S',DispoType=@DispoType,SaleDate=B.SaleDate,saletime=B.saletime,AgentName=B.AgentName,

Notes=B.SchedNotes,Callbact=B.Callbact

FROM INSERTED B Where Calllist.I3_RowID=B.I3_RowID And Calllist.I3_RowID=@I3_RowID

else if Ltrim(Rtrim(@Dispotype)) In (Select OutcomeCode From AAleadgen.dbo.OutcomeCode Where Sale=1 And Process='AAleadgen')

Update Calllist Set Status='U',DispoType=B.DispoType,AgentName=B.AgentName,

FName=B.FName,Saledate=B.Saledate,saletime=B.saletime,

num_calls=b.num_calls,Comments=b.Comments,

SchedNotes=B.SchedNotes,SchedDate=B.SchedDate,SchedTime=B.SchedTime,AAMembershipNumber=B.AAMembershipNumber

,Address1=B.Address1,Address2=B.Address2,PostCode=B.PostCode,

MOTLEAD=B.MOTLEAD,MOTDATE=B.MOTDATE,POLICYDATE=B.POLICYDATE,TYPE=B.TYPE,

RenewDtMotor=B.RenewDtMotor,Con_EstMotor=B.Con_EstMotor,RenewDtHome=B.RenewDtHome,Con_EstHome=B.Con_EstHome,

RenewDtBuilding=B.RenewDtBuilding,Con_EstBuilding=B.Con_EstBuilding,CBReason=B.CBReason,

RenewDtContent=B.RenewDtContent,Con_EstContent=B.Con_EstContent,MotLdNature=B.MotLdNature,

BldLdNature=B.BldLdNature,CntLdNature=B.CntLdNature,CompCategory=B.CompCategory

From INSERTED B Where Calllist.I3_RowID=B.I3_RowID And Calllist.I3_RowID=@I3_RowID

Else if Ltrim(Rtrim(@Dispotype)) = '922'

Update Calllist Set Status='U',DispoType=B.DispoType,

-- rejectionreason=B.rejectionreason,RefusalReason=B.RefusalReason,

AgentName=B.AgentName,SaleDate=B.SaleDate,saletime=B.saletime,Comments=b.Comments

From INSERTED B Where Calllist.I3_RowID=B.I3_RowID And Calllist.I3_RowID=@I3_RowID

Else

Update Calllist Set Status='U',DispoType=B.DispoType,AgentName=B.AgentName,SaleDate=B.SaleDate,saletime=B.saletime,

num_calls=b.num_calls,Comments=b.Comments,

SchedNotes=B.SchedNotes,SchedDate=B.SchedDate,SchedTime=B.SchedTime,

AAMembershipNumber=B.AAMembershipNumber

,Address1=B.Address1,Address2=B.Address2,PostCode=B.PostCode,

MOTLEAD=B.MOTLEAD,MOTDATE=B.MOTDATE,POLICYDATE=B.POLICYDATE,TYPE=B.TYPE,

RenewDtMotor=B.RenewDtMotor,Con_EstMotor=B.Con_EstMotor,RenewDtHome=B.RenewDtHome,Con_EstHome=B.Con_EstHome,

RenewDtBuilding=B.RenewDtBuilding,Con_EstBuilding=B.Con_EstBuilding,CBReason=B.CBReason,

RenewDtContent=B.RenewDtContent,Con_EstContent=B.Con_EstContent,MotLdNature=B.MotLdNature,

BldLdNature=B.BldLdNature,CntLdNature=B.CntLdNature,CompCategory=B.CompCategory

From INSERTED B Where Calllist.I3_RowID=B.I3_RowID And Calllist.I3_RowID=@I3_RowID

Update Calllist Set Attempts=IsNull(Attempts,0)+1 Where I3_RowID=@I3_RowID

The logic is like that there is a primary table Calllist and the capturecalllist table is its copy.Now the record is selected from the Calllist based on the I3_rowid of the phonenumber which is dialled and shown on the screen.

now the agent capture the data and click on save at this moment the record is inserted in the table Capturecalllist and on its insert the above trigger is fired whcih updates the record in the Primary table Calllist.

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 7

Q1) Is there a foreign key relationship between CALLLIST and CAPTURECALLLIST ? If so, is the foreign key indexed ?

And what type of index is it on the PK ? Clustered or non-clustered ?

Q2) Also, why do you need to insert into CaptureCallList table at all ? Why can't the code directly update the CALLLIST table

as needed ?

Q3) Is CALLLIST.I3_RowID indexed ?

For the trigger code:

1) If you insert more than one record in capturecalllist table, then your trigger will have errors since you are assigning

local variables values from the inserted table and in the event of multiple rows, the assignment would fail.

2) The code is doing an update on CallList without doing a select for update first (look up BOL for UPDLOCK - doing an update without

a "select for update i.e. using the UPDLOCK hint" can lead to conversion deadlocks) - you need to do a select WITH (UPDLOCK) first to prevent any such conversion deadlocks.

I would do this:

1) Get CAPTURECALLLIST out of the picture completely - there is rarely a need to have identical "clone" tables

in the database...this will negate the usage of the trigger as well.

2) Set lock timeout to a certain value for the connection. Do a select with (UPDLOCK, ROWLOCK) on the CALLLIST table.

3) The do the update on that table.

4) Followed by any other subsequent operations that are needed to be part of the same transaction.

And have proper error handling logic in place.

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 8

Ans1)No there is no foreign Key relationship between Calllist and CaptureCalllist. Also there is no PK in the Calllist table and no index.

Ans2)The data is inserted into the capturecalllist so that in case of any error we have the data in the capturecalllist table and so can update the calllist with the same.I have to just check with the developers once for the logic behind this.

Ans3)no it is not indexed.

Also would like to ask one more question--Creating indexex on tables which have frequesnt updations and inserts costs a lost so is it advisable to create indexes on them???

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 9

Your updates (and the select for updates i.e. WITH (UPDLOCK)) have to be based on indexed columns otherwise you will be locking the entire table. Right now, you have a heap table - I am very surprised with the way your tables are structured. Firstly, there is redundancy - there is absolutely no need to maintain a clone table. The statement that "..in case of an error.." is flawed because the trigger is part of the same transaction and hence if the update to CALLLIST fails, the insert into capturecalllist will also fail.

Having un-necessary indexes on a table will lead to an overhead during DML (update, delete and inserts), however indexes are needed to tune the selects as well as the updates and deletes (as well as any insert into...select from...clauses) so that you do not end up scanning the whole table. So, please ensure that CALLLIST.3_RowID is indexed and I am assuming that it has good selectivity (does this column have unique values ?) since that is the only column that is being used in the filter criteria and then do 1,2,3 and 4 that I mentioned above:

/*******************

1) Get CAPTURECALLLIST out of the picture completely - there is rarely a need to have identical "clone" tables

in the database...this will negate the usage of the trigger as well.

2) Set lock timeout to a certain value for the connection. Do a select with (UPDLOCK, ROWLOCK) on the CALLLIST table.

3) The do the update on that table.

4) Followed by any other subsequent operations that are needed to be part of the same transaction.

*********************/

Gift Peddie SSC Guru Points: 73570 More actions · Answer 10

Hi,

I am sorry I thought your problem was resolved by now, try these links for code to resolve Deadlock and Asp.net centric trigger code to make changes to your existing code. Hope this helps.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=223015#bm223471

http://www.aspfaq.com/show.asp?id=2448

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie