Solved

Readpast Vs No Lock

Posted on 2010-11-29
3
742 Views
Last Modified: 2012-05-10
What is the difference between readpast and no lock
0
Comment
Question by:KaranGupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 12

Accepted Solution

by:
udaya kumar laligondla earned 250 total points
ID: 34236595
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 250 total points
ID: 34236755
Hi,

NOLOCK

This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table.

The benefit of the statement is that it allows you to keep the database engine from issuing locks against the tables in your queries; this increases concurrency and performance because the database engine does not have to maintain the shared locks involved. The downside is that, because the statement does not issue any locks against the tables being read, some "dirty," uncommitted data could potentially be read. A "dirty" read is one in which the data being read is involved in a transaction from another connection. If that transaction rolls back its work, the data read from the connection using NOLOCK will have read uncommitted data. This type of read makes processing inconsistent and can lead to problems. The trick is being able to know when you should use NOLOCK.

As a side note, NOLOCK queries also run the risk of reading "phantom" data, or data rows that are available in one database transaction read but can be rolled back in another. (I will take a closer look at this side effect in part two of this article series.)

The following example shows how NOLOCK works and how dirty reads can occur. In the script below, I begin a transaction and insert a record in the SalesHistory table.

BEGIN TRANSACTION
      INSERT INTO SalesHistory
      (Product, SaleDate, SalePrice)          
      VALUES            
      ('PoolTable', GETDATE(), 500)                  

The transaction is still open, which means that the record that was inserted into the table still has locks issued against it. In a new query window, run the following script, which uses the NOLOCK table hint in returning the number of records in the SalesHistory table.

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

The number of records returned is 301. Since the transaction that entered the record into the SalesHistory table has not been committed, I can undo it. I'll roll back the transaction by issuing the following statement:

ROLLBACK TRANSACTION

This statement removes the record from the SalesHistory table that I previously inserted. Now I run the same SELECT statement that I ran earlier:

SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK)

This time the record count returned is 300. My first query read a record that was not yet committed -- this is a dirty read.

READPAST

This is a much less commonly used table hint than NOLOCK. This hint specifies that the database engine not consider any locked rows or data pages when returning results.

The advantage of this table hint is that, like NOLOCK, blocking does not occur when issuing queries. In addition, dirty reads are not present in READPAST because the hint will not return locked records. The downside of the statement is that, because records are not returned that are locked, it is very difficult to determine if your result set, or modification statement, includes all of the necessary rows. You may need to include some logic in your application to ensure that all of the necessary rows are eventually included.

The READPAST table hint example is very similar to the NOLOCK table hint example. I'll begin a transaction and update one record in the SalesHistory table.

BEGIN TRANSACTION
      UPDATE TOP(1) SalesHistory
      SET SalePrice = SalePrice + 1

Because I do not commit or roll back the transaction, the locks that were placed on the record that I updated are still in effect. In a new query editor window, run the following script, which uses READPAST on the SalesHistory table to count the number of records in the table.

SELECT COUNT(*)

FROM SalesHistory WITH(READPAST)

My SalesHistory table originally had 300 records in it. The UPDATE statement is currently locking one record in the table. The script above that uses READPAST returns 299 records, which means that because the record I am updating is locked, it is ignored by the READPAST hint.


Source - http://blog.csdn.net/envykok/archive/2010/10/24/5962894.aspx



Please check above links.There are many type of locks described.
0
 

Author Closing Comment

by:KaranGupta
ID: 34245135
thanks
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question