Lock multiple tables in SQL server
I need to check the state of my data in the database at a given moment in time across multiple tables.
I have the following tables
CREATE TABLE dbo.Activities
(
ActivityId int PRIMARY KEY NOT NULL,
StateId int NOT NULL
)
CREATE TABLE dbo.States
(
StateId int PRIMARY KEY NOT NULL,
IsFinal bit NOT NULL
)
CREATE TABLE dbo.Requests
(
RequestId int PRIMARY KEY NOT NULL,
CreatedUt datetime2(7) NOT NULL
)
I'm using a workflow engine. The workflow can have multiple active branches at the same time. Every time the state of an activity changes I notify the engine and start a request. The engine responds to the request with the text activity to be started or "no activity" if there are no more activities on that branch. There may be other activities in active states on other branches. When I get the response I delete the request and start an activity if needed.
I need to know when to stop the workflow. That would be when all activities are in final states and no requests exist.
I want to check the states of both tables at the same point in time. I need to avoid race conditions like this ones
- I check the activities table -> all are in final states (but there is an open request at this time that the code does not know about)
- The request finishes and ads a new activity and gets deleted
- I check the requests table and see no request so I wrongfully stop the workflow.
Is there a lock in SQL that I can use to lock both tables before checking them?
sql
sql-server edited Nov 23 '18 at 13:02
marc_s
583k13011241270
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
add a comment |
I need to check the state of my data in the database at a given moment in time across multiple tables.
I have the following tables
CREATE TABLE dbo.Activities
(
ActivityId int PRIMARY KEY NOT NULL,
StateId int NOT NULL
)
CREATE TABLE dbo.States
(
StateId int PRIMARY KEY NOT NULL,
IsFinal bit NOT NULL
)
CREATE TABLE dbo.Requests
(
RequestId int PRIMARY KEY NOT NULL,
CreatedUt datetime2(7) NOT NULL
)
I'm using a workflow engine. The workflow can have multiple active branches at the same time. Every time the state of an activity changes I notify the engine and start a request. The engine responds to the request with the text activity to be started or "no activity" if there are no more activities on that branch. There may be other activities in active states on other branches. When I get the response I delete the request and start an activity if needed.
I need to know when to stop the workflow. That would be when all activities are in final states and no requests exist.
I want to check the states of both tables at the same point in time. I need to avoid race conditions like this ones
- I check the activities table -> all are in final states (but there is an open request at this time that the code does not know about)
- The request finishes and ads a new activity and gets deleted
- I check the requests table and see no request so I wrongfully stop the workflow.
Is there a lock in SQL that I can use to lock both tables before checking them?
sql
sql-server edited Nov 23 '18 at 13:02
marc_s
583k13011241270
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
1
Read about transactions.
– jarlh
Nov 23 '18 at 9:50
stackoverflow.com/questions/57625/…
– Svenmarim
Nov 23 '18 at 13:04
add a comment |
I need to check the state of my data in the database at a given moment in time across multiple tables.
I have the following tables
CREATE TABLE dbo.Activities
(
ActivityId int PRIMARY KEY NOT NULL,
StateId int NOT NULL
)
CREATE TABLE dbo.States
(
StateId int PRIMARY KEY NOT NULL,
IsFinal bit NOT NULL
)
CREATE TABLE dbo.Requests
(
RequestId int PRIMARY KEY NOT NULL,
CreatedUt datetime2(7) NOT NULL
)
I'm using a workflow engine. The workflow can have multiple active branches at the same time. Every time the state of an activity changes I notify the engine and start a request. The engine responds to the request with the text activity to be started or "no activity" if there are no more activities on that branch. There may be other activities in active states on other branches. When I get the response I delete the request and start an activity if needed.
I need to know when to stop the workflow. That would be when all activities are in final states and no requests exist.
I want to check the states of both tables at the same point in time. I need to avoid race conditions like this ones
- I check the activities table -> all are in final states (but there is an open request at this time that the code does not know about)
- The request finishes and ads a new activity and gets deleted
- I check the requests table and see no request so I wrongfully stop the workflow.
Is there a lock in SQL that I can use to lock both tables before checking them?
sql
sql-server edited Nov 23 '18 at 13:02
marc_s
583k13011241270
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
I need to check the state of my data in the database at a given moment in time across multiple tables.
I have the following tables
CREATE TABLE dbo.Activities
(
ActivityId int PRIMARY KEY NOT NULL,
StateId int NOT NULL
)
CREATE TABLE dbo.States
(
StateId int PRIMARY KEY NOT NULL,
IsFinal bit NOT NULL
)
CREATE TABLE dbo.Requests
(
RequestId int PRIMARY KEY NOT NULL,
CreatedUt datetime2(7) NOT NULL
)
I'm using a workflow engine. The workflow can have multiple active branches at the same time. Every time the state of an activity changes I notify the engine and start a request. The engine responds to the request with the text activity to be started or "no activity" if there are no more activities on that branch. There may be other activities in active states on other branches. When I get the response I delete the request and start an activity if needed.
I need to know when to stop the workflow. That would be when all activities are in final states and no requests exist.
I want to check the states of both tables at the same point in time. I need to avoid race conditions like this ones
- I check the activities table -> all are in final states (but there is an open request at this time that the code does not know about)
- The request finishes and ads a new activity and gets deleted
- I check the requests table and see no request so I wrongfully stop the workflow.
Is there a lock in SQL that I can use to lock both tables before checking them?
sql
sql-server sql
sql-server edited Nov 23 '18 at 13:02
marc_s
583k13011241270
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
edited Nov 23 '18 at 13:02
marc_s
583k13011241270
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
edited Nov 23 '18 at 13:02
marc_s
583k13011241270
edited Nov 23 '18 at 13:02
marc_s
583k13011241270
edited Nov 23 '18 at 13:02
marc_s
583k13011241270
583k13011241270
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
asked Nov 23 '18 at 9:37
Sorin BolosSorin Bolos
10328
10328
1
Read about transactions.
– jarlh
Nov 23 '18 at 9:50
stackoverflow.com/questions/57625/…
– Svenmarim
Nov 23 '18 at 13:04
add a comment |
1
Read about transactions.
– jarlh
Nov 23 '18 at 9:50
stackoverflow.com/questions/57625/…
– Svenmarim
Nov 23 '18 at 13:04
1
1
Read about transactions.
– jarlh
Nov 23 '18 at 9:50
Read about transactions.
– jarlh
Nov 23 '18 at 9:50
stackoverflow.com/questions/57625/…
– Svenmarim
Nov 23 '18 at 13:04
stackoverflow.com/questions/57625/…
– Svenmarim
Nov 23 '18 at 13:04
add a comment |
0
active
oldest
votes
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53444023%2flock-multiple-tables-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53444023%2flock-multiple-tables-in-sql-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Read about transactions.
– jarlh
Nov 23 '18 at 9:50
stackoverflow.com/questions/57625/…
– Svenmarim
Nov 23 '18 at 13:04