Clarification on transactions handling in sql server with ADO .NET
I need a little help understanding transaction behaviour in sql server, expecially in a web application I am rewriting.
This is the actual process when user creates a new report:
- read max from T_REPORT.
- increment result by one (new id) and saves this values in a local variable.
- insert new row in T_REPORT, using the new id.
- do insert in other tables using new report id as external key.
I have to rewrite this process in a way that prevents the existence of dirty or incomplete report data in database when a error occurs during this process; so I decided to wrap this process in a transaction, but I don't know what type of transaction isolation to use.
How sql server handles transactions? Does it enqueue them? If not, is it correcto to use "read uncommitted" isolation level? just to allow every new report process to reserve a different new report id for his insert in concurrent calls.
Here are some additional informations:
- Application is single threaded, but it can have multiple users
connected simultaneously. - Database tables identities don't have
autoincrement and I am forbidden to change database schema in any way - I use sql server 2008R2, ado.net, c# 7
sql-server sql-server-2008-r2 ado.net c#-7.0 edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
asked Nov 12 '18 at 9:32
albealbe
175
|
show 2 more comments
I need a little help understanding transaction behaviour in sql server, expecially in a web application I am rewriting.
This is the actual process when user creates a new report:
- read max from T_REPORT.
- increment result by one (new id) and saves this values in a local variable.
- insert new row in T_REPORT, using the new id.
- do insert in other tables using new report id as external key.
I have to rewrite this process in a way that prevents the existence of dirty or incomplete report data in database when a error occurs during this process; so I decided to wrap this process in a transaction, but I don't know what type of transaction isolation to use.
How sql server handles transactions? Does it enqueue them? If not, is it correcto to use "read uncommitted" isolation level? just to allow every new report process to reserve a different new report id for his insert in concurrent calls.
Here are some additional informations:
- Application is single threaded, but it can have multiple users
connected simultaneously. - Database tables identities don't have
autoincrement and I am forbidden to change database schema in any way - I use sql server 2008R2, ado.net, c# 7
sql-server sql-server-2008-r2 ado.net c#-7.0 edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
asked Nov 12 '18 at 9:32
albealbe
175
It's a web application - multiple concurrent users means multithreaded application, no way around it, I'm afraid. Also - "I am forbidden to change database schema in any way" - The way you are working now will cause problems - the correct way to do it is use the database auto-increment mechanism.
– Zohar Peled
Nov 12 '18 at 11:10
just a clarification: the concurrent users are handled by iis, where the web application is hosted. But all methods in application are syncronous
– albe
Nov 12 '18 at 13:32
Sure, but the fact that IIS manages the threads for you dost not mean you are in a single-threaded environment. More over, in theory, multiple instances of an application can connect to the same database concurrently, each application is single-threaded, but both connections has the same effect as a multithreaded application with regards to the database - this is why we have locks.
– Zohar Peled
Nov 12 '18 at 13:35
i really can't change db schema. Is "Read uncommitted" isolation enough, or there is a way to force sql to enqueue transactions?
– albe
Nov 12 '18 at 14:14
Read uncommitted is absolutely not what you want for this. If anything, I would go with the opposite end - serializable. official documentation
– Zohar Peled
Nov 12 '18 at 14:18
|
show 2 more comments
I need a little help understanding transaction behaviour in sql server, expecially in a web application I am rewriting.
This is the actual process when user creates a new report:
- read max from T_REPORT.
- increment result by one (new id) and saves this values in a local variable.
- insert new row in T_REPORT, using the new id.
- do insert in other tables using new report id as external key.
I have to rewrite this process in a way that prevents the existence of dirty or incomplete report data in database when a error occurs during this process; so I decided to wrap this process in a transaction, but I don't know what type of transaction isolation to use.
How sql server handles transactions? Does it enqueue them? If not, is it correcto to use "read uncommitted" isolation level? just to allow every new report process to reserve a different new report id for his insert in concurrent calls.
Here are some additional informations:
- Application is single threaded, but it can have multiple users
connected simultaneously. - Database tables identities don't have
autoincrement and I am forbidden to change database schema in any way - I use sql server 2008R2, ado.net, c# 7
sql-server sql-server-2008-r2 ado.net c#-7.0 edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
asked Nov 12 '18 at 9:32
albealbe
175
I need a little help understanding transaction behaviour in sql server, expecially in a web application I am rewriting.
This is the actual process when user creates a new report:
- read max from T_REPORT.
- increment result by one (new id) and saves this values in a local variable.
- insert new row in T_REPORT, using the new id.
- do insert in other tables using new report id as external key.
I have to rewrite this process in a way that prevents the existence of dirty or incomplete report data in database when a error occurs during this process; so I decided to wrap this process in a transaction, but I don't know what type of transaction isolation to use.
How sql server handles transactions? Does it enqueue them? If not, is it correcto to use "read uncommitted" isolation level? just to allow every new report process to reserve a different new report id for his insert in concurrent calls.
Here are some additional informations:
- Application is single threaded, but it can have multiple users
connected simultaneously. - Database tables identities don't have
autoincrement and I am forbidden to change database schema in any way - I use sql server 2008R2, ado.net, c# 7
sql-server sql-server-2008-r2 ado.net c#-7.0 sql-server sql-server-2008-r2 ado.net c#-7.0 edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
asked Nov 12 '18 at 9:32
albealbe
175
edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
asked Nov 12 '18 at 9:32
albealbe
175
edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
edited Nov 12 '18 at 9:39
Vadim Kotov
4,35153247
4,35153247
asked Nov 12 '18 at 9:32
albealbe
175
asked Nov 12 '18 at 9:32
albealbe
175
asked Nov 12 '18 at 9:32
albealbe
175
175
It's a web application - multiple concurrent users means multithreaded application, no way around it, I'm afraid. Also - "I am forbidden to change database schema in any way" - The way you are working now will cause problems - the correct way to do it is use the database auto-increment mechanism.
– Zohar Peled
Nov 12 '18 at 11:10
just a clarification: the concurrent users are handled by iis, where the web application is hosted. But all methods in application are syncronous
– albe
Nov 12 '18 at 13:32
Sure, but the fact that IIS manages the threads for you dost not mean you are in a single-threaded environment. More over, in theory, multiple instances of an application can connect to the same database concurrently, each application is single-threaded, but both connections has the same effect as a multithreaded application with regards to the database - this is why we have locks.
– Zohar Peled
Nov 12 '18 at 13:35
i really can't change db schema. Is "Read uncommitted" isolation enough, or there is a way to force sql to enqueue transactions?
– albe
Nov 12 '18 at 14:14
Read uncommitted is absolutely not what you want for this. If anything, I would go with the opposite end - serializable. official documentation
– Zohar Peled
Nov 12 '18 at 14:18
|
show 2 more comments
It's a web application - multiple concurrent users means multithreaded application, no way around it, I'm afraid. Also - "I am forbidden to change database schema in any way" - The way you are working now will cause problems - the correct way to do it is use the database auto-increment mechanism.
– Zohar Peled
Nov 12 '18 at 11:10
just a clarification: the concurrent users are handled by iis, where the web application is hosted. But all methods in application are syncronous
– albe
Nov 12 '18 at 13:32
Sure, but the fact that IIS manages the threads for you dost not mean you are in a single-threaded environment. More over, in theory, multiple instances of an application can connect to the same database concurrently, each application is single-threaded, but both connections has the same effect as a multithreaded application with regards to the database - this is why we have locks.
– Zohar Peled
Nov 12 '18 at 13:35
i really can't change db schema. Is "Read uncommitted" isolation enough, or there is a way to force sql to enqueue transactions?
– albe
Nov 12 '18 at 14:14
Read uncommitted is absolutely not what you want for this. If anything, I would go with the opposite end - serializable. official documentation
– Zohar Peled
Nov 12 '18 at 14:18
It's a web application - multiple concurrent users means multithreaded application, no way around it, I'm afraid. Also - "I am forbidden to change database schema in any way" - The way you are working now will cause problems - the correct way to do it is use the database auto-increment mechanism.
– Zohar Peled
Nov 12 '18 at 11:10
It's a web application - multiple concurrent users means multithreaded application, no way around it, I'm afraid. Also - "I am forbidden to change database schema in any way" - The way you are working now will cause problems - the correct way to do it is use the database auto-increment mechanism.
– Zohar Peled
Nov 12 '18 at 11:10
just a clarification: the concurrent users are handled by iis, where the web application is hosted. But all methods in application are syncronous
– albe
Nov 12 '18 at 13:32
just a clarification: the concurrent users are handled by iis, where the web application is hosted. But all methods in application are syncronous
– albe
Nov 12 '18 at 13:32
Sure, but the fact that IIS manages the threads for you dost not mean you are in a single-threaded environment. More over, in theory, multiple instances of an application can connect to the same database concurrently, each application is single-threaded, but both connections has the same effect as a multithreaded application with regards to the database - this is why we have locks.
– Zohar Peled
Nov 12 '18 at 13:35
Sure, but the fact that IIS manages the threads for you dost not mean you are in a single-threaded environment. More over, in theory, multiple instances of an application can connect to the same database concurrently, each application is single-threaded, but both connections has the same effect as a multithreaded application with regards to the database - this is why we have locks.
– Zohar Peled
Nov 12 '18 at 13:35
i really can't change db schema. Is "Read uncommitted" isolation enough, or there is a way to force sql to enqueue transactions?
– albe
Nov 12 '18 at 14:14
i really can't change db schema. Is "Read uncommitted" isolation enough, or there is a way to force sql to enqueue transactions?
– albe
Nov 12 '18 at 14:14
Read uncommitted is absolutely not what you want for this. If anything, I would go with the opposite end - serializable. official documentation
– Zohar Peled
Nov 12 '18 at 14:18
Read uncommitted is absolutely not what you want for this. If anything, I would go with the opposite end - serializable. official documentation
– Zohar Peled
Nov 12 '18 at 14:18
|
show 2 more comments
0
active
oldest
votes
Your Answer
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%2f53259276%2fclarification-on-transactions-handling-in-sql-server-with-ado-net%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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53259276%2fclarification-on-transactions-handling-in-sql-server-with-ado-net%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
It's a web application - multiple concurrent users means multithreaded application, no way around it, I'm afraid. Also - "I am forbidden to change database schema in any way" - The way you are working now will cause problems - the correct way to do it is use the database auto-increment mechanism.
– Zohar Peled
Nov 12 '18 at 11:10
just a clarification: the concurrent users are handled by iis, where the web application is hosted. But all methods in application are syncronous
– albe
Nov 12 '18 at 13:32
Sure, but the fact that IIS manages the threads for you dost not mean you are in a single-threaded environment. More over, in theory, multiple instances of an application can connect to the same database concurrently, each application is single-threaded, but both connections has the same effect as a multithreaded application with regards to the database - this is why we have locks.
– Zohar Peled
Nov 12 '18 at 13:35
i really can't change db schema. Is "Read uncommitted" isolation enough, or there is a way to force sql to enqueue transactions?
– albe
Nov 12 '18 at 14:14
Read uncommitted is absolutely not what you want for this. If anything, I would go with the opposite end - serializable. official documentation
– Zohar Peled
Nov 12 '18 at 14:18