How do I limit a SQL stored procedure to be run by one person

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

I also tried finding the procedure with sys.dm_exec_requests and blocking the procedure, while this does work, i think it's not optimal because on some servers I don't have the permissions to run sys.dm_exec_sql_text(sql_handle).

What is the best way for me to do this?

edited 59 mins ago

Michael Green

13.5k82958

asked 3 hours ago

twoheadedmona

New contributor

Can you take one step back, and provide some more info on what the procedure is doing, and why you want to avoid multiple people running it at the same time? There may be a coding technique that eliminates this requirement, or some sort of queuing you could implement to handle things.
â€“Â AMtwo
1 hour ago

add a commentÂ |Â

up vote
1
down vote

favorite

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

What is the best way for me to do this?

edited 59 mins ago

Michael Green

13.5k82958

asked 3 hours ago

twoheadedmona

New contributor

Can you take one step back, and provide some more info on what the procedure is doing, and why you want to avoid multiple people running it at the same time? There may be a coding technique that eliminates this requirement, or some sort of queuing you could implement to handle things.
â€“Â AMtwo
1 hour ago

add a commentÂ |Â

up vote
1
down vote

favorite

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

What is the best way for me to do this?

edited 59 mins ago

Michael Green

13.5k82958

asked 3 hours ago

twoheadedmona

New contributor

I have a stored procedure that basically selects values from one table and inserts them to another, a sort of archiving. I want to avoid multiple people from doing that at the same time.

While this procedure is running, I don't want anyone else to be able to start it, however I don't want serialization, the other person to run the procedure after I am done with it.

What I want is for the other person trying to start it to get an error, while I am running the procedure.

I've tried with using sp_getapplock, however I can't manage to completely stop the person from running the procedure.

What is the best way for me to do this?

sql-server sql sql-server-2012 t-sql stored-procedures

edited 59 mins ago

Michael Green

13.5k82958

asked 3 hours ago

twoheadedmona

New contributor

edited 59 mins ago

Michael Green

13.5k82958

asked 3 hours ago

twoheadedmona

New contributor

edited 59 mins ago

Michael Green

13.5k82958

edited 59 mins ago

Michael Green

13.5k82958

edited 59 mins ago

Michael Green

13.5k82958

asked 3 hours ago

twoheadedmona

New contributor

asked 3 hours ago

twoheadedmona

asked 3 hours ago

twoheadedmona

New contributor

twoheadedmona is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

Can you take one step back, and provide some more info on what the procedure is doing, and why you want to avoid multiple people running it at the same time? There may be a coding technique that eliminates this requirement, or some sort of queuing you could implement to handle things.
â€“Â AMtwo
1 hour ago

add a commentÂ |Â

Can you take one step back, and provide some more info on what the procedure is doing, and why you want to avoid multiple people running it at the same time? There may be a coding technique that eliminates this requirement, or some sort of queuing you could implement to handle things.
â€“Â AMtwo
1 hour ago

Can you take one step back, and provide some more info on what the procedure is doing, and why you want to avoid multiple people running it at the same time? There may be a coding technique that eliminates this requirement, or some sort of queuing you could implement to handle things.
â€“Â AMtwo
1 hour ago

add a commentÂ |Â

3 Answers
3

active

oldest

votes

up vote
3
down vote

Use sp_getapplock in the beginning of the proc, and set a lock timeout to a very low value. This way you get an error when you are blocked.

answered 3 hours ago

Tibor Karaszi

9915

add a commentÂ |Â

up vote
2
down vote

