none
(readpast readcommitted) vs (readpast repeatableread) vs readpast RRS feed

  • Question

  •  

    What is the difference among them ? who can tell ?

     

    select * from table (readpast readcommitted)

    select * from table (readpast repeatableread)

    select * from table (readpast)

     

     

    I suppose they are the same as readpast.

    I cannot tell the differece.

     

    thank you

    Tuesday, September 25, 2007 6:10 PM

Answers

  • ReadPast is a hint that says your query should skip any rows that are currently locked rather than wait for the lock to be released. This means there might be some rows that you miss in your select statement.

     

    The other hints are isolation hints that say what type of locking should be done for your own transactions. ReadCommitted says you will lock the row as you read it, but then your lock will be released; this means others can read and even modify the row .. so if you were to read the row again in that transaction you might get a different value.

     

    RepeatableRead says you will lock the row as you read it and the lock will be held until you commit or rollback the transaction; if you read the row again in that transaction you'll get the same value. This can/will reduce the possible concurrency since other transactions that want to change the row will have to wait until you release your lock.

     

    ReadUncommitted and Serializable are two more isolation hints that affect what locks you pay attention to, how long your locks are held, or what range of rows are locked. Together the four isolation levels and the hints that can be specified per statement give you control of locking and allow you to finely manage the tradeoffs between concurrency and performance.

     

    Rather than specify an isolation hint at the statement level, it's typically better to determine the isolation required and use SET TRANSACTION ISOLATION LEVEL xxx so that it applies to the whole transaction. The statement isolation hint can come in handy when you generally use a looser isolation for the transaction but then use statement hints if certain statements/objects need some other isolation level for correctness.

    Tuesday, September 25, 2007 8:52 PM
  • The Readpast hint tells SQL Server to skip over rows that are currently locked with an incompatible lock to the lock you are trying to get.  Usually this means that when you are trying to get a shared lock, if another process has an exclusive lock on the row, then it won't be returned in the query.

     

    The best use for this I know of is to implement a multi-user queue or stack.  A user takes a lock on the row by initially setting a value to in progress (probably the user id is set in the row also.  While this exclusive lock is being held, other users cannot get access to the row.  So other users trying to dequeue or pop something off of the stack don't need to wait, because they can just go get a different row to work on.

     

    Repeatableread and Readcommitted are isolation level hints that control how long locks are held.

     

    In your readcommitted query, the query will process rows in the following manner:

     

    1. Get a lock on the row

    2. Place the row on the output buffer

    3. Release that lock

    4. Repeat until no more rows

     

    This process is nicknamed "crabbing" through the table, because it is kind of a sideways walk through the table.

     

    Repeatableread is the same process, except that it does not release the lock.  The goal of repeatable read is that, for the life of the transaction (and every select statement is an implicit transaction) any time you query the same data, you will get at least the same results.

     

    The locks are released at the end of the statement execution, or transaction if inside an explicit transaction. 

     

    Note that this will not prevent other users from adding rows that might match. For that you use the Serializable isolation level (or hint, as you are asking about),  It will put range locks to make sure that no new rows are introduced that would match the query.

     

     

    Wednesday, September 26, 2007 4:38 AM
    Moderator

All replies

  • to see the difference, run the following and examine the output of sp_lock in each batch. Replace dbo.Dates with your table name:

    BEGIN TRANSACTION
    select top 10 * from dbo.Dates (readpast readcommitted)
    EXEC sp_lock
    ROLLBACK
    go
    BEGIN TRANSACTION
    select top 10 * from dbo.Dates (readpast repeatableread)
    EXEC sp_lock
    ROLLBACK
    go
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    BEGIN TRANSACTION
    select top 10 * from dbo.Dates (readpast)
    EXEC sp_lock
    ROLLBACK
    go
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
    BEGIN TRANSACTION
    select top 10 * from dbo.Dates (readpast)
    EXEC sp_lock
    ROLLBACK
    go
    Tuesday, September 25, 2007 6:26 PM
  •  

    Hi AKuz, thanks very much for your reply.

    However I am .NET developer, instead of database administrator. and I do not quite understand the result of sp_lock

    Could you please give me a brief description of the difference between (readpast readcommitted) and (readpast repeatableread) ?

     

    thanks once again

    Tuesday, September 25, 2007 6:59 PM
  • basically a select with (readpast repeatableread) holds more locks than a select with (readpast readcommitted) to implement repeatable read isolation level. A select with (readpast) runs under current isolation level and holds as many lock as required by the current isolation level. You might need to read abvout isolation levels in BOL - this knowledge is essential if you work with databases.
    Tuesday, September 25, 2007 7:11 PM
  • ReadPast is a hint that says your query should skip any rows that are currently locked rather than wait for the lock to be released. This means there might be some rows that you miss in your select statement.

     

    The other hints are isolation hints that say what type of locking should be done for your own transactions. ReadCommitted says you will lock the row as you read it, but then your lock will be released; this means others can read and even modify the row .. so if you were to read the row again in that transaction you might get a different value.

     

    RepeatableRead says you will lock the row as you read it and the lock will be held until you commit or rollback the transaction; if you read the row again in that transaction you'll get the same value. This can/will reduce the possible concurrency since other transactions that want to change the row will have to wait until you release your lock.

     

    ReadUncommitted and Serializable are two more isolation hints that affect what locks you pay attention to, how long your locks are held, or what range of rows are locked. Together the four isolation levels and the hints that can be specified per statement give you control of locking and allow you to finely manage the tradeoffs between concurrency and performance.

     

    Rather than specify an isolation hint at the statement level, it's typically better to determine the isolation required and use SET TRANSACTION ISOLATION LEVEL xxx so that it applies to the whole transaction. The statement isolation hint can come in handy when you generally use a looser isolation for the transaction but then use statement hints if certain statements/objects need some other isolation level for correctness.

    Tuesday, September 25, 2007 8:52 PM
  • The Readpast hint tells SQL Server to skip over rows that are currently locked with an incompatible lock to the lock you are trying to get.  Usually this means that when you are trying to get a shared lock, if another process has an exclusive lock on the row, then it won't be returned in the query.

     

    The best use for this I know of is to implement a multi-user queue or stack.  A user takes a lock on the row by initially setting a value to in progress (probably the user id is set in the row also.  While this exclusive lock is being held, other users cannot get access to the row.  So other users trying to dequeue or pop something off of the stack don't need to wait, because they can just go get a different row to work on.

     

    Repeatableread and Readcommitted are isolation level hints that control how long locks are held.

     

    In your readcommitted query, the query will process rows in the following manner:

     

    1. Get a lock on the row

    2. Place the row on the output buffer

    3. Release that lock

    4. Repeat until no more rows

     

    This process is nicknamed "crabbing" through the table, because it is kind of a sideways walk through the table.

     

    Repeatableread is the same process, except that it does not release the lock.  The goal of repeatable read is that, for the life of the transaction (and every select statement is an implicit transaction) any time you query the same data, you will get at least the same results.

     

    The locks are released at the end of the statement execution, or transaction if inside an explicit transaction. 

     

    Note that this will not prevent other users from adding rows that might match. For that you use the Serializable isolation level (or hint, as you are asking about),  It will put range locks to make sure that no new rows are introduced that would match the query.

     

     

    Wednesday, September 26, 2007 4:38 AM
    Moderator
  • thank you all

     

    Wednesday, September 26, 2007 6:25 PM