hi, I have a problem on building an application that can be accessed by
multiple clients concurrently. I have a table called [sub_bg_id_allocation],
it has 2 int columns: bg_id and sub_bg_id. The business rule states that
sub_bg_id should be unique within a bg_id, e.g.
bg_id subbg_id
--
1 110
1 111
1 112
2 110
2 111
... ...
the subbg_id is started by 110 and incremented by 1 within a bg_id, actually
this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. The
newly allocated sub_bg_id will be used for inserting a 'real' data record in
another table called [pro_child_subbg_info]. (don't ask me why I need the
[sub_bg_id_allocation] table, it's already there and used by existing system
)
Now I write a stored procedure like this:
CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
@.bg_id int,
@.subbg_id int output
@....
AS
BEGIN
BEGIN TRAN
-- calculate subbg_id
declare @.last_subbg_id int, @.subbg_id int
select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgid
= @.bg_id
if @.last_subbg_id >= 110
set @.subbg_id = @.last_subbg_id + 1
else
set @.subbg_id = 110
-- subbg_id allocation
insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- subbg info.
insert pro_child_subbg_info (
bg_id, sub_bg_id, ...) values (
@.bg_id, @.subbg_id, @....)
-- check error immediately
if @.@.error <> 0
goto ErrorHandler
-- end successfully
COMMIT TRAN
RETURN 0 -- ***** 0 means ok *****
ErrorHandler:
ROLLBACK TRAN
RETURN -1 -- ***** -1 means fail *****
Is there any problem in a concurrent client scenario? I wonder about the
MAX() function works properly in concurrent case, should I lock the
[sub_bg_id_allocation] table within this stored procedure? How can I lock it
?
Thanks a lot!Hi
You may want to add a hint such as UPDLOCK to the select statement.
http://msdn.microsoft.com/library/d...br />
4ox9.asp
This will potentially reduce concurrency such as
http://support.microsoft.com/defaul...kb;en-us;179362
so you may want to look at using an identity instead.
John
"nonno" wrote:
> hi, I have a problem on building an application that can be accessed by
> multiple clients concurrently. I have a table called [sub_bg_id_allocation
],
> it has 2 int columns: bg_id and sub_bg_id. The business rule states that
> sub_bg_id should be unique within a bg_id, e.g.
> bg_id subbg_id
> --
> 1 110
> 1 111
> 1 112
> 2 110
> 2 111
> ... ...
> the subbg_id is started by 110 and incremented by 1 within a bg_id, actual
ly
> this [sub_bg_id_allocation] table is used for calculating new sub_bg_id. T
he
> newly allocated sub_bg_id will be used for inserting a 'real' data record
in
> another table called [pro_child_subbg_info]. (don't ask me why I need the
> [sub_bg_id_allocation] table, it's already there and used by existing syst
em)
> Now I write a stored procedure like this:
> CREATE PROCEDURE [dbo].[LiteCreateDDISubbg]
> @.bg_id int,
> @.subbg_id int output
> @....
> AS
> BEGIN
> BEGIN TRAN
> -- calculate subbg_id
> declare @.last_subbg_id int, @.subbg_id int
> select @.last_subbg_id = max(sub_bg_id) from sub_bg_id_allocation where bgi
d
> = @.bg_id
> if @.last_subbg_id >= 110
> set @.subbg_id = @.last_subbg_id + 1
> else
> set @.subbg_id = 110
> -- subbg_id allocation
> insert sub_bg_id_allocation (bg_id, subbg_id) values (@.bg_id, @.subbg_id)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- subbg info.
> insert pro_child_subbg_info (
> bg_id, sub_bg_id, ...) values (
> @.bg_id, @.subbg_id, @....)
> -- check error immediately
> if @.@.error <> 0
> goto ErrorHandler
> -- end successfully
> COMMIT TRAN
> RETURN 0 -- ***** 0 means ok *****
> ErrorHandler:
> ROLLBACK TRAN
> RETURN -1 -- ***** -1 means fail *****
> Is there any problem in a concurrent client scenario? I wonder about the
> MAX() function works properly in concurrent case, should I lock the
> [sub_bg_id_allocation] table within this stored procedure? How can I lock
it?
> Thanks a lot!|||Thanks John. How can I use identity in this case?
"John Bell" wrote:
> Hi
> You may want to add a hint such as UPDLOCK to the select statement.
> http://msdn.microsoft.com/library/d... />
z_4ox9.asp
> This will potentially reduce concurrency such as
> http://support.microsoft.com/defaul...kb;en-us;179362
> so you may want to look at using an identity instead.
>
> John
>
> "nonno" wrote:
>|||Hi
That would be a change in functionaility in that the subbg_id will be
continually increasing. Does the subbg_id really need to do what you are
trying to build?
When needed you can still rank the entries by using something
select a.bg_id, subbg_id as original_subbg_id, (select count(*) from
sub_bg_id_allocation b where a.subbg_id < b.subbg_id ) + 110 as
ranked_subbg_id FROM sub_bg_id_allocation a
John
"nonno" wrote:
> Thanks John. How can I use identity in this case?
> "John Bell" wrote:
>
