Frequent deadlocks

Last Post 29 Jan 2008 11:08 AM by SQLUSA. 14 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
HarryNejad
New Member
New Member

--
29 Jan 2008 10:07 AM

I have a table with 400,000 records.
A PK is there on this table on a combination of two columns

I have one stored procedure that is invoked from the VB interface and gets three parameters and selects a number of records from that table and stores them in a temp table.
The selected records are usually about 10 to 20 records.

I have another stored procedures that has a number of input parameters (two of which are the Primary Key columns of this table) and the stored procedure simply updates ONE single record in that table.
This sp is also invoked by the VBcode through the user interface

When several users (four or five, not really a lot) access the above functionalities at the same time, MOST of the time they get dealocks:

ODBC SQL Server Driver][SQL Server]Transaction (Process ID 139) was deadlock
ed on lock resources with another process and has been chosen as the deadlock vi
ctim. Rerun the transaction.


All the queries have perfectly matching indexes, and thhe select statements have nolock hint, and the update has a rowlock hint.

I have exhausted all the possibilities as to why so many frequent deadlocks happen.


We have larger tables that are also frequently used (selected and updsated) and we never have deadlock problems with them.
But with this table we get a lot of deadlocks.

But why?

And how can I fix it?


Any help will be greatly appreciated.




HarryNejad
New Member
New Member

--
29 Jan 2008 11:00 AM


sp_helpindex REC_TRX_SCRUB gives this:

index_name index_description index_keys
-------------------- ----------------------------------------------------- ----------------------------------
PK_REC_TRX_SCRUB nonclustered, unique, primary key located on PRIMARY ARC_CTR, FAS_FILE_NAME
idx nonclustered located on PRIMARY TRX_FUND_ACCT, TRX_FUND_NUM, MGT_CD


==================================
==================================
==================================
==================================


The first stored procedure (just read) is this:

create procedure sp_MPS_LOAD_COF_SCRUB @MGT_CD varchar(5),
@TRX_FUND_ACCT varchar(15),
@TRX_FUND_NUM varchar(5)

as

set nocount on

insert #MPS9113 (
SELECTED_IND,
TRX_PROC_DT,
TRX_SEQ_NUM,
TRX_CLOSE_BAL,
TRX_UNIT,
TRX_GRSS_AMT,
TRADE_DT,
ARC_CTR,
FAS_FILE_NAME,
TRX_TYPE,
TRX_DETL,
TRX_POS_NEG,
TRX_PROD_IND )
select
SELECTED_IND,
case isdate(TRX_PROC_DT)
when 1 then convert(datetime,TRX_PROC_DT)
else '19000101'
end,
TRX_SEQ_NUM,
convert(numeric(20,4),TRX_UNIT_UISS)/10000 + convert(numeric(20,4),TRX_UNIT_ISS)/10000,
convert(numeric(20,4),TRX_UNIT)/10000,
convert(numeric(20,2),TRX_GRSS_AMT)/100,
case isdate(TRADE_DT)
when 1 then convert(datetime,TRADE_DT)
else '19000101'
end,
ARC_CTR,
FAS_FILE_NAME,
TRX_TYPE,
TRX_DETL,
TRX_POS_NEG,
TRX_PROD_IND
from
REC_TRX_SCRUB with ( nolock )
where
TRX_FUND_ACCT = @TRX_FUND_ACCT and
TRX_FUND_NUM = @TRX_FUND_NUM and
MGT_CD = @MGT_CD


update #MPS9113
set TRX_MNEM = S.TRX_MNEM_ENG,
TRX_FLOW_CD = S.TRX_FLOW_CD,
TRX_OPEN_BAL = TRX_CLOSE_BAL - (S.FACTOR_ACT_UNIT * TRX_UNIT)
from S_TRX_CD S with ( nolock )
where #MPS9113.TRX_TYPE = FAS_CD_IN and
#MPS9113.TRX_DETL = FAS_DTL_IN and
#MPS9113.TRX_POS_NEG = S.TRX_POS_NEG and
#MPS9113.TRX_PROD_IND = S.TRX_PROD_IND and
FAS_PRIM = 'P'

go




==================================
==================================
==================================
==================================


The second stored procedure (update) is this:



create procedure sp_MPS_PROC_SCRUB_DET @ARC_CTR varchar(15),
@FAS_FILE_NAME varchar(17),
@MGT_CD varchar(5),
@TRX_FUND_ACCT varchar(15),
@TRX_FUND_NUM varchar(5),
@TRX_REP_NUM varchar(5),
@TRX_CD varchar(4),
@TRX_SEQ_NUM varchar(15),
@TRX_PROC_DT varchar(8),
@TRADE_DT varchar(8),
@SETTLE_DT varchar(8),
@TRX_OPEN_BAL varchar(20),
@TRX_UNIT varchar(20),
@TRX_UNIT_UISS varchar(20),
@TRX_GRSS_AMT varchar(20),
@TRX_DSC_COMM varchar(20),
@TRX_NET_AMT varchar(20),
@TRX_PRICE varchar(20),
@TRX_FE_COMM varchar(20),
@TRX_DEDU varchar(20),
@TRX_UNIT_ISS varchar(20),
@USER_SYSID int

as

set nocount on

declare @TRX_TYPE varchar(1),
@TRX_DETL varchar(1),
@TRX_POS_NEG varchar(1),
@TRX_PROD_IND varchar(1),
@TRX_ACCT_DSGN char(1)

select @TRX_TYPE = FAS_CD_IN ,
@TRX_DETL = FAS_DTL_IN,
@TRX_POS_NEG = TRX_POS_NEG,
@TRX_PROD_IND = TRX_PROD_IND
from S_TRX_CD with ( nolock )
where TRX_CD = @TRX_CD

-- convert TRX_UNIT (13)

select @TRX_UNIT = convert(varchar(25),convert(numeric(20,4), @TRX_UNIT)*10000)

select @TRX_UNIT = substring(@TRX_UNIT,1, charindex('.', @TRX_UNIT)-1)

