Database
 

SQL Server 2008 R2 : Table Hints for Locking

1/15/2013 6:39:56 PM
You can set an isolation level for your connection by using the SET TRANSACTION ISOLATION LEVEL command. This command sets a global isolation level for an entire session, which is useful if you want to provide a consistent isolation level for an application. However, sometimes you might want to specify different isolation levels for specific queries or for different tables within a single query. SQL Server allows you to do this by supporting table hints in the SELECT, MERGE, UPDATE, INSERT, and DELETE statements. In this way, you can override the isolation level currently set at the session level.

You have seen that locking is dynamic and automatic in SQL Server. Based on certain factors (for example, SARGs, key distribution, data volume), the Query Optimizer chooses the granularity of the lock (that is, row, page, or table level) on a resource. Although it is usually best to leave such decisions to the Query Optimizer, you might encounter certain situations in which you want to force a different lock granularity on a resource than what the optimizer has chosen. SQL Server provides additional table hints that you can use in the query to force lock granularity for various tables participating in a join.

SQL Server also automatically determines the lock type (SHARED, UPDATE, EXCLUSIVE) to use on a resource, depending on the type of command being executed on the resource. For example, a SELECT statement uses a shared lock. SQL Server also provides additional table hints to override the default lock type.

The table hints to override the lock isolation, granularity, or lock type for a table can be provided using the WITH operator of the SELECT, UPDATE, INSERT, and DELETE statements.

The following sections discuss the various locking hints that can be passed to an optimizer to manage isolation levels and the lock granularity of a query.

Note

Although many of the table-locking hints can be combined, you cannot combine more than one isolation level or lock granularity hint at a time on a single table. Also, the NOLOCK, READUNCOMMITTED, and READPAST hints described in the following sections cannot be used on tables that are the target of INSERT, UPDATE, MERGE, or DELETE queries.


Transaction Isolation–Level Hints

SQL Server provides a number of hints that you can use in a query to override the default transaction isolation level:

  • HOLDLOCK— HOLDLOCK maintains shared locks for the duration of the entire statement or for the entire transaction, if the statement is in a transaction. This option is 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

    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, in the section “Lock Type Hints.” Another option would be to use an application lock.


  • 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 data. This is the default locking isolation mode for SQL Server. If the database option READ_COMMITTED_SNAPSHOT is ON, SQL Server does not acquire shared locks on the data and uses row versioning.

  • READCOMMITTEDLOCK— This option specifies that read operations acquire shared locks as data is read and release those locks when the read operation is completed, regardless of the setting of the READ_COMMITTED_SNAPSHOT database option.

  • REPEATABLEREAD— This is the same as specifying Repeatable Read mode with the SET TRANSACTION ISOLATION LEVEL command. It prevents nonrepeatable reads within a transaction and behaves similarly to the HOLDLOCK hint.

  • SERIALIZABLE— This is the same as specifying Serializable Read mode with the SET TRANSACTION ISOLATION LEVEL command. It prevents phantom reads within a transaction and behaves similarly to using the HOLDLOCK hint.

  • READPAST— This hint specifies that the query skip over the rows or pages locked by other transactions, returning only the data that can be read. Read operations specifying READPAST are not blocked. When specified in an UPDATE or DELETE statement, READPAST is applied only when reading data to identify which records to update. READPAST can be specified only in transactions operating at the Read Committed or Repeatable Read isolation levels. This lock hint is useful when reading information from a SQL Server table used as a work queue. A query using READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait for the other transactions to release their locks.

Lock Granularity Hints

You can use to override lock granularity:

  • ROWLOCK— You can use this option to force the Lock Manager to place a row-level lock on a resource instead of a page-level or a table-level lock. You can use this option in conjunction with the XLOCK lock type hint to force exclusive row locks.

  • PAGLOCK— You can use this option to force a page-level lock on a resource instead of a row-level or table-level lock. You can use this option in conjunction with the XLOCK lock type hint to force exclusive page locks.

  • TABLOCK— You can use this option to force a table-level lock instead of a row-level or page-level lock. You can use this option in conjunction with the HOLDLOCK table hint to hold the table lock until the end of the transaction.

  • TABLOCKX— You can use this option to force a table-level exclusive lock instead of a row-level or page-level lock. No shared or update locks are granted to other transactions as long as this option is in effect. If you are planning maintenance on a SQL Server table and you don’t want interference from other transactions, using this option is one of the ways to essentially put a table into a single-user mode.

Lock Type Hints

You can use the following optimizer hints to override the lock type that SQL Server uses:

  • UPDLOCK— This option is similar to HOLDLOCK except that whereas HOLDLOCK uses a shared lock on the resource, UPDLOCK places an update lock on the resource for the duration of the transaction. This allows other processes to read the information, but not acquire update or exclusive locks on the resource. This option provides read repeatability within the transaction while preventing deadlocks that can result when using HOLDLOCK.

  • XLOCK— This option places an exclusive lock on the resource for the duration of the transaction. This prevents other processes from acquiring locks on the resource.

 
Others
 
 
 
Most View
 
- Sharepoint 2013 : Managing and Configuring My Sites (part 4) - SkyDrive Pro
- Windows 7 : Windows Media Player - Taking Your Music and Video on the Go (part 2) - Syncing Files to Your Portable Media Player
- Lync Server 2013 Clients : Mac Client - Navigation and Layout (part 1)
- Microsoft OneNote 2010 : Working with Links (part 2) - Creating a Link from a Picture,Modifying a Link in Your Notes, Removing a Link from Your Notes
- Windows Server 2012 : Deploying domain controllers using Server Manager (part 2) - First domain controller in new forest
- Migrating to Exchange 2013 : Moving Mailboxes - Preparing for Inter-Org Mailbox Moves
- Application Lifecycle Management in SharePoint 2013 : Managing SharePoint 2013 Testing Teams (part 1) - Automated Builds and Integration Testing
- Using the Windows 8 Interface : Taking a Tour of the Windows 8 Interface (part 2) - The App Bar,The Charms Menu
- Windows 8 for Business : Virtualization (part 3) - Using Hyper-V Virtual Machine Connection
- Active Directory 2008 : Monitoring and Troubleshooting Active Directory Replication
 
 
Top 10
 
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 3) - Controlling Output
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 2) - PowerShell Help , PowerShell Variables
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Basic PowerShell Usage (part 1) - Listing the SharePoint Commands
- Microsoft Sharepoint 2013 : Administering Sharepoint with Windows Powershell - Commands
- Microsoft Sharepoint 2013 : Microsoft SharePoint 2013 Management Shell and Other Hosts
- Microsoft Word 2010 : Using Advanced Text Features - Addressing Envelopes
- Microsoft Word 2010 : Using WordArt (part 2) - Changing the Format of a WordArt Object
- Microsoft Word 2010 : Using WordArt (part 1) - Inserting WordArt Text
- Microsoft Word 2010 : Creating and Formatting Text Boxes (part 3) - Formatting Text in a Text Box, Linking Text Boxes
- Microsoft Word 2010 : Creating and Formatting Text Boxes (part 2) - Resizing a Text Box , Moving a Text Box