February 1, 2016 at 2:27 am
Hi,
I have a common SP which generates a transaction no based on a given table which has Prefix, Suffix, no of char, Effective From, Effective TO, Last Generated no
This sp is getting called at same time from 8 different user's. For about 2 people same transaction no is getting generated. Is there any way to avoid duplication by using Locks?
Note: No is getting generated based on Last Gen Column
February 1, 2016 at 2:40 am
Yes, but it's going to be hard to give specifics without seeing the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2016 at 5:26 am
This is the SP which i am using.... I added with rowlock to check but duplicate no is generating for about 5 people when 18 users access simultaneously. The table is dnm_ncdet_numclsdets
CREATE procedure test_SP
@ctxt_language udd_ctxt_language,
@ctxt_ouinstance udd_ctxt_ouinstance,
@ctxt_service udd_ctxt_service,
@ctxt_user udd_ctxt_user,
@call_compname udd_component,
@trantype_code udd_typecode,
@notypeno udd_notypeno,
@trandate udd_date,
@transaction_no udd_transactionnumber output,
@m_errorid udd_number output,
@execflag udd_execflag output
as
begin
set nocount on
declare @suffix udd_reference_det1 , @prefix udd_reference_det1
declare @startno udd_paraminteger, @lastno udd_paraminteger
declare @endno udd_paraminteger, @nolength udd_paraminteger
declare @cnum udd_reference_det1
declare @functionname udd_functionname
declare @transaction udd_transaction
declare @trantype udd_transactiontype
declare @pfix udd_reference_det1
declare @sfix udd_reference_det1
declare @lo_tmpudd_loid ,
@bu_tmpudd_buid ,
@return_val int,
@tran_dateudd_date ,
-- @meta_trantype_tmp udd_typecode-- Modified for 5H101-Pro_rpt_PRO_00151
@meta_trantype_tmp udd_transactiontype
select @tran_date = dbo.pro_getdate(@ctxt_ouinstance, getutcdate())
select @notypeno = ltrim(rtrim(upper(@notypeno)))
exec scm_get_emod_details @ctxt_ouinstance, @tran_date,
@lo_tmp out, @bu_tmp out, @return_val out
if (@return_val <> 0)
return
select @functionname = meta_function ,
@transaction = meta_transaction ,
@trantype = meta_trantype
from dnm_meta_funtran_metadata (nolock)
where meta_trantype_code = @trantype_code
select @m_errorid = 0
select @execflag = 0
if exists (select 1 from dnm_mapnt_mapnotypeno (nolock), dnm_numcls_numclshdr (nolock)
where mapnt_notypeno = @notypeno
and mapnt_function = @functionname
and mapnt_transaction = @transaction
and mapnt_trantype = @trantype
and mapnt_lo = numcls_lo
and mapnt_ou = @ctxt_ouinstance
and mapnt_map ='Y'
and mapnt_notypeno = numcls_notypeno
and numcls_lo = @lo_tmp )
begin
select @ctxt_ouinstance = @ctxt_ouinstance
end
else
begin
select @m_errorid = 2410003
select @execflag = 1
return
end
select @prefix = ltrim(rtrim(isnull(b.ncdet_prefix,''))) ,
@suffix = ltrim(rtrim(isnull(b.ncdet_suffix,''))) ,
@startno = isnull(b.ncdet_start_no, 0),
@endno = isnull(b.ncdet_endno, 0) ,
@lastno = isnull(b.ncdet_lastgen_no, 0) ,
@nolength = isnull(a.numcls_autono_length, 0)
from dnm_numcls_numclshdr a with (rowlock), dnm_ncdet_numclsdets b with (rowlock)
where a.numcls_notypeno = b.ncdet_notypeno
and a.numcls_lotserno = 0
and b.ncdet_notypeno = @notypeno
and @trandate between b.ncdet_eff_fromdate and b.ncdet_eff_todate
and a.numcls_status = 'AC'
and a.numcls_lo = b.ncdet_lo
and a.numcls_lo = @lo_tmp
if isnull(@nolength,0) = 0
begin
select @m_errorid = 2410002
select @execflag = 1
return
end
if @endno > 0
begin
if @lastno >= 999999999
begin
select @m_errorid = 2410004
select @execflag = 1
return
end
if @lastno >= @endno
begin
select @m_errorid = 2410004
select @execflag = 1
return
end
end
if @endno = 0
begin
if @nolength >9
select @nolength = 9
if @lastno >= power(10, @nolength)-1
begin
select @m_errorid = 2410004
select @execflag = 1
return
end
end
if @lastno = 0 or @lastno is null
begin
select @lastno = @startno - 1
end
select @cnum = ltrim(rtrim(convert(char, @lastno+1 )))
if ltrim(rtrim(@prefix)) = '##'
select @pfix = ''
else
select @pfix = @prefix
if ltrim(rtrim(@suffix)) = '##'
select @sfix = ''
else
select @sfix = @suffix
select @transaction_no = ltrim(rtrim(@pfix)) +
replicate ( '0' ,@nolength - len(@cnum )) +ltrim(rtrim(@cnum)) +
ltrim(rtrim(@sfix))
update dnm_ncdet_numclsdets with (rowlock)
setncdet_lastgen_no = @lastno + 1 ,
ncdet_last_gendate = @tran_date
where ncdet_lo= @lo_tmp
andncdet_notypeno= @notypeno
andncdet_prefix= @prefix
andncdet_suffix= @suffix
set nocount off
end
February 3, 2016 at 5:28 am
Rowlock's not going to help at all. You at the very least need a transaction around the whole thing, and a updlock hint on the query which fetches the current max value from the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2016 at 5:33 am
Can you suggest what to change? Or another lock that can be used?
February 3, 2016 at 5:40 am
Yes, I just did.
You at the very least need a transaction around the whole thing, and a updlock hint on the query which fetches the current max value from the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply