Data Management optimize

Using NOLOCK and READPAST table hints in SQL Server

Table locking hints provide developers much tighter control of their transactions. Look at the benefits and disadvantages of using the NOLOCK and READPAST table hints in SQL Server.

When data in a database is read or modified, the database engine uses special types of controls, called locks, to maintain integrity in the database. Locks basically work by making sure database records involved in a transaction cannot be modified by other transactions until the first transaction has committed, ensuring database consistency.

Get SQL tips in your inbox
TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

When designing database applications, you should keep in mind the different types of locks that will be issued, and the different levels of isolation your transactions will occur. Typically, the SQL Server defaults work fine for what you are trying to accomplish. However, there will be times when it is advantageous to manually make hints to how locks are issued on your tables in your SQL statements.

This article focuses on two table hints: NOLOCK and READPAST. I'll set up a table to use for our example queries. Execute the script in Listing A to create the SalesHistory table and populate the table with data.

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.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

23 comments
hamish.holder
hamish.holder

I have tried this in SQL 2008 R2 and observed that WITH(READPAST) spins its wheels (in a new query transaction) waiting for a COMMIT or ROLLBACK before returning, while WITH(NOLOCK) returns but with dirty data as stated. Am I doing something wrong, WITH(READPAST) does not appear to be behaving as stated?

rbhargava25
rbhargava25

Very nice article with good clarification. but I have one doubt is that when we fires query with (NOLOCK) is more risky then READPAST still we uses frequently NOLOCK table hint why? Please Suggest Rahul Bhargava

laputa92
laputa92

Really useful but...I want to select a record that between 2 function, so how...? I want to retrieve those record although someone doing update but don't want include those record that insert by someone without commit... :(

thisisfutile
thisisfutile

Tim, Thanks for the post. Question concerning the READPAST command. I?m using SQL 2000 SP4 and when I try to run your Update command it gave me a syntax error for the TOP command so I rewrote it like this: BEGIN TRANSACTION update SalesHistory SET SalePrice = SalePrice + 1 where saleid in (select top 1 saleid from saleshistory) I assume that?s an unsupported SQL 2000 thing, but that?s not my question?. This update transaction executed, but when I ran the Select statement with READPAST, I still got a count of 300. Any clues? Gabe

mgoff2
mgoff2

I am in a large shop and the omittion of (nolock) is a firing offence, well, with SQL2000. I am hearing, 'NOT SO' with SQL2005. Can a DBA type substantiate this fact?

Tony Hopkinson
Tony Hopkinson

so you get all the data but it's state could be undefined. READPAST skips locked records so any uncommitted records will not be in the result set. Risk of what is is the question that needs to be answered. If you were say returning all the orders created today, at the time there was an uncommitted transaction on one of them. READPAST would skip it, NOLOCK, you might get one that was going to be deleted, one that was have its date_created chnage to yesterday, one that was about to be rolled back. No of you were just using that list to give a say dashboard value, number of orders and total value today, once every five minutes, big deal.. If you were going to do and end of period report, or use the order key in somethng that couldn't cope with it not existing, but you still didn't want it to block, then READPAST is a better choice. Risk is down to how you use the data, not inherrent to the hint.

Tony Hopkinson
Tony Hopkinson

an update is effectively and insert and a delete so as far as the locking mechanisms go there isn't a distinction between updated and inserted. These are only hints as well, the database engine will have a go, so you can't rely on it. To get a similar effect, would be to have some field (or add an extra one) and have your inserts be an insert with the field set to new record in one transaction, and then immediately follow it with an update set flagField = 0 where flagfield is 1 and ID = ... sort of thing. If you have a time created/inserted field on the table you might be be able to limit yourself to readpast where time_created < GetDate() Another possibility is to instead of having a flag field or date, have a table for 'new' records, then in a second transaction insert them into the 'real' table. Another would be to have a date_modified field in the table, null on insert and any update transaction would set it to GetDate(). That woudl be readpast Where date_modified is not null. Messy though, very messy, bound leap out and bite your arse at inconvenient times. :p I'd be trying to design this necessity out as a matter of urgency. Hths

chapman.tim
chapman.tim

Its my fault...I didn't indicate that I was using 2005 for my example. That TOP command is a new thing in 2005...The way you rewrote it works perfectly. Thats strange that it didn't work for you. I just tried it on a 2000 machine and it worked. I suspect that for some reason the transaction that you begin is closing somehow. After you run the update statement, run this: SELECT @@TRANCOUNT. If it is 1 then try the READPAST query again. If it is 0, then that record is committing for some reason.

AshishDBA
AshishDBA

Nolocks are fine till you are not worried with dirty reads, it anyhow increases your query performance, but avoid when dirty reads may creat caos. Try to set condition such that dirty read may not effect. you can also go with readpast , the difference is readpast does not even consider those row which have locks, so there are no dirty read , but then there is a fear of not getting the complete data.

chapman.tim
chapman.tim

