| re: how to lock the store procedure and allow one process to acces it at a time
"Jin" <texlqj@hotmail.com> wrote in message
news:82b49cd5.0401131104.7c12efc5@posting.google.c om...[color=blue]
> Hello:
>
> I run one process that calls the following the store procedure and
> works fine.
>
> create PROCEDURE sp_GetHostSequenceNum
> AS
> BEGIN
>
> SELECT int_parameter_dbf + 1
> FROM system_parameter_dbt
> WHERE parameter_name_dbf = 'seqNum'
>
> UPDATE system_parameter_dbt
> SET int_parameter_dbf = int_parameter_dbf + 1
> WHERE parameter_name_dbf = 'seqNum'
>
> END
> GO
>
>
> If I run two processes that call the above store procedure, I might
> occasionally get the dirty data of int_parameter_dbt. I guess that is
> caused by two processes accessing to the same resource simultaneously.
> Is there any way to lock the store procedure call from MSSQL Server
> and allow only one process to access it at a time?
>
> Thanks for help.
>
> Best Jin[/color]
Here is one possible approach, using an UPDATE syntax specific to MSSQL:
create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN
declare @val int
UPDATE system_parameter_dbt
SET @val = int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'
select @val
END
GO
Alternatively, you can use a locking hint:
create PROCEDURE sp_GetHostSequenceNum
AS
BEGIN
begin tran
SELECT int_parameter_dbf + 1
FROM system_parameter_dbt with (UPDLOCK)
WHERE parameter_name_dbf = 'seqNum'
UPDATE system_parameter_dbt
SET int_parameter_dbf = int_parameter_dbf + 1
WHERE parameter_name_dbf = 'seqNum'
commit
END
GO
Simon
| 
