Let's say I have a SQL query that takes 3 minutes to execute. If the data being accessed by the query changes during execution, do I get the data as of the start of the query or as of the end or somewhere in between?
Announcement
Collapse
No announcement yet.
Data Changes During Long Queries
Collapse
X
-
It depends. First, are you using commitment control or not? Isolation level "uncommitted read" lets you see changes in other jobs, but "cursor stability" won't let you see them. There are four isolation levels that you need to consider when using commitment control.
Second, a query accesses data in pages, and if someone updates a row that you haven't paged into memory yet, then you'll see the update. If they update a row that is in the page that is currently in memory, you won't see the change.
-
Originally posted by TedHolt View PostIt depends. First, are you using commitment control or not? Isolation level "uncommitted read" lets you see changes in other jobs, but "cursor stability" won't let you see them. There are four isolation levels that you need to consider when using commitment control.
Second, a query accesses data in pages, and if someone updates a row that you haven't paged into memory yet, then you'll see the update. If they update a row that is in the page that is currently in memory, you won't see the change.
Does IBM i not do snapshots of the data for select queries? Is there a way to have the database engine do that for particular sessions/queries? It sounds like maybe cursor stability is what I should look into.
Comment
-
Originally posted by TedHolt View PostIf your applications don't use commitment control, then isolation level is irrelevant. Queries will see changes only for rows that have not yet been paged into memory.
Comment
Comment