That seems a bit like an extreme, especially since it doesn't make sense to always use it. In 2005 there are new isolation levels called SNAPSHOT ISOLATION AND READ COMMITTED SNAPSHOT. The way they work is that a version store is kept for past values for transactions, so that no shared locks are kept on reads. This was a really broad explanation. For something more thorough, see: http://msdn2.microsoft.com/en-us/library/ms345124.aspx

nickpixel
nickpixel

...and kind of moot now that the cause of the issue is known but we have one of our 2000 db's running in 6.5 compatability mode (please don't ask why) and many features introduced in 2000 (like the "TOP" keyword) don't work.

thisisfutile
thisisfutile

That's exactly what it's doing. Instead of selecting TOP 1, I specifically selected SaleId = 1 so that I could track a specific record. The result from these queries: select saleprice from saleshistory where saleid = 1 BEGIN TRANSACTION update SalesHistory SET SalePrice = SalePrice + 1 where saleid = 1 select @@trancount select saleprice from saleshistory where saleid = 1 select count(*) from saleshistory with(readpast) Was this: 1005 1 1006 300 If I run them again: 1006 2 1007 300 Why do you suppose the transaction is committing?

chapman.tim
chapman.tim

Its very neat and useful, although the db will be a little bit slower with the row versioning because it has to maintain those versions in tempdb.

chapman.tim
chapman.tim

Great, I'm glad to hear you've learned something new. In my next article I am going to dig a little bit deeper and look at other locking hints.

thisisfutile
thisisfutile

I should probably report that the Begin Transaction is working properly and I should have mentioned this in my last post. Even though the SalePrice is reflecting the increase, when I ROLLBACK Transaction and look at the saleprice again it goes back to the value before the BEGIN Transaction command...as it should. I re-read your original post and see that I missed your request to ?open a new query editor window?. I did not do that the first time and thus the problem. I ran another Begin Transaction, Update and then opened a new window for a new connection and the results are correct. It shows 299. I did however run the NOLOCK command against the Insert transaction in the same window and got the desired result of 301. I thought that was interesting. Something else interesting?if you run the select count(*) command withOUT the READPAST command, the query runs forever (I stopped it after 2 minutes). Sorry for all of the confusion. Thanks for all the follow-up because I?ve learned something. Gabe

chapman.tim
chapman.tim

Are you making sure that you are running the select vs update statements in different connection windows?

Hammer71
Hammer71

Hi, It working for me as advertised in SQL Server 2000. (Besides the TOP bit which I figured must be 2005 syntax). When I do the readpast hint I get 299 rows on my select statement. Would be interested if you come up with the answer to what's happening in your environment.

Tony Hopkinson
Tony Hopkinson

with only one server service started. Ran the query 1000 - times with a randomly generated key between 1 and a thousand in table with two columns, with an integer primary key of 1 to a 1000. Disabled 2000, enabled 2005, restart and run the wee bench mark app again. I'd already had a report from another division that it was slower, also system architect said his tests with a full application showed it was slower. Based on their investigations I narrowed it down and got some figures so I've something to measure against. The was using straight ADO from Delphi, which is what our systems use currently. As for caching another exercise where I ran the app more than once showed minmial variation. I like 2005, but there's no getting away from it, it's slower, for adhoc queries. There was no point in weighting the test. SQL 2005 is happening if only for express installs on Vista (can't use MSDE). I will be one of the ones doing the work to make the new version perform as well and with duff data I'd be looking in the wrong place. Should add preparing the query did have a significant impact, but of pretty much the same level on both server versions.

sjcsystltd
sjcsystltd

I don't mean this in a condescending way, but you said you rebooted? You did do that for both 2000 and 2005 didn't you ? The danger being that the data would not have been in cache, so it's bound to be slower on first access. Quite why SQL can't examine and log what was in it's cache on shutdown and then reload that on startup I don't know.

Tony Hopkinson
Tony Hopkinson

but they are more like best fit wizards. I can live with failed to convert, it's made a few assumptions and wild guess and did it successfully that gives me the shivers. :( To / from Oracle is a nightmare, becasue of the no DML in PL/SQL Can't even do If Exists() Drop, It was the same machine, I even shut the other server instance down and rebooted in between tests. SQL 2005 treats adhoc queries as exactly that, the definition of adhoc is not encapsulated in the database. The price of all the extras had to be paid somewhere. Use SPS or your own .net functions and it's as fast as 2000 even with the added overhead

chapman.tim
chapman.tim

...thats interesting. I haven't seen that yet (and I hope that I don't). Are the machines that you are using to compare identical machines? I like stored procs too, but haven't been in a shop that wants to be able to switch back ends like that. I wonder if there are any good conversion tools out there that can do that type of stuff for you....like TSQL sprocs to Oracle SQL sprocs?

Tony Hopkinson
Tony Hopkinson

1000 select statements same machine, same data, same code prepared or unprepared 30% slower Two columns, integer primary key , selecting one row by the key!. I gave it every chance to look good, it failed miserably. I like stored proc designs, the company wants to be able to switch back ends though, so that's out of the question, they are too propriety.