To add to @Tibor-Karazi's answer, setting a lock timeout doesn't actually produce an error (I've submitted a PR against the docs). sp_getapplock just returns -1, so you have to check the return value. So like this:

create or alter procedure there_can_be_only_one 
as
begin
begin transaction

 declare @rv int
 exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
 if @rv < 0
 begin
 throw 50001, 'There is already an instance of this procedure running.', 10
 end

 --do stuff
 waitfor delay '00:00:20'


commit transaction
end

edited 21 mins ago

answered 31 mins ago

David Browne - Microsoft

8,844723

add a commentÂ |Â

up vote
1
down vote

Another option is to build a table to control access to the procedure. the example below shows a possible table as well as a procedure that could use it.

CREATE TABLE dbo.ProcedureLock
 (
 ProcedureLockID INT NOT NULL IDENTITY(1,1)
 , ProcedureName SYSNAME NOT NULL
 , IsLocked BIT NOT NULL CONSTRAINT DF_ProcedureLock_IsLocked DEFAULT (0)
 , UserSPID INT NULL
 , DateLockTaken DATETIME2(7) NULL
 , DateLockExpires DATETIME2(7) NULL
 , CONSTRAINT PK_ProcedureLock PRIMARY KEY CLUSTERED (ProcedureLockID)
 )

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_ProcedureLock_ProcedureName
 ON dbo.ProcedureLock (ProcedureName)

INSERT INTO dbo.ProcedureLock
 (ProcedureName, IsLocked)
VALUES ('dbo.DoSomeWork', 0)

GO

CREATE PROCEDURE dbo.DoSomeWork
AS
BEGIN

 /** Take Lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 1
 , UserSPID = @@SPID
 , DateLockTaken = SYSDATETIME()
 , DateLockExpires = DATEADD(MINUTE, 10, SYSDATETIME())
 WHERE ProcedureName = 'dbo.DoSomeWork'
 AND (IsLocked = 0
 OR (IsLocked = 1 AND DateLockExpires < SYSDATETIME())
 )

 IF COALESCE(@@ROWCOUNT, 0) = 0
 BEGIN
 ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;
 END

 /** DO WHATEVER NEEDS TO BE DONE */

 /** Release the lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 0
 , UserSPID = NULL
 , DateLockTaken = NULL
 , DateLockExpires = NULL
 WHERE ProcedureName = 'dbo.DoSomeWork'

END

answered 23 mins ago

Jonathan Fite

3,694818

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

twoheadedmona is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f219337%2fhow-do-i-limit-a-sql-stored-procedure-to-be-run-by-one-person%23new-answer', 'question_page');

);

Post as a guest

Name

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
3
down vote

Use sp_getapplock in the beginning of the proc, and set a lock timeout to a very low value. This way you get an error when you are blocked.

answered 3 hours ago

Tibor Karaszi

9915

add a commentÂ |Â

up vote
3
down vote

Use sp_getapplock in the beginning of the proc, and set a lock timeout to a very low value. This way you get an error when you are blocked.

answered 3 hours ago

Tibor Karaszi

9915

add a commentÂ |Â

up vote
3
down vote

Use sp_getapplock in the beginning of the proc, and set a lock timeout to a very low value. This way you get an error when you are blocked.

answered 3 hours ago

Tibor Karaszi

9915

Use sp_getapplock in the beginning of the proc, and set a lock timeout to a very low value. This way you get an error when you are blocked.

answered 3 hours ago

Tibor Karaszi

9915

answered 3 hours ago

Tibor Karaszi

9915

answered 3 hours ago

Tibor Karaszi

9915

answered 3 hours ago

Tibor Karaszi

9915

add a commentÂ |Â

up vote
2
down vote

create or alter procedure there_can_be_only_one 
as
begin
begin transaction

 declare @rv int
 exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
 if @rv < 0
 begin
 throw 50001, 'There is already an instance of this procedure running.', 10
 end

 --do stuff
 waitfor delay '00:00:20'


commit transaction
end

edited 21 mins ago

answered 31 mins ago

David Browne - Microsoft

8,844723

add a commentÂ |Â

up vote
2
down vote

create or alter procedure there_can_be_only_one 
as
begin
begin transaction

 declare @rv int
 exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
 if @rv < 0
 begin
 throw 50001, 'There is already an instance of this procedure running.', 10
 end

 --do stuff
 waitfor delay '00:00:20'


commit transaction
end

edited 21 mins ago

answered 31 mins ago

David Browne - Microsoft

8,844723

add a commentÂ |Â

up vote
2
down vote

create or alter procedure there_can_be_only_one 
as
begin
begin transaction

 declare @rv int
 exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
 if @rv < 0
 begin
 throw 50001, 'There is already an instance of this procedure running.', 10
 end

 --do stuff
 waitfor delay '00:00:20'


commit transaction
end

edited 21 mins ago

answered 31 mins ago

David Browne - Microsoft

8,844723

create or alter procedure there_can_be_only_one 
as
begin
begin transaction

 declare @rv int
 exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
 if @rv < 0
 begin
 throw 50001, 'There is already an instance of this procedure running.', 10
 end

 --do stuff
 waitfor delay '00:00:20'


commit transaction
end

edited 21 mins ago

answered 31 mins ago

David Browne - Microsoft

8,844723

edited 21 mins ago

answered 31 mins ago

David Browne - Microsoft

8,844723

answered 31 mins ago

David Browne - Microsoft

8,844723

answered 31 mins ago

David Browne - Microsoft

8,844723

add a commentÂ |Â

up vote
1
down vote

Another option is to build a table to control access to the procedure. the example below shows a possible table as well as a procedure that could use it.

CREATE TABLE dbo.ProcedureLock
 (
 ProcedureLockID INT NOT NULL IDENTITY(1,1)
 , ProcedureName SYSNAME NOT NULL
 , IsLocked BIT NOT NULL CONSTRAINT DF_ProcedureLock_IsLocked DEFAULT (0)
 , UserSPID INT NULL
 , DateLockTaken DATETIME2(7) NULL
 , DateLockExpires DATETIME2(7) NULL
 , CONSTRAINT PK_ProcedureLock PRIMARY KEY CLUSTERED (ProcedureLockID)
 )

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_ProcedureLock_ProcedureName
 ON dbo.ProcedureLock (ProcedureName)

INSERT INTO dbo.ProcedureLock
 (ProcedureName, IsLocked)
VALUES ('dbo.DoSomeWork', 0)

GO

CREATE PROCEDURE dbo.DoSomeWork
AS
BEGIN

 /** Take Lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 1
 , UserSPID = @@SPID
 , DateLockTaken = SYSDATETIME()
 , DateLockExpires = DATEADD(MINUTE, 10, SYSDATETIME())
 WHERE ProcedureName = 'dbo.DoSomeWork'
 AND (IsLocked = 0
 OR (IsLocked = 1 AND DateLockExpires < SYSDATETIME())
 )

 IF COALESCE(@@ROWCOUNT, 0) = 0
 BEGIN
 ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;
 END

 /** DO WHATEVER NEEDS TO BE DONE */

 /** Release the lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 0
 , UserSPID = NULL
 , DateLockTaken = NULL
 , DateLockExpires = NULL
 WHERE ProcedureName = 'dbo.DoSomeWork'

END

answered 23 mins ago

Jonathan Fite

3,694818

add a commentÂ |Â

up vote
1
down vote

Another option is to build a table to control access to the procedure. the example below shows a possible table as well as a procedure that could use it.

CREATE TABLE dbo.ProcedureLock
 (
 ProcedureLockID INT NOT NULL IDENTITY(1,1)
 , ProcedureName SYSNAME NOT NULL
 , IsLocked BIT NOT NULL CONSTRAINT DF_ProcedureLock_IsLocked DEFAULT (0)
 , UserSPID INT NULL
 , DateLockTaken DATETIME2(7) NULL
 , DateLockExpires DATETIME2(7) NULL
 , CONSTRAINT PK_ProcedureLock PRIMARY KEY CLUSTERED (ProcedureLockID)
 )

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_ProcedureLock_ProcedureName
 ON dbo.ProcedureLock (ProcedureName)

INSERT INTO dbo.ProcedureLock
 (ProcedureName, IsLocked)
VALUES ('dbo.DoSomeWork', 0)

GO

CREATE PROCEDURE dbo.DoSomeWork
AS
BEGIN

 /** Take Lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 1
 , UserSPID = @@SPID
 , DateLockTaken = SYSDATETIME()
 , DateLockExpires = DATEADD(MINUTE, 10, SYSDATETIME())
 WHERE ProcedureName = 'dbo.DoSomeWork'
 AND (IsLocked = 0
 OR (IsLocked = 1 AND DateLockExpires < SYSDATETIME())
 )

 IF COALESCE(@@ROWCOUNT, 0) = 0
 BEGIN
 ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;
 END

 /** DO WHATEVER NEEDS TO BE DONE */

 /** Release the lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 0
 , UserSPID = NULL
 , DateLockTaken = NULL
 , DateLockExpires = NULL
 WHERE ProcedureName = 'dbo.DoSomeWork'

END

answered 23 mins ago

Jonathan Fite

3,694818

add a commentÂ |Â

up vote
1
down vote

Another option is to build a table to control access to the procedure. the example below shows a possible table as well as a procedure that could use it.

CREATE TABLE dbo.ProcedureLock
 (
 ProcedureLockID INT NOT NULL IDENTITY(1,1)
 , ProcedureName SYSNAME NOT NULL
 , IsLocked BIT NOT NULL CONSTRAINT DF_ProcedureLock_IsLocked DEFAULT (0)
 , UserSPID INT NULL
 , DateLockTaken DATETIME2(7) NULL
 , DateLockExpires DATETIME2(7) NULL
 , CONSTRAINT PK_ProcedureLock PRIMARY KEY CLUSTERED (ProcedureLockID)
 )

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_ProcedureLock_ProcedureName
 ON dbo.ProcedureLock (ProcedureName)

INSERT INTO dbo.ProcedureLock
 (ProcedureName, IsLocked)
VALUES ('dbo.DoSomeWork', 0)

GO

CREATE PROCEDURE dbo.DoSomeWork
AS
BEGIN

 /** Take Lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 1
 , UserSPID = @@SPID
 , DateLockTaken = SYSDATETIME()
 , DateLockExpires = DATEADD(MINUTE, 10, SYSDATETIME())
 WHERE ProcedureName = 'dbo.DoSomeWork'
 AND (IsLocked = 0
 OR (IsLocked = 1 AND DateLockExpires < SYSDATETIME())
 )

 IF COALESCE(@@ROWCOUNT, 0) = 0
 BEGIN
 ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;
 END

 /** DO WHATEVER NEEDS TO BE DONE */

 /** Release the lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 0
 , UserSPID = NULL
 , DateLockTaken = NULL
 , DateLockExpires = NULL
 WHERE ProcedureName = 'dbo.DoSomeWork'

END

answered 23 mins ago

Jonathan Fite

3,694818

Another option is to build a table to control access to the procedure. the example below shows a possible table as well as a procedure that could use it.

CREATE TABLE dbo.ProcedureLock
 (
 ProcedureLockID INT NOT NULL IDENTITY(1,1)
 , ProcedureName SYSNAME NOT NULL
 , IsLocked BIT NOT NULL CONSTRAINT DF_ProcedureLock_IsLocked DEFAULT (0)
 , UserSPID INT NULL
 , DateLockTaken DATETIME2(7) NULL
 , DateLockExpires DATETIME2(7) NULL
 , CONSTRAINT PK_ProcedureLock PRIMARY KEY CLUSTERED (ProcedureLockID)
 )

CREATE UNIQUE NONCLUSTERED INDEX IDXUQ_ProcedureLock_ProcedureName
 ON dbo.ProcedureLock (ProcedureName)

INSERT INTO dbo.ProcedureLock
 (ProcedureName, IsLocked)
VALUES ('dbo.DoSomeWork', 0)

GO

CREATE PROCEDURE dbo.DoSomeWork
AS
BEGIN

 /** Take Lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 1
 , UserSPID = @@SPID
 , DateLockTaken = SYSDATETIME()
 , DateLockExpires = DATEADD(MINUTE, 10, SYSDATETIME())
 WHERE ProcedureName = 'dbo.DoSomeWork'
 AND (IsLocked = 0
 OR (IsLocked = 1 AND DateLockExpires < SYSDATETIME())
 )

 IF COALESCE(@@ROWCOUNT, 0) = 0
 BEGIN
 ;THROW 50000, 'This procedure can only be run one at a time, please wait', 1;
 END

 /** DO WHATEVER NEEDS TO BE DONE */

 /** Release the lock */
 UPDATE dbo.ProcedureLock
 SET IsLocked = 0
 , UserSPID = NULL
 , DateLockTaken = NULL
 , DateLockExpires = NULL
 WHERE ProcedureName = 'dbo.DoSomeWork'

END

answered 23 mins ago

Jonathan Fite

3,694818

answered 23 mins ago

Jonathan Fite

3,694818

answered 23 mins ago

Jonathan Fite

3,694818

answered 23 mins ago

Jonathan Fite

3,694818

add a commentÂ |Â

twoheadedmona is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

twoheadedmona is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky