Deadlocking problems with SPs - 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 2000 Forums

SQL Server Administration (2000)

Deadlocking problems with SPs

Author

Topic

MuadDBA

628 Posts

Posted - 2004-05-10 : 11:58:51

When I took my current job, I was 90% DBA and 10% developer. Now, I am doing more devleoper work and less DBA work, and my weaknesses are showing.

First, some background on the system:

It's an OCR system for automating validation of bank documents. We have 2 main tables, LOAN and DOCUMENT.

The LOAN table has most of the info that is global to the entire loan (Loan number, product type, correspondant, closing date, etc). This table gets information from a nightly file feed, and is refreshed live (based on a status code field) from a loan origination system.

The DOCUMENT table has info specific to documents which are scanned into an imaging workflow system (loan number, document type, object_id, street addresses, various other document level information). This information is added by the OCR software. Once this is done, a set of business rules is run against it, and it results in message codes benig appended to the image, and these codes are then brought back into the workflow imaging system.

The origination system is constantly polling for items which need their data refreshed, and the logic engine is also constatly polling for items which are ready for the business rules to be run. The workflow system also polls for data and will cut the OCR process short if someone manually reviews the documents instead of waiting for them to be reveiwed automatically.

All of these systems poll based on a status code field present at both the loan and the document level. As you will see when I post the code for the stored procedure interfaces, each time one table's status code is updated, it updates the status code for the other table so the statuses are kept in synch. Also, each system is polling for DIFFERENT status codes and updating them (ie status 320 means OCR should extract info, status 500 means origination system should refresh, status 550 means logic engine should run, etc).

My problem is that I am experiencing deadlocks several times during the day. Can you see any reason why this should happend based on my code?

CREATE PROCEDURE SP_LOAN_UPDATE
@eiStream_ID [varchar](15) = '-1' ,
@Status [varchar](4) = '-1' ,
@M_refresh_date_time [datetime] = '12/31/2999' ,
@M_loan [varchar](10) = '-1' ,
@M_loan_status [varchar](3) = '-1' ,
@M_loan_type [varchar](4) = '-1' ,
@M_financing_type [int] = -1 ,
@M_program_type [int] = -1 ,
@M_program_sub_type [int] = -1 ,
@M_note_rev_date [datetime] = '12/31/2999' ,
@M_audit_flag [varchar](2) = '-1' ,
@M_MI_coverage [varchar](2) = '-1' ,
@M_lender_type [numeric](10,0) = -1 ,
@M_state_code [varchar](2) = '-1' ,
@M_purpose_code [smallint] = -1 ,
@M_closing_date [datetime] = '12/31/2999' ,
@M_CLC_code [varchar](2) = '-1' ,
@M_condition_0176 [varchar](2) = '-1' ,
@M_city [varchar](30) = '-1' ,
@M_bldg_no [varchar](10) = '-1' ,
@M_street_no [varchar](10) = '-1' ,
@M_pre_dir [varchar](2) = '-1' ,
@M_street_name [varchar](50) = '-1' ,
@M_post_dir [varchar](2) = '-1' ,
@M_street_type [varchar](7) = '-1' ,
@M_apt_no [varchar](6) = '-1' ,
@M_state [varchar](2) = '-1' ,
@M_ZIP [varchar](10) = '-1' ,
...
@S_Loan_Type [varchar](10) = '-1' ,
@Source [varchar](4) = null,
@NO_HISTORY [varchar](10) = 'history',
@INT_CALL tinyint = 0,
@ERR [int] = 3 output
as
declare @RC int
declare @nummostosubtract int /* Number of months to subtract from IPTD from mupit*/
select @nummostosubtract = 0
select @source = rtrim(@source)
select @status = rtrim(@status)
IF @M_LOAN is null OR @M_LOAN = '-1' OR @STATUS = '-1' OR @STATUS is null OR @status not in
(select status from STATUS_CODES)
BEGIN
PRINT 'You must supply a valid LOAN and STATUS code'
SELECT @ERR = 4
SELECT @ERR
RAISERROR (50004,16,2)
RETURN @ERR
END
exec SP_SOURCE_CHECK @SOURCE, @ERR output
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END

