| Author |
 Topic  |
|
|
ynevet
Starting Member
Israel
10 Posts |
 Posted - 03/04/2010 : 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
India
22 Posts |
Posted - 03/04/2010 : 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
Israel
10 Posts |
Posted - 03/04/2010 : 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 |
Edited by - ynevet on 03/04/2010 08:44:27 |
|
|
|
Ifor
Constraint Violating Yak Guru
475 Posts |
Posted - 03/04/2010 : 08:38:48
|
Look at application locks. (sp_getapplock and sp_releaseapplock in BOL.)
|
|
|
|
ynevet
Starting Member
Israel
10 Posts |
Posted - 03/04/2010 : 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 |
Edited by - ynevet on 03/04/2010 08:50:23 |
|
|
|
pk_bohra
Flowing Fount of Yak Knowledge
India
1182 Posts |
Posted - 03/04/2010 : 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
Flowing Fount of Yak Knowledge
India
1182 Posts |
Posted - 03/04/2010 : 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
India
47023 Posts |
Posted - 03/05/2010 : 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/
|
|
|
| |
Topic |
|
Prevent concurrent use in stored procedure
