sql server: readpast and nolock

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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yust read books online

The important parts for you

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.

Is equivalent to 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
wil give you 1, 2, 8, 4, 5  and you won't be sure that the 8 will be commited


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

<<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