select @eistream_id = eistream_id from document where loan = @m_loan
UPDATE LOAN SET
eistream_id = CASE
when @eistream_id = '-1' then eistream_id
Else @eistream_id
End,
status = @status,
time_stamp = getdate(),
m_refresh_date_time = CASE
when @m_refresh_date_time = '12/31/2999' AND @source<> 'M' then m_refresh_date_time
when @m_refresh_date_time = '12/31/2999' AND @source = 'M' and @status not in ('530','560') then m_refresh_date_time
when @source = 'M' and @status in ('530','560') then getdate()
Else @m_refresh_date_time
end,
m_loan = @m_loan,
m_loan_status = CASE
when @m_loan_status = '-1' OR @source<> 'M' then m_loan_status
Else @m_loan_status
end,
m_loan_type = CASE
when @m_loan_type = '-1' OR @source<> 'M' then m_loan_type
Else @m_loan_type
end,
m_financing_type = CASE
when @m_financing_type = -1 OR @source<> 'M' then m_financing_type
Else @m_financing_type
end,
m_program_type = CASE
when @m_program_type = -1 OR @source<> 'M' then m_program_type
Else @m_program_type
end,
m_program_sub_type = CASE
when @m_program_sub_type = -1 OR @source<> 'M' then m_program_sub_type
Else @m_program_sub_type
end,
m_note_rev_date = CASE
when @m_note_rev_date = '12/31/2999' OR @source<> 'M' then m_note_rev_date
Else @m_note_rev_date
end,
m_audit_flag = CASE
when @m_audit_flag = '-1' OR @source<> 'M' then m_audit_flag
Else @m_audit_flag
end,
m_MI_coverage = CASE
when @m_MI_coverage = '-1' OR @source<> 'M' then m_MI_coverage
Else @m_MI_Coverage
end,
m_lender_type = CASE
when @m_lender_type = -1 OR @source<> 'M' then m_lender_type
Else @m_lender_type
end,
m_state_code = CASE
when @m_state_code = '-1' OR @source<> 'M' then m_state_code
Else @m_state_code
end,
m_purpose_code = CASE
when @m_purpose_code = '-1' OR @source<> 'M' then m_purpose_code
Else @m_purpose_code
end,
m_closing_date = CASE
when @m_closing_date = '12/31/2999' OR @source<> 'M' then m_closing_date
Else @m_closing_date
end,
m_clc_code = CASE
when @m_CLC_code = '-1' OR @source<> 'M' then m_clc_code
Else @m_clc_code
end,
m_condition_0176 = CASE
when @m_condition_0176 = '-1' OR @source<> 'M' then m_condition_0176
Else @m_condition_0176
end,
m_city = CASE
when @m_city = '-1' OR @source<> 'M' then m_city
Else @m_city
end,
m_bldg_no = CASE
when @m_bldg_no = '-1' OR @source<> 'M' then m_bldg_no
Else @m_bldg_no
end,
m_street_no = CASE
when @m_street_no = '-1' OR @source<> 'M' then m_street_no
Else @m_street_no
end,
m_pre_dir = CASE
when @m_pre_dir = '-1' OR @source<> 'M' then m_pre_dir
Else @m_pre_dir
end,
m_street_name = CASE
when @m_street_name = '-1' OR @source<> 'M' then m_street_name
Else @m_street_name
end,
m_post_dir = CASE
when @m_post_dir = '-1' OR @source<> 'M' then m_post_dir
Else @m_post_dir
end,
m_street_type = CASE
when @m_street_type = '-1' OR @source<> 'M' then m_street_type
Else @m_street_type
end,
m_apt_no = CASE
when @m_apt_no = '-1' OR @source<> 'M' then m_apt_no
Else @m_apt_no
end,
m_state = CASE
when @m_state = '-1' OR @source<> 'M' then m_state
Else @m_state
end,
m_zip = CASE
when @m_zip = '-1' OR @source<> 'M' then m_zip
Else @m_zip
end,
...
s_loan_type = CASE
when @s_loan_type = '-1' OR @source <> 'S' or @s_loan_type not in ('CONV', 'ARM') then s_loan_type
else @s_loan_type
end
Where m_loan = @m_loan
Select @RC = @@ROWCOUNT
IF @RC = 1 and @NO_HISTORY = 'history'
BEGIN
exec SP_HISTORY_APPEND @m_loan, @eistream_id, @status, @source, @err output
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END
IF @INT_CALL = 0 and @EISTREAM_ID <> '-1'
begin
exec @err = SP_DOCUMENT_UPDATE @LOAN = @M_LOAN, @EISTREAM_ID = @EISTREAM_ID, @STATUS = @STATUS, @SOURCE = @SOURCE, @NO_HISTORY ='NO_HISTORY', @INT_CALL = 1
end
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END
END
ELSE
BEGIN
IF @RC = 1 and @NO_HISTORY = 'NO_HISTORY'
BEGIN
IF @INT_CALL = 0 and @EISTREAM_ID <> '-1'
begin
exec @err = SP_DOCUMENT_UPDATE @LOAN = @M_LOAN, @EISTREAM_ID = @EISTREAM_ID, @STATUS = @STATUS, @SOURCE = @SOURCE, @INT_CALL = 1
end
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END
END
ELSE
BEGIN
IF @RC <> 1
BEGIN
SELECT @ERR = 5
select @ERR
RAISERROR (50005,16,2)
RETURN @ERR
END
END
END
IF @INT_CALL = 0 select @err
return @ERR

GO

CREATE procedure SP_DOCUMENT_UPDATE
@eiStream_ID varchar(15) ='-1',
@DocType varchar(5) ='-1',
@Loan varchar(10) ='-1',
@Status varchar(4) ='-1',
@L_result_text varchar(250) ='-1',
@L_P_I numeric(15,2) =-1,
@L_note_review_date datetime = '12/31/2999',
@L_middle_1 varchar(1) ='-',
@L_middle_2 varchar(1) ='-',
@L_middle_3 varchar(1) ='-',
@L_middle_4 varchar(1) ='-',
@O_note_loan varchar(10) ='-1',
@O_note_closing_date datetime = '12/31/2999',
@O_note_city varchar(30) ='-1',
@O_note_street_address varchar(50) ='-1',
@O_note_state varchar(2) ='-1',
@O_note_zip varchar(10) ='-1',
@O_note_loan_amt numeric(15,2) =-1,
@O_note_lender varchar(50) ='-1',
@O_note_interest_rate numeric(7,4) =-1,
...
@L_SECURE_FLAG smallint = -1,
@source varchar(4) ='-1',
@NO_HISTORY varchar(10) ='history',
@INT_CALL tinyint =0,
@err smallint = 0 output
AS
select @source = rtrim(@source)
select @status = rtrim(@status)
IF @LOAN is null OR @LOAN = '-1' OR @STATUS = '-1' OR @STATUS is null OR @status not in
(select status from STATUS_CODES) OR @eiStream_id is null or @eiStream_ID = '-1'
BEGIN
PRINT 'You must supply a valid LOAN, eiStream_ID, and STATUS code'
SELECT @ERR = 4
SELECT @ERR
RAISERROR (50004,16,2)
RETURN @ERR
END
exec SP_SOURCE_CHECK @SOURCE, @ERR output
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END
IF (SELECT COUNT(*) FROM DOCUMENT WHeRE EISTREAM_ID = @EISTREAM_ID) < 1
begin
exec @err = SP_LOAN_UPDATE @M_LOAN = @LOAN, @STATUS = @STATUS, @SOURCE = @SOURCE, @INT_CALL = 1
Print 'No Document Found, Updating Loan Record'
end
Update DOCUMENT set
DocType = CASE
when @DocType= '-1' then DocType
else @DocType
end,
Status = CASE
when @Status= '-1' then Status
else @Status
end,
Time_stamp = getdate(),
L_result_text = CASE
when @L_result_text= '-1' OR @source<> 'L' then L_result_text
else @L_result_text
end,
L_P_I = CASE
when @L_P_I= -1 OR @source<> 'L' then L_P_I
else @L_P_I
end,
L_note_review_date = CASE
when @L_note_review_date= '12/31/2999' OR @source<> 'L' then L_note_review_date
else @L_note_review_date
end,
L_middle_1 = CASE
when @L_middle_1= '-' OR @source<> 'L' then L_middle_1
else @L_middle_1
end,
L_middle_2 = CASE
when @L_middle_2= '-' OR @source<> 'L' then L_middle_2
else @L_middle_2
end,
L_middle_3 = CASE
when @L_middle_3= '-' OR @source<> 'L' then L_middle_3
else @L_middle_3
end,
L_middle_4 = CASE
when @L_middle_4= '-' OR @source<> 'L' then L_middle_4
else @L_middle_4
end,
O_note_loan = CASE
when @O_note_loan= '-1' OR @source<> 'O' then O_note_loan
else @O_note_loan
end,
O_note_closing_date = CASE
when @O_note_closing_date= '12/31/2999' OR @source<> 'O' then O_note_closing_date
else @O_note_closing_date
end,
O_note_city = CASE
when @O_note_city= '-1' OR @source<> 'O' then O_note_city
else @O_note_city
end,
O_note_street_address = CASE
when @O_note_street_address = '-1' OR @source<> 'O' then O_note_street_address
else @O_note_street_address
end,
O_note_state = CASE
when @O_note_state= '-1' OR @source<> 'O' then O_note_state
else @O_note_state
end,
O_note_zip = CASE
when @O_note_zip= '-1' OR @source<> 'O' then O_note_zip
else @O_note_zip
end,
O_note_loan_amt = CASE
when @O_note_loan_amt= -1 OR @source<> 'O' then O_note_loan_amt
else @O_note_loan_amt end,
O_note_lender = CASE
when @O_note_lender= '-1' OR @source<> 'O' then O_note_lender
else @O_note_lender
end,
O_note_interest_rate = CASE
when @O_note_interest_rate= -1 OR @source<> 'O' then O_note_interest_rate
else @O_note_interest_rate
end,
...
L_SECURE_FLAG = CASE
when @L_SECURE_FLAG= -1 OR @source <> 'L' then L_SECURE_FLAG
else @L_SECURE_FLAG
end
where @eistream_id = eistream_id and @loan = loan
IF @@ROWCOUNT <> 1
BEGIN
SELECT @ERR = 5
select @err
RAISERROR (50005,16,2)
RETURN @ERR
END
IF @NO_HISTORY <> 'NO_HISTORY'
BEGIN
exec SP_HISTORY_APPEND @loan, @eistream_id, @status, @source, @err output
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END
IF @INT_CALL = 0
BEGIN
exec @err = SP_LOAN_UPDATE @M_LOAN = @LOAN, @STATUS = @STATUS, @SOURCE = @SOURCE, @NO_HISTORY ='NO_HISTORY', @INT_CALL = 1
END
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END
END
ELSE
BEGIN
IF @NO_HISTORY = 'NO_HISTORY'
BEGIN
IF @INT_CALL = 0
BEGIN
exec @err = SP_LOAN_UPDATE @M_LOAN = @LOAN, @STATUS = @STATUS, @SOURCE = @SOURCE, @INT_CALL = 1
END
IF @ERR <> 3
BEGIN
SELECT @ERR
RETURN @ERR
END
END
END
IF @INT_CALL = 0 SELECT @ERR
RETURN @ERR

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources