This is a dumb question. I was wondering how sql server works in this situation. Lets say I am selecting from a table ( select * from tab) and the table is having hundreds of rows getting inserted per second, how does sql server know when to stop reading the rows?

Edit: Just to add, I did a select on a 200Gb table and monitored the locks on another session with sp_lock and saw that sql server was grabbing page shared lock on one page at a time and reading data. So wondering how does it know when to stop? Does sql server take into consideration when the select started and select only the pages that are committed when the transaction starts?

  • Commit would be on the row level. Don't know the answer but if you had a sort it would have to lock it down to rows committed at time the query began. – paparazzo Aug 29 '16 at 9:46
  • The statement sees a "snapshot" of the table as it was when the statement started (assuming at least read committed isolation level). This is part of the ACID principle (also know as "statement level read consistency" in other DBMS) – a_horse_with_no_name Aug 29 '16 at 11:46

It depends on transaction isolation level.

Your class of problem is called "phantom read", and will only be circumvented when your transaction isolation level is "serializable" or above. SQL Server has a default level of "read comitted", so you can read "too few" rows.

See https://en.wikipedia.org/wiki/Isolation_(database_systems)#Phantom_reads .

  • I don't think OP is asking about 2 selects in a transaction – paparazzo Aug 29 '16 at 9:43
  • Yeah, not talking about two select. Just one select in readcommitted transaction. – carmans Aug 29 '16 at 9:49

Have a look at 'Snapshot Isolation' here - https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.