how to lock the store procedure and allow one process to acces it at a time

[Jin]

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

[Simon Hayes]

"Jin" <te****@hotmail.com> wrote in message
news:82**************************@posting.google.c om...

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

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

[Joe Weinstein]

Sure. You could do it transactionally at serializable isolation level...
Joe

Jin wrote:

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