Mixing
Read Committed Snapshot, stale data reads?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
I'm running SQL Azure and I have a following case.
I have two transactions W (writer) and R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The R transaction starts at the same time as W and continuously executes SELECT statements to read the table that is written by the transaction W.
Normally, the transaction R sees all the the changes immediately after W commits its changes. However, occasionally the transaction R sees the state without the most recent commit from the transaction W.
The question is:
Does the RCSI guarantee that R always see the most recent state of the table?
According to:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query. But is it guaranteed, when having two independent sessions?
sql-server azure-sql-database isolation-level azure-sql-database-v12
edited 12 secs ago
Paul White♦
46.6k14252401
asked 1 hour ago
Grzegorz Banczak
1112
New contributor
Grzegorz Banczak 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 |Â
up vote
2
down vote
favorite
I'm running SQL Azure and I have a following case.
I have two transactions W (writer) and R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The R transaction starts at the same time as W and continuously executes SELECT statements to read the table that is written by the transaction W.
Normally, the transaction R sees all the the changes immediately after W commits its changes. However, occasionally the transaction R sees the state without the most recent commit from the transaction W.
The question is:
Does the RCSI guarantee that R always see the most recent state of the table?
According to:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query. But is it guaranteed, when having two independent sessions?
sql-server azure-sql-database isolation-level azure-sql-database-v12
edited 12 secs ago
Paul White♦
46.6k14252401
asked 1 hour ago
Grzegorz Banczak
1112
New contributor
Grzegorz Banczak 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 |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I'm running SQL Azure and I have a following case.
I have two transactions W (writer) and R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The R transaction starts at the same time as W and continuously executes SELECT statements to read the table that is written by the transaction W.
Normally, the transaction R sees all the the changes immediately after W commits its changes. However, occasionally the transaction R sees the state without the most recent commit from the transaction W.
The question is:
Does the RCSI guarantee that R always see the most recent state of the table?
According to:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query. But is it guaranteed, when having two independent sessions?
sql-server azure-sql-database isolation-level azure-sql-database-v12
edited 12 secs ago
Paul White♦
46.6k14252401
asked 1 hour ago
Grzegorz Banczak
1112
New contributor
Grzegorz Banczak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I'm running SQL Azure and I have a following case.
I have two transactions W (writer) and R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The R transaction starts at the same time as W and continuously executes SELECT statements to read the table that is written by the transaction W.
Normally, the transaction R sees all the the changes immediately after W commits its changes. However, occasionally the transaction R sees the state without the most recent commit from the transaction W.
The question is:
Does the RCSI guarantee that R always see the most recent state of the table?
According to:
https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query. But is it guaranteed, when having two independent sessions?
sql-server azure-sql-database isolation-level azure-sql-database-v12
sql-server azure-sql-database isolation-level azure-sql-database-v12
edited 12 secs ago
Paul White♦
46.6k14252401
asked 1 hour ago
Grzegorz Banczak
1112
New contributor
Grzegorz Banczak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 12 secs ago
Paul White♦
46.6k14252401
asked 1 hour ago
Grzegorz Banczak
1112
New contributor
Grzegorz Banczak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 12 secs ago
Paul White♦
46.6k14252401
edited 12 secs ago
Paul White♦
46.6k14252401
edited 12 secs ago
Paul White♦
46.6k14252401
46.6k14252401
asked 1 hour ago
Grzegorz Banczak
1112
New contributor
Grzegorz Banczak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 1 hour ago
Grzegorz Banczak
1112
asked 1 hour ago
Grzegorz Banczak
1112
1112
New contributor
Grzegorz Banczak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Grzegorz Banczak is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Grzegorz Banczak 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 |Â
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
2
down vote
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query.
Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.
But is it guaranteed, when having two independent sessions?
Yes, it should be.
Does the RCSI guarantee that
Ralways see the most recent state of the table?
Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.
What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the same of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.
Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.
answered 47 mins ago
Sean Gallardy
13.2k11943
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query.
Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.
But is it guaranteed, when having two independent sessions?
Yes, it should be.
Does the RCSI guarantee that
Ralways see the most recent state of the table?
Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.
What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the same of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.
Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.
answered 47 mins ago
Sean Gallardy
13.2k11943
add a comment |Â
up vote
2
down vote
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query.
Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.
But is it guaranteed, when having two independent sessions?
Yes, it should be.
Does the RCSI guarantee that
Ralways see the most recent state of the table?
Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.
What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the same of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.
Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.
answered 47 mins ago
Sean Gallardy
13.2k11943
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query.
Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.
But is it guaranteed, when having two independent sessions?
Yes, it should be.
Does the RCSI guarantee that
Ralways see the most recent state of the table?
Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.
What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the same of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.
Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.
answered 47 mins ago
Sean Gallardy
13.2k11943
Read committed should always get the latest revision of the row that was available at the time of executing the SELECT query.
Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.
But is it guaranteed, when having two independent sessions?
Yes, it should be.
Does the RCSI guarantee that
Ralways see the most recent state of the table?
Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.
What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the same of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.
Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.
answered 47 mins ago
Sean Gallardy
13.2k11943
answered 47 mins ago
Sean Gallardy
13.2k11943
answered 47 mins ago
Sean Gallardy
13.2k11943
answered 47 mins ago
Sean Gallardy
13.2k11943
13.2k11943
add a comment |Â
add a comment |Â
Grzegorz Banczak is a new contributor. Be nice, and check out our Code of Conduct.
Grzegorz Banczak is a new contributor. Be nice, and check out our Code of Conduct.
Grzegorz Banczak is a new contributor. Be nice, and check out our Code of Conduct.
Grzegorz Banczak is a new contributor. Be nice, and check out our Code of Conduct.
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218490%2fread-committed-snapshot-stale-data-reads%23new-answer', 'question_page');
);
Post as a guest
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
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
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
