sql server: readpast and nolock

enrique_aeo
enrique_aeo used Ask the Experts™
on
hi Experts, could help me with a real example (code transact sql) to understand the difference between the readpast and nolock
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Yust read books online
http://msdn.microsoft.com/en-us/library/ms187373.aspx

The important parts for you

"READPAST
Specifies that the Database Engine not read rows that are locked by other transactions
..For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5.

NOLOCK
Is equivalent to READUNCOMMITTED.

READUNCOMMITTED
Specifies that dirty reads are allowed.  Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions.
"

=> same situation as
 SELECT * FROM T1 WITH (READUNCOMMITTED )
wil give you 1, 2, 8, 4, 5  and you won't be sure that the 8 will be commited

Author

Commented:
sorry, but still do not understand since both allow me to read data that is not confirmed
can you explainme a little more

Commented:
<<but still do not understand since both allow me to read data that is not confirmed>>
Readpast won't read uncommited data, it will skip it. That's why in the example both old value 3 as new value 8 are not in return-list.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial