January 11, 2017 at 6:14 pm
I am trying to troubleshoot an issue in a report and wondering if the below scenario is possible. I have a tables which is 548GB and there are reports which sometimes select 5 years of data which could be almost the entire table. The table has a non unique clustered index.
I read about read committed isolation levels and was testing it to see how it was locking the table during selects and from my understanding it looks like it puts a series of shared page lock. Acquire shared page lock, read page, release shared page lock, move to next page and so on.
If the above is true, and lets assume the query is doing an clustered index scan, how does it handle an insert at the beginning of the table which it has already read past. What I mean by beginning of table is that since the table is having a clustered index and since it has to be ordered if an lower value index id record gets inserted then it needs to be inserted into the starting half of the leaf level of the clustered index.
January 12, 2017 at 4:40 am
jesijesijesi - Wednesday, January 11, 2017 6:14 PMAcquire shared page lock, read page, release shared page lock, move to next page and so on.
Yup, that's pretty much correct.
If the above is true, and lets assume the query is doing an clustered index scan, how does it handle an insert at the beginning of the table which it has already read past.
The row will be inserted into the table without a problem, since the scan has already gone past that point it won't see it and the newly inserted row won't appear in the output.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply