LOCK Comparizon : NOLOCK vs. READPAST vs. ROWLOCK vs. UPLOCK vs. HOLDLOCK

1. TABLOCK VS. HOLDLOCK

SELECT
 
*
 
FROM
 
table
 
WITH
 (
HOLDLOCK
) 其他事务可以读取表，但不能更新删除 





SELECT
 
*
 
FROM
 
table
 
WITH
 (TABLOCKX) 其他事务不能读取表,更新和删除
 




2. NOLOCK VS. UPLOCK 

http://www.mssqltips.com/tip.asp?tip=1257

The UPDLOCK hint tells the SQL Server query engine “Don’t allow any other reader of this row to acquire an UPDLOCK (“U” lock) because I will be promoting this lock to an exclusive “X” lock later in my processing”.

from envykok:

- the row i selected no allowed to be modify by others guys because i will promote modify later .

3. NOLOCK VS. ROWLOCK

Diff:

• NOLOCK only use in SELECT Statement
• ROWLOCK can be used in SELECT, UPDATE, DELETE
  

http://www.sql-server-performance.com/articles/per/lock_contention_nolock_rowlock_p2.aspx

Using NOLOCK

Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk. 

For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique. But I think you'll find that for better than 90% of your application, it would not be that big of a deal if a user (or even intermediate code) saw an uncommitted modification. In fact, you'll probably find that most of your data never or only very rarely changes, in which case the overhead of locking the data is almost always completely wasted. 

For example, if I want to count all users that joined Streamload.com between June 1 and August 31 of Y2K, there's no reason for me to lock anything: that number was cast in stone the moment September 1, 2000 rolled around. Another example is the file listings you see on Streamload.com: it doesn't much matter if you don't see the exact perfect data, since either you don't own the data and it doesn't much matter what you see, or you do own the data and you know perfectly well whether you just modified the data or not and whether new files have finished uploading. 

Just don't use this type of data as the basis for modifications to the database, and don't use it when it's really important that the user not see the wrong thing (an account statement or balance, for instance). 

Using ROWLOCK

Using ROWLOCK politely asks SQL Server to only use row-level locks. You can use this in SELECT, UPDATE, and DELETE statements, but I only use it in UPDATE and DELETE statements. You'd think that an UPDATE in which you specify the primary key would always cause a row lock, but when SQL Server gets a batch with a bunch of these, and some of them happen to be in the same page (depending on this situation, this can be quite likely, e.g. updating all files in a folder, files which were created at pretty much the same time), you'll see page locks, and bad things will happen. And if you don't specify a primary key for an UPDATE or DELETE, there's no reason the database wouldn't assume that a lot won't be affected, so it probably goes right to page locks, and bad things happen. 

By specifically requesting row-level locks, these problems are avoided. However, be aware that if you are wrong and lots of rows are affected, either the database will take the initiative and escalate to page locks, or you'll have a whole army of row locks filling your server's memory and bogging down processing. One thing to be particularly aware of is the "Management/Current Activity" folder with Enterprise Manager. It takes a long time to load information about a lot of locks. The information is valuable, and this technique is very helpful, but don't be surprised if you see hundreds of locks in the "Locks/Processes" folder after employing this technique. Just be glad you don't have lock timeouts or deadlocks. 

Additional Note

I get the sense that SQL Server honors NOLOCK requests religiously, but is more discretional with ROWLOCK requests. You can only use NOLOCK in SELECT statements. This includes inner queries, and the SELECT clause of the INSERT statement. You can and should use NOLOCK in joins, for example:

SELECT COUNT(Users.UserID)
FROM Users WITH (NOLOCK)
JOIN UsersInUserGroups WITH (NOLOCK) ON 
Users.UserID = UsersInUserGroups.UserID 

4. NOLOCK Vs. READPAST

Diff:

• NOLOCK : Read Uncommitted record
  
• READPAST : Ignore Uncommitted record

Same: only use for SELECT Statment

http://articles.techrepublic.com.com/5100-10878_11-6185492.html

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.

Others: