SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Prevent concurrent use in stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ynevet
Starting Member

Israel
10 Posts

Posted - 03/04/2010 :  05:59:10  Show Profile  Reply with Quote
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

India
22 Posts

Posted - 03/04/2010 :  07:41:57  Show Profile  Reply with Quote
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...)
Go to Top of Page

ynevet
Starting Member

Israel
10 Posts

Posted - 03/04/2010 :  08:11:19  Show Profile  Reply with Quote
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

Edited by - ynevet on 03/04/2010 08:44:27
Go to Top of Page

Ifor
Aged Yak Warrior

548 Posts

Posted - 03/04/2010 :  08:38:48  Show Profile  Reply with Quote
Look at application locks. (sp_getapplock and sp_releaseapplock in BOL.)
Go to Top of Page

ynevet
Starting Member

Israel
10 Posts

Posted - 03/04/2010 :  08:47:45  Show Profile  Reply with Quote
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

Edited by - ynevet on 03/04/2010 08:50:23
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 03/04/2010 :  23:20:44  Show Profile  Reply with Quote


--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
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 03/04/2010 :  23:27:07  Show Profile  Reply with Quote
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.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/05/2010 :  10:01:25  Show Profile  Reply with Quote
I think OP was asking about way to lock execution of SP not tables used

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000