By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,262 Members | 1,128 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,262 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"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
Jul 20 '05 #2

P: n/a

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


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.