Prevent concurrent use in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Prevent concurrent use in stored procedure

Author

Topic

ynevet
Starting Member

10 Posts

Posted - 2010-03-04 : 05:59:10

Hello folks,

There is anyway to lock my stored procedure from being use by more than 1 user at the same time?

Thanks,

Yair

Yair

bhuvnesh.dogra
Starting Member

22 Posts

Posted - 2010-03-04 : 07:41:57

What do you mean by SP used by more than one user at a time?
as far i know, every user will execute it ( directly/indirectly) in individual(separate)transaction.

---Bhuvnesh-----
While 1=1(learning Sql...)

ynevet
Starting Member

10 Posts

Posted - 2010-03-04 : 08:11:19

I have a sensitive SP which I don't want users to execute at the same time,
i.e, only when my SP transaction is commited, then the next user can use it - some sort of queue.

Yair

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-03-04 : 08:38:48

Look at application locks. (sp_getapplock and sp_releaseapplock in BOL.)

ynevet
Starting Member

10 Posts

Posted - 2010-03-04 : 08:47:45

The following is my SP, how can I integrate sp_getapplock and sp_releaseapplock inside?

CREATE PROCEDURE [dbo].[InsertMissionDemands]
@MissionSeq int,
@Demand nvarchar(50)
AS
BEGIN
DECLARE @NewSeq int
DECLARE @TotalDemandsForMission int

INSERT MISSION_DEMANDS(MissionSeq, Demand)
VALUES(@MissionSeq, @Demand)
SET @NewSeq = SCOPE_IDENTITY()

INSERT MISSION_DEMANDS_HISTORY(MissionSeq, Demand)
VALUES(@MissionSeq, @Demand)

SET @TotalDemandsForMission = (SELECT COUNT(*) FROM MISSION_DEMANDS_HISTORY WHERE MissionSeq = @MissionSeq)

UPDATE MISSION_DEMANDS SET DemandNumber = @TotalDemandsForMission
WHERE MissionSeq = @MissionSeq AND Seq = @NewSeq
END

GO

Yair

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-04 : 23:20:44

--Try it.. I have not carried out any test but i feel that it should work

CREATE PROCEDURE [dbo].[InsertMissionDemands]
@MissionSeq int,
@Demand nvarchar(50)
AS
BEGIN
DECLARE @NewSeq int
DECLARE @TotalDemandsForMission int
Declare @PkValue int

Begin tran

--Read from the MISSION_DEMANDS using exclusive lock.. This will not allow other users to read or insert record.
Select @PkValue = 0 from MISSION_DEMANDS with( TABLOCKX)

INSERT MISSION_DEMANDS(MissionSeq, Demand)
VALUES(@MissionSeq, @Demand)
SET @NewSeq = SCOPE_IDENTITY()

INSERT MISSION_DEMANDS_HISTORY(MissionSeq, Demand)
VALUES(@MissionSeq, @Demand)

SET @TotalDemandsForMission = (SELECT COUNT(*) FROM MISSION_DEMANDS_HISTORY WHERE MissionSeq = @MissionSeq)

UPDATE MISSION_DEMANDS SET DemandNumber = @TotalDemandsForMission
WHERE MissionSeq = @MissionSeq AND Seq = @NewSeq
END

Commit tran

--Remember to do error handling and rollback trans if any error occurs.

GO

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-03-04 : 23:27:07

TABLOCKX statement locks the table. This command should be used with caution. I have suggeseted this method as you are saying that it is very sensitive and critical SP.

I would like to know experts opinion. Using of TABLOCKS in this situation is preferred or is there any other way to deal with this type of situation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-05 : 10:01:25

I think OP was asking about way to lock execution of SP not tables used

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources