March 21, 2006 at 6:46 am
When we run the following stored procedure p_lpi_refund_import_load, the cursor refund_cursor cursor locks the table LENDER_PLACED_INS_LOAN_REFUND_IMPORT in a multi user enviroment though it uses user id which is unique for different users.
Following is the Stored Procedure
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.p_lpi_refund_import_load Script Date: 2/21/2006 4:45:21 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_lpi_refund_import_load]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_lpi_refund_import_load]
GO
CREATE procedure dbo.p_lpi_refund_import_load
/**************************************************************************************/
/* Method Name : p_lpi_refund_import_load */
/* Description : Validates the Refunds in the import table. */
/* Creator : Brian E. Wade */
/* Create Date : 2/22/06 */
/**************************************************************************************/
/* History : Original */
/**************************************************************************************/
@user_id int
as
begin
declare @process_name varchar(60),
@number int,
@message varchar(255),
@message_end varchar(60),
@error int,
@validation_error_count int,
@rowcount int,
@trancount int,
@row_id int,
@loan_number varchar(13),
@refund_amt varchar(10),
@refund_dt varchar(10),
@certificate_number varchar(20),
@proc_dt datetime,
@validation varchar(255)
set nocount on
/* Initalize some variables
------------------------ */
select @process_name = 'p_lpi_refund_import_load',
@message_end = '',
@number = 0
declare refund_cursor cursor for
select row_id,loan_number,refund_amt
from dbo.LENDER_PLACED_INS_LOAN_REFUND_IMPORT
where created_by_id = @user_id
open refund_cursor
fetch next from refund_cursor into @row_id,@loan_number,@refund_amt
/*******************************************************************************/
/* Perform validation on the imported refunds */
/* Validations: */
/* 1. Loan must have Lender Placed Insurance */
/* 2. The loan can only have one certificate */
/*******************************************************************************/
begin transaction
while @@fetch_status = 0
begin
/* Reset the values of variables for the next Loan
----------------------------------------------- */
select @refund_dt = null,
@certificate_number = null,
@validation = ''
/* Validation for Loan Number
-------------------------- */
if @loan_number is null or datalength(@loan_number) = 0
begin
set @validation = 'Loan Number cannot be blank.'
goto update_loan
end
/* Determine if there is more than one Certificate Number
------------------------------------------------------ */
select @certificate_number = certificate_number
from dbo.LENDER_PLACED_INS_LOAN
where active_flg = 1 and
loan_number = @loan_number
set @rowcount = @@rowcount
select @certificate_number = case
when @rowcount = 1 then @certificate_number
else null
end,
@validation = @validation +
(case
when @rowcount = 0 then ' Loan has no Lender Placed Insurance.'
when @rowcount > 1 then ' Loan has more that one active instance of Lender Placed Insurance.'
else ''
end)
/* Validation for Refund Amt
------------------------- */
if @refund_amt is null or datalength(@refund_amt) = 0
set @validation = @validation + 'Refund Amount cannot be blank.'
else
select @validation = case dbo.f_isNumeric(@refund_amt)
when 0 then 'Refund Amt is not a valid number.'
when 1 then @validation
end
/* Get the Next Business date for the Refund Date
---------------------------------------------- */
select @refund_dt = convert(varchar(10),dbo.f_get_nth_bus_dt(getdate(),1),101)
if @refund_dt is null or datalength(@refund_dt) = 0
set @validation = @validation + ' Cound not determine the Next Business Date.'
/* Update the table with the Refund Date, Certificate Number, and
Error Message, if any.
-------------------------------------------------------------- */
update_loan:
update LENDER_PLACED_INS_LOAN_REFUND_IMPORT
set refund_dt = @refund_dt,
certificate_number = @certificate_number,
error_msg = @validation
where row_id = @row_id
set @error = @@error
if @error <> 0
goto cursor_end
fetch next from refund_cursor into @row_id,@loan_number,@refund_amt
end
cursor_end:
close refund_cursor
deallocate refund_cursor
if @error = 0
commit transaction
else
begin
rollback transaction
select @number = 1, @message = @process_name + ': Unable to validate Refund information'
end
proc_end:
/* Determine the number of validation errors
----------------------------------------- */
select @validation_error_count = sum(case when isnull(datalength(error_msg),0) = 0 then 0 else 1 end)
from LENDER_PLACED_INS_LOAN_REFUND_IMPORT
where created_by_id = @user_id
select @number as error_number,
@message + @message_end as error_message,
@validation_error_count as validation_error_count
return @number
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_lpi_refund_import_load]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
GRANT EXECUTE ON [dbo].[p_lpi_refund_import_load] TO [IRIS_User_Role]
Print 'Procedure [dbo].[p_lpi_refund_import_load] Created'
END
GO
March 21, 2006 at 1:18 pm
yup - you have an implicit transaction - always a strong chnace of locking.
Do you mean you are suffering from blocking? If so you need to analyse the locks being held by the transaction(s) . I can't comment about the code as without the data structures and all the rest too difficult - but if there are not sufficient indexes for updates then you may suffer table/page locking which causes blocking. I have found sql server taking page locks rather than row locks for updates which isn't always the best solution.
As I say you need to analyse the locks and work backwards from there. Check out Inside SQL Server by Kalen Delaney, Microsoft technet ( some good stuff available ) and the administrators companion from ms press.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply