tailieunhanh - Microsoft SQL Server 2008 R2 Unleashed- P146

Microsoft SQL Server 2008 R2 Unleashed- P146:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1394 CHAPTER 37 Locking and Performance equivalent to the Serializable Read isolation level. The following hypothetical example demonstrates the usage of the HOLDLOCK statement within a transaction declare @seqno int begin transaction -- get a UNIQUE sequence number from sequence table SELECT @seqno isnull seq 0 1 from sequence WITH HOLDLOCK -- in the absence of HOLDLOCK shared lock will be released -- and if some other concurrent transaction ran the same -- command both of them could get the same sequence number UPDATE sequence set seq @seqno --now go do something else with this unique sequence number commit tran NOTE As discussed earlier in this chapter in the Deadlocks section using HOLDLOCK in this manner leads to potential deadlocks between processes executing the transaction at the same time. For this reason the HOLDLOCK hint as well as the REPEATABLEREAD and SERIALIZABLE hints should be used sparingly if at all. In this example it might be better for the SELECT statement to use an update or an exclusive lock on the sequence table using the hints discussed later in this chapter in the section Lock Type Hints. Another option would be to use an application lock as discussed previously in this chapter in the section Using Application Locks. NOLOCK You can use this option to specify that no shared lock be placed on the resource. This option is similar to running a query at Isolation Level 0 Read Uncommitted which allows the query to ignore exclusive locks and read uncommitted changes. The NOLOCK option is a useful feature in reporting environments where the accuracy of the results is not critical. READUNCOMMITTED This is the same as specifying the Read Uncommitted mode when using the SET TRANSACTION ISOLATION LEVEL command and it is the same as the NOLOCK table hint. READCOMMITTED This is the same as specifying the Read Committed mode when you use the SET TRANSACTION ISOLATION LEVEL command. The query waits for exclusive locks to be released before reading the .