Table Hints

(Source: msdn.microsoft.com)

Think about using a READPAST hint. If a row or page is locked you just don’t read it. Often that is OK as those “Rows” were being used anyway. eg: in any ticketing system; Airline seats, Theatre, Hotel, where you have many customers competing for finite resources, you often know the total resources anyway. In one system we drew all the seats in a concert hall & colored them orange. Then Selected all the seats on that night with a READPAST hint. Those that were RESERVED or AVAILABLE were returned. We colored them appropriately. Any seat not returned was possibly being locked by another reservations clerk, so remained in Orange. If they hit refresh again, these seats typically turned Red (booked).

HOLDLOCK Is equivalent to SERIALIZABLE. For more information, see SERIALIZABLE later in this topic. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in. HOLDLOCK cannot be used in a SELECT statement that includes the FOR BROWSE option.

IGNORE_CONSTRAINTS Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET. By default, INSERT checks Unique Constraints and Check Constraints and Primary and Foreign Key Constraints. When IGNORE_CONSTRAINTS is specified for a bulk-import operation, INSERT must ignore these constraints on a target table. Note that you cannot disable UNIQUE, PRIMARY KEY, or NOT NULL constraints. You might want to disable CHECK and FOREIGN KEY constraints if the input data contains rows that violate constraints. By disabling the CHECK and FOREIGN KEY constraints, you can import the data and then use Transact-SQL statements to clean up the data. However, when CHECK and FOREIGN KEY constraints are ignored, each ignored constraint on the table is marked as is_not_trusted in the sys.check_constraints or sys.foreign_keys catalog view after the operation.

IGNORE_TRIGGERS Is applicable only in an INSERT statement when the BULK option is used with OPENROWSET. Specifies that any triggers defined on the table are ignored by the bulk-import operation. By default, INSERT applies triggers. Use IGNORE_TRIGGERS only if your application does not depend on any triggers and maximizing performance is important.

NOLOCK Is equivalent to READUNCOMMITTED.

PAGLOCK Takes page locks either where individual locks are ordinarily taken on rows or keys, or where a single table lock is ordinarily taken. By default, uses the lock mode appropriate for the operation. When specified in transactions operating at the SNAPSHOT isolation level, page locks are not taken unless PAGLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

READCOMMITTED Specifies that read operations comply with the rules for the READ COMMITTED isolation level by using either locking or row versioning. If the database option READ_COMMITTED_SNAPSHOT is OFF, the Database Engine acquires shared locks as data is read and releases those locks when the read operation is completed. If the database option READ_COMMITTED_SNAPSHOT is ON, the Database Engine does not acquire locks and uses row versioning.

REPEATABLEREAD Specifies that a scan is performed with the same locking semantics as a transaction running at REPEATABLE READ isolation level.

ROWLOCK Specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.

SERIALIZABLE Is equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not. The scan is performed with the same semantics as a transaction running at the SERIALIZABLE isolation level.

TABLOCK Specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.

UPDLOCK Specifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level. If UPDLOCK is combined with TABLOCK, or a table-level lock is taken for some other reason, an exclusive (X) lock will be taken instead. When UPDLOCK is specified, the READCOMMITTED and READCOMMITTEDLOCK isolation level hints are ignored.