while len(
SQLUSA
New Member
New Member

--
29 Jan 2008 11:08 AM
Your trouble is

insert #MPS9113

followed by

update #MPS9113

Break up the stored proc into 2, one insert sproc, one update.

Kalman Toth
http://www.sqlusa.com/highperformance2005
HarryNejad
New Member
New Member

--
29 Jan 2008 11:08 AM

Also, as regards to the locking chain in profiler:

In the "Events" tab, I have chosen the entire "Locks" event class (and all its subclasses)

Also, I have chosen: Performance => Execution plan

When I run profiler, I get a whole lot of rows like:
Lock: Acquired
Lock: Released
etc.

But it does not tell me what type of lock was acquired.

Is there any specific "Column" that I have to choose as well?



Also, even the execution plan says nothing about the lock type

Am I missing anything?


thanks,

SQLUSA
New Member
New Member

--
29 Jan 2008 11:12 AM
As a rule of thumb

NEVER HIT THE SAME TABLE MORE THAN ONCE IN A STORED PROC.

Kalman Toth
http://www.sqlusa.com/order2005highperformance
HarryNejad
New Member
New Member

--
29 Jan 2008 02:13 PM

When I set statistics io on and run just a solitary update query (taken out of the stored procedure), I get this:


Table 'REC_TRX_SCRUB'. Scan count 1, logical reads 13, physical reads 0, read-ahead reads 0.

(1 row(s) affected)

------------------------------------------------------------------------

If I run the sp itself (that contains the above query), I get this:

Table 'S_TRX_CD'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0.
Table 'REC_TRX_SCRUB'. Scan count 1, logical reads 22, physical reads 5, read-ahead reads 0.
Table 'REC_TRX_SCRUB'. Scan count 1, logical reads 13, physical reads 2, read-ahead reads 0.


------------------------------------------------------------------------

How do I interpret this?
Is it good or bad?
What does it tell me about the likelihood of a deadlock?



thanks,

HarryNejad
New Member
New Member

--
29 Jan 2008 02:15 PM


Also, how can I see the deadlock chain?

Do you mean in profiler?

If so, in profiler on the "Events" tab, I have selected "Locks" class (including all its subclasses)

And I get a lot of rows like:

Lock: Acquired
Lock: Released

But does not tell me what table and what kind of lock


How can I fnd out these kind of details in profiler?

Am I missing any "Column" that I need to add to the "Columns" list?

Or am I missing something else?


thanks,

SQLUSA
New Member
New Member

--
29 Jan 2008 10:55 PM
SQL Server 2005 has visual deadlock tracing facility.

It is absolutely great.

In SQL Server 2000, you have to turn on specific tracing.

The trace results will go to the error log.

As I stated, breakup the offending stored proc and your problem will be solved.

BOL: Keep transactions short and in one batch.


Independently Russell has a good point about index performance. Reindex all DYNAMIC tables with FILL FACTOR 70 every night.


Kalman Toth
http://www.sqlusa.com/highperformance2005
SQLUSA
New Member
New Member

--
30 Jan 2008 04:37 AM
Harry,

More notes: all those WHILE-s should be replaced by string concatation using the RIGHT string function.

Traceflag setting for deadlock detection:

DBCC TRACEON(3605, 1204)



Kalman Toth
http://www.sqlusa.com/order2005highperformance
SQLUSA
New Member
New Member

--
30 Jan 2008 06:22 AM
Russell,

If this is a 2005 topic, can you move it over to 2005 T-SQL?

Harry,

Let me know if this is 2005. I will tell you how to break the deadlock real easy.

Andy,

By all fairness, the code listed above is telltale about a beginner T-SQL developer. It requires complete reengineering.


Kalman Toth
High Performance SQL Server 2005: http://www.sqlusa.com/order2005highperformance
SQLUSA
New Member
New Member

--
30 Jan 2008 08:06 AM
Russell,

I agree with you that performance indexes are important.

Several items point to a beginner T-SQL coder like using WHILE for leading 0-s for multiple times, the way temp table is being used, the rowlock usage is suspect as well ...

DBCC TRACEON(3605, 1204)

is good, a little bit hard to read in error log, but basically it will tell the story.

The 2005 visual deadlock analyzer is simply great.

I am still waiting which way to go... 2000 or 2005 .

Harry,

if 2000, pls. list us the TRACE log result from sql server 2000 error log.

Kalman Toth
http://www.sqlusa.com
SwePeso
New Member
New Member

--
01 Feb 2008 02:53 PM
insert #MPS9113 with (rowlock)
(
TRX_MNEM,
TRX_FLOW_CD,
TRX_OPEN_BAL,
SELECTED_IND,
TRX_PROC_DT,
TRX_SEQ_NUM,
TRX_CLOSE_BAL,
TRX_UNIT,
TRX_GRSS_AMT,
TRADE_DT,
ARC_CTR,
FAS_FILE_NAME,
TRX_TYPE,
TRX_DETL,
TRX_POS_NEG,
TRX_PROD_IND
)
select case when b.FAS_PRIM = 'P' then S.TRX_MNEM_ENG else b.TRX_MNEM end,
case when b.FAS_PRIM = 'P' then S.TRX_FLOW_CD else b.TRX_FLOW_CD end,
case when b.FAS_PRIM = 'P' then convert(numeric(20, 4), b.TRX_UNIT_UISS) / 10000 + convert(numeric(20,4), b.TRX_UNIT_ISS) / 10000 - (S.FACTOR_ACT_UNIT * convert(numeric(20, 4), b.TRX_UNIT) / 10000) else b.TRX_OPEN_BAL end,
b.SELECTED_IND,
case isdate(b.TRX_PROC_DT)
when 1 then convert(datetime, b.TRX_PROC_DT)
else '19000101'
end,
b.TRX_SEQ_NUM,
convert(numeric(20, 4), b.TRX_UNIT_UISS) / 10000 + convert(numeric(20,4), b.TRX_UNIT_ISS) / 10000,
convert(numeric(20, 4), b.TRX_UNIT) / 10000,
convert(numeric(20, 2), b.TRX_GRSS_AMT) / 100,
case isdate(b.TRADE_DT)
when 1 then convert(datetime, b.TRADE_DT)
else '19000101'
end,
b.ARC_CTR,
b.FAS_FILE_NAME,
b.TRX_TYPE,
b.TRX_DETL,
b.TRX_POS_NEG,
b.TRX_PROD_IND
from REC_TRX_SCRUB as b with (nolock)
left join S_TRX_CD as S with (nolock) on s.FAS_CD_IN = b.TRX_TYPE
and s.FAS_DTL_IN = b.TRX_DETL
and s.TRX_POS_NEG = b.TRX_POS_NEG
and s.TRX_PROD_IND = b.TRX_PROD_IND
where b.TRX_FUND_ACCT = @TRX_FUND_ACCT
and b.TRX_FUND_NUM = @TRX_FUND_NUM
and b.MGT_CD = @MGT_CD
SwePeso
New Member
New Member

--
01 Feb 2008 03:23 PM
create procedure sp_MPS_PROC_SCRUB_DET
(
@ARC_CTR varchar(15),
@FAS_FILE_NAME varchar(17),
@MGT_CD varchar(5),
@TRX_FUND_ACCT varchar(15),
@TRX_FUND_NUM varchar(5),
@TRX_REP_NUM varchar(5),
@TRX_CD varchar(4),
@TRX_SEQ_NUM varchar(15),
@TRX_PROC_DT varchar(8),
@TRADE_DT varchar(8),
@SETTLE_DT varchar(8),
@TRX_OPEN_BAL varchar(20),
@TRX_UNIT varchar(20),
@TRX_UNIT_UISS varchar(20),
@TRX_GRSS_AMT varchar(20),
@TRX_DSC_COMM varchar(20),
@TRX_NET_AMT varchar(20),
@TRX_PRICE varchar(20),
@TRX_FE_COMM varchar(20),
@TRX_DEDU varchar(20),
@TRX_UNIT_ISS varchar(20),
@USER_SYSID int
)
as

set nocount on

declare @TRX_TYPE varchar(1),
@TRX_DETL varchar(1),
@TRX_POS_NEG varchar(1),
@TRX_PROD_IND varchar(1),
@TRX_ACCT_DSGN char(1)

select @TRX_TYPE = FAS_CD_IN,
@TRX_DETL = FAS_DTL_IN,
@TRX_POS_NEG = TRX_POS_NEG,
@TRX_PROD_IND = TRX_PROD_IND
from S_TRX_CD with (nolock)
where TRX_CD = @TRX_CD

-- determine account designation
select @TRX_ACCT_DSGN = min(TRX_ACCT_DSGN)
from REC_TRX_SCRUB with (nolock)
where MGT_CD = @MGT_CD
and TRX_FUND_ACCT = @TRX_FUND_ACCT
and TRX_FUND_NUM = @TRX_FUND_NUM
and TRX_ACCT_DSGN in ('1', '2', '3')

if @TRX_ACCT_DSGN is null
select @TRX_ACCT_DSGN = min(ADM.DESIGNATION_CD)
from ACT with (nolock)
inner join IVD with (nolock) on IVD.IVD_SYSID = ACT.IVD_SYSID
inner join PLN with (nolock) on PLN.PLN_SYSID = ACT.PLN_SYSID
inner join ADM with (nolock) on ADM.ADM_CD = PLN.PLN_ADM_CD
where IVD.MGT_CD = @MGT_CD
and ACT_MGT_NUM = @TRX_FUND_ACCT

if @TRX_ACCT_DSGN is null
set @TRX_ACCT_DSGN = '1'

-- save data
update REC_TRX_SCRUB with (rowlock)
set TRX_PROC_DT = @TRX_PROC_DT,
TRX_SEQ_NUM = @TRX_SEQ_NUM,
TRADE_DT = @TRADE_DT,
TRX_TYPE = @TRX_TYPE,
TRX_DETL = @TRX_DETL,
TRX_POS_NEG = @TRX_POS_NEG,
TRX_PROD_IND = @TRX_PROD_IND,
SETTLE_DT = @SETTLE_DT,
MGT_CD = @MGT_CD,
TRX_FUND_ACCT = @TRX_FUND_ACCT,
TRX_FUND_NUM = @TRX_FUND_NUM,
TRX_REP_NUM = @TRX_REP_NUM,
TRX_UNIT = REPLACE(STR(@TRX_UNIT * 10000, 13, 0), ' ', '0'),
TRX_GRSS_AMT = REPLACE(STR(@TRX_GRSS_AMT * 100, 11, 0), ' ', '0'),
TRX_NET_AMT = REPLACE(STR(@TRX_NET_AMT * 100, 11, 0), ' ', '0'),
TRX_UNIT_UISS = CASE CHARINDEX('-', @TRX_UNIT_UISS)
WHEN 0 THEN REPLACE(STR(@TRX_UNIT_UISS * 10000, 13, 0), ' ', '0')
ELSE '-' + REPLACE(STR(ABS(@TRX_UNIT_UISS) * 10000, 12, 0), ' ', '0')
END,
TRX_UNIT_ISS = CASE CHARINDEX('-', @TRX_UNIT_ISS)
WHEN 0 THEN REPLACE(STR(@TRX_UNIT_ISS * 10000, 13, 0), ' ', '0')
ELSE '-' + REPLACE(STR(ABS(@TRX_UNIT_ISS) * 10000, 12, 0), ' ', '0')
END,
TRX_PRICE = REPLACE(STR(@TRX_PRICE * 10000, 7, 0), ' ', '0'),
TRX_DSC_COMM = REPLACE(STR(@TRX_DSC_COMM * 1000, 5, 0), ' ', '0'),
TRX_FE_COMM = REPLACE(STR(@TRX_FE_COMM * 1000, 5, 0), ' ', '0'),
TRX_DEDU = REPLACE(STR(@TRX_DEDU * 100, 10, 0), ' ', '0'),
TRX_ACCT_DSGN = @TRX_ACCT_DSGN,
LAST_UPD_DT = getdate(),
USER_SYSID = @USER_SYSID,
SELECTED_IND = 1
where ARC_CTR = @ARC_CTR
and FAS_FILE_NAME = @FAS_FILE_NAME
SQLUSA
New Member
New Member

--
02 Feb 2008 01:14 PM
Nice job Peter!

Kalman Toth, Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005grandslam/
SwePeso
New Member
New Member

--
04 Feb 2008 02:11 AM
Thanks.
You are not authorized to post a reply.

Acceptable Use Policy