November 4, 2019 at 9:26 pm
ZZartin -
Getting a second page of data is not rerunning a query. It's getting more data from a single query. That is what is under discussion in this article and these posts.
If you contend that data volatility means that getting a consistent view of data is not possible, you are, unfortunately, just incorrect.
Except the technique being described here is not just getting more data from a single query. It is literally running a new query with potentially different values for offset and fetch next against the current data in the database. As such it is 100% expected that against a volatile data set this will generate different results, just like running a query for say all orders with a specific status can change minute over minute. And it's been my experience that generally this is what people actually want when they ask for pagination.
As far as I know SQL server has no way out of the box to run a query a query and have it give you the results based on the data that was in the database at some arbitrary time. And that's also not what this technique is describing doing.
November 4, 2019 at 9:30 pm
rick-1071254 wrote:ZZartin -
Getting a second page of data is not rerunning a query. It's getting more data from a single query. That is what is under discussion in this article and these posts.
If you contend that data volatility means that getting a consistent view of data is not possible, you are, unfortunately, just incorrect.
As far as I know SQL server has no way out of the box to run a query a query and have it give you the results based on the data that was in the database at some arbitrary time. And that's also not what this technique is describing doing.
Snapshot isolation?
November 4, 2019 at 9:55 pm
ZZartin wrote:rick-1071254 wrote:ZZartin -
Getting a second page of data is not rerunning a query. It's getting more data from a single query. That is what is under discussion in this article and these posts.
If you contend that data volatility means that getting a consistent view of data is not possible, you are, unfortunately, just incorrect.
As far as I know SQL server has no way out of the box to run a query a query and have it give you the results based on the data that was in the database at some arbitrary time. And that's also not what this technique is describing doing.
Snapshot isolation?
Snapshot Isolation doesn't let you for example just write a query where you can say show me the query based on the data from 10 minutes ago.
November 4, 2019 at 10:00 pm
Snapshot isolation?
Bingo. And there are other solutions with other databases.
And, no, you are incorrect about the problem, which is stated quite clearly in the first sentence of the section labeled, curiously enough, "The problem defined"
One of the features I've been asked for many times while building applications is a way to page through the results of a query.
Note that it says "a query". It is literally, as you say, running one query, not multiple queries. Your representation is a different use case.
November 4, 2019 at 10:04 pm
Do you expect your bank account to be locked while you page through your transaction history?
Not if I am the only one accessing it, which is exactly what multi-version consistency or snapshot isolation provides. The whole point of these solutions is so that readers don't block writers.
But if there is a chance someone is writing to my bank account, and that will affect my potential action at the end of my examination, you're dang right I want the account locked, as does the bank.
This is data consistency 101 folks.
November 4, 2019 at 10:06 pm
Snapshot Isolation doesn't let you for example just write a query where you can say show me the query based on the data from 10 minutes ago.
I don't know snapshot isolation well enough to vouch for this, but, again, this is not what the use case is. The use case says that you want a consistent view of the data at the time of the query. That's what snapshot isolation does. Multi-version consistency, in some databases, does in fact allow you to query data 10 minutes ago - or potentially any length of time ago.
November 4, 2019 at 10:08 pm
Snapshot isolation?
Bingo. And there are other solutions with other databases.
And, no, you are incorrect about the problem, which is stated quite clearly in the first sentence of the section labeled, curiously enough, "The problem defined"
One of the features I've been asked for many times while building applications is a way to page through the results of a query.
Note that it says "a query". It is literally, as you say, running one query, not multiple queries. Your representation is a different use case.
Except snapshot isolation does not automatically do what you say as it operates on an individual statement level. So each subsequent query might still use different data.
And if you want to argue the semantics of the article that's fine, but the technique described technically does require running multiple queries or more precisely to make you semantically happy running one query multiple times with different values for some of the parameters. Especially as it specifically mentions you will be rerunning the query.
This means that each call to a query using OFFSET and FETCH must re-run the query and determine what rows to return.
November 4, 2019 at 10:23 pm
Note that it says "a query". It is literally, as you say, running one query, not multiple queries. Your representation is a different use case.
Yes one query. Run multiple times. Sorry you didn't understand the article.
But if there is a chance someone is writing to my bank account, and that will affect my potential action at the end of my examination, you're dang right I want the account locked, as does the bank.
You would want a large $$$ refund delayed because you submitted a query to view your prior transaction history? For how long lol? Banks don't ever want to lock your account against new transactions. Never.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
November 4, 2019 at 10:25 pm
This is getting tiring.
Except snapshot isolation does not automatically do what you say as it operates on an individual statement level. So each subsequent query might still use different data.
Yes, as I said in my initial reply, it is an individual statement - a query. Not sure why this is not getting through.
And if you want to argue the semantics of the article that's fine, but the technique described technically does require running multiple queries. Especially as it specifically mentions you will be rerunning the query.
It's not semantics - it is addressing the problem as stated. Microsoft implemented snapshot isolation to combat a technical advantage that Oracle (and others) had over them for 20 years. The need to see a consistent view of data is pretty standard. There are different way of implementing it, but snapshot isolation does it for you.
It took me about 30 seconds to find this in the SQL Server on line docs
The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins.
That is what should be done. Using OFFSET is not.
November 4, 2019 at 10:25 pm
Note that it says "a query". It is literally, as you say, running one query, not multiple queries. Your representation is a different use case.
I think you're being unnecessarily pedantic, and you're conflating "a query" with "the results from a query". I don't think the author's intent was as you described, though maybe it was. Based on your interpretation I'm not sure exactly how you expect users to page through the data without executing another query against the database, which is what the article is demonstrating -- how to run multiple queries against a dataset to page through the data.
We are, unfortunately, arguing points that the article author conspicuously omitted (deliberately or not), and I think I can see why. 🙂
November 4, 2019 at 10:35 pm
This is getting tiring.
Except snapshot isolation does not automatically do what you say as it operates on an individual statement level. So each subsequent query might still use different data.
Yes, as I said in my initial reply, it is an individual statement - a query. Not sure why this is not getting through.
And if you want to argue the semantics of the article that's fine, but the technique described technically does require running multiple queries. Especially as it specifically mentions you will be rerunning the query.
It's not semantics - it is addressing the problem as stated. Microsoft implemented snapshot isolation to combat a technical advantage that Oracle (and others) had over them for 20 years. The need to see a consistent view of data is pretty standard. There are different way of implementing it, but snapshot isolation does it for you.
It took me about 30 seconds to find this in the SQL Server on line docs
The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins.
That is what should be done. Using OFFSET is not.
I'm not sure what you are not understanding. Snapshot isolation isolates a single transaction to prevent locking issues, this technique requires running multiple transactions. Snapshot isolation does not prevent the issue you are mentioning.
What would prevent the issue is returning all the data in the query to the application or storing it in some kind of temp structure in SQL Server. Both of which have their own issues that this technique is designed to by pass.
I'm also not so sure why you are so upset that if someone is clicking through multiple pages of data they'll see the current data in the database. Which is often quite desirable as this is primarily intended to be a technique for sending records to an app for display not back end processing. For example if I'm on say a forum and I click to page two, then back to page 100% expect to see new posts.
November 5, 2019 at 2:11 am
The truly sad part about this discussion is the lack of understanding of data integrity and consistency. Sigh.
November 5, 2019 at 2:29 am
The truly sad part about this discussion is the lack of understanding of data integrity and consistency. Sigh.
You do understand that this technique is intended primarily to present information to an application for display not data manipulation? And that presenting out of date inaccurate information is very much not ideal as it could lead to people acting incorrectly on it and actually creating the situation you seem so afraid of?
November 5, 2019 at 5:46 am
This was removed by the editor as SPAM
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply