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

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

up vote
6
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 11 mins ago

Jerod Johnson

1534

asked 13 hours ago

twoheadedmona

311

New contributor

2

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
10 hours ago

add a commentÂ |Â

up vote
6
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 11 mins ago

Jerod Johnson

1534

asked 13 hours ago

twoheadedmona

311

New contributor

2

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
10 hours ago

add a commentÂ |Â

up vote
6
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 11 mins ago

Jerod Johnson

1534

asked 13 hours ago

twoheadedmona

311

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 11 mins ago

Jerod Johnson

1534

asked 13 hours ago

twoheadedmona

311

New contributor

edited 11 mins ago

Jerod Johnson

1534

asked 13 hours ago

twoheadedmona

311

New contributor

edited 11 mins ago

Jerod Johnson

1534

edited 11 mins ago

Jerod Johnson

1534

edited 11 mins ago

Jerod Johnson

1534

asked 13 hours ago

twoheadedmona

311

New contributor

asked 13 hours ago

twoheadedmona

311

asked 13 hours ago

twoheadedmona

311

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.

2

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
10 hours ago

add a commentÂ |Â

2

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
10 hours 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
10 hours ago

add a commentÂ |Â

4 Answers
4

active

oldest

votes

up vote
6
down vote

To add to @Tibor-Karaszi'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 7 hours ago

answered 9 hours ago

David Browne - Microsoft

8,899723

add a commentÂ |Â

up vote
5
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 13 hours ago

Tibor Karaszi

1,0375

add a commentÂ |Â

up vote
3
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 9 hours ago

Jonathan Fite

3,714818

add a commentÂ |Â

up vote
0
down vote

I realize the 'real' problem may be more complex.

In case it isn't: if you do the archiving immediately with a trigger you can avoid the problem you're trying to solve.

Hope that helps,

-Chris C.

answered 3 hours ago

J. Chris Compton

1012

New contributor

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-at-a-time%23new-answer', 'question_page');

);

Post as a guest

Name

4 Answers
4

active

oldest

votes

4 Answers
4

active

oldest

votes

up vote
6
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 7 hours ago

answered 9 hours ago

David Browne - Microsoft

8,899723

add a commentÂ |Â

up vote
6
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 7 hours ago

answered 9 hours ago

David Browne - Microsoft

8,899723

add a commentÂ |Â

up vote
6
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 7 hours ago

answered 9 hours ago

David Browne - Microsoft

8,899723

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 7 hours ago

answered 9 hours ago

David Browne - Microsoft

8,899723

edited 7 hours ago

answered 9 hours ago

David Browne - Microsoft

8,899723

answered 9 hours ago

David Browne - Microsoft

8,899723

answered 9 hours ago

David Browne - Microsoft

8,899723

add a commentÂ |Â

up vote
5
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 13 hours ago

Tibor Karaszi

1,0375

add a commentÂ |Â

up vote
5
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 13 hours ago

Tibor Karaszi

1,0375

add a commentÂ |Â

up vote
5
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 13 hours ago

Tibor Karaszi

1,0375

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 13 hours ago

Tibor Karaszi

1,0375

answered 13 hours ago

Tibor Karaszi

1,0375

answered 13 hours ago

Tibor Karaszi

1,0375

answered 13 hours ago

Tibor Karaszi

1,0375

add a commentÂ |Â

up vote
3
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 9 hours ago

Jonathan Fite

3,714818

add a commentÂ |Â

up vote
3
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 9 hours ago

Jonathan Fite

3,714818

add a commentÂ |Â

up vote
3
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 9 hours ago

Jonathan Fite

3,714818

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 9 hours ago

Jonathan Fite

3,714818

answered 9 hours ago

Jonathan Fite

3,714818

answered 9 hours ago

Jonathan Fite

3,714818

answered 9 hours ago

Jonathan Fite

3,714818

add a commentÂ |Â

up vote
0
down vote

I realize the 'real' problem may be more complex.

In case it isn't: if you do the archiving immediately with a trigger you can avoid the problem you're trying to solve.

Hope that helps,

-Chris C.

answered 3 hours ago

J. Chris Compton

1012

New contributor

add a commentÂ |Â

up vote
0
down vote

I realize the 'real' problem may be more complex.

In case it isn't: if you do the archiving immediately with a trigger you can avoid the problem you're trying to solve.

Hope that helps,

-Chris C.

answered 3 hours ago

J. Chris Compton

1012

New contributor

add a commentÂ |Â

up vote
0
down vote

I realize the 'real' problem may be more complex.

In case it isn't: if you do the archiving immediately with a trigger you can avoid the problem you're trying to solve.

Hope that helps,

-Chris C.

answered 3 hours ago

J. Chris Compton

1012

New contributor

I realize the 'real' problem may be more complex.

In case it isn't: if you do the archiving immediately with a trigger you can avoid the problem you're trying to solve.

Hope that helps,

-Chris C.

answered 3 hours ago

J. Chris Compton

1012

New contributor

answered 3 hours ago

J. Chris Compton

1012

New contributor

answered 3 hours ago

J. Chris Compton

1012

answered 3 hours ago

J. Chris Compton

1012

New contributor

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

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