I was tasked with re-designing an Enterprise Date-warehouse in SQL Server 2008 and wanted to get a right estimate of things probably possible including the frequent inserts and updates along with the REPORTING Selects to be happening on the DATABASE.
I was keeping a keen eye on the locking scenario as there was a vast possibility of Locking-deadlocking scenario and as i was going through the basics once again and wanted to use HINTS, wherever possible. I wanted to remain this as a reminder and also share this information with future audience. This was taken from SQL Server Performance Tuning – Technical Reference.
| HOLDLOCK | This hint tells SQL Server to hold a shared lock until the transaction is completed instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE. HOLDLOCK can negatively affect concurrency of your system. No other concurrent transaction can access the locked table. |
| NOLOCK | Equivalent to READUNCOMMITTED hint. The NOLOCK hint tells SQL Server not to issue shared locks and not to honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. This table-locking hint applies only to the SELECT statement. |
| PAGLOCK | This will use a PAGE Lock rather than a table Lock, which would normally be used. |
| READCOMMITTED | In this type of LOCK, SQL Server will perform a scan with the same locking semantics as a transaction running at the Read committed isolation level. By default, SQL Server operates at this ISOLATION Level. |
| READPAST | This will skip any locked rows SQL Server will encounter. This option causes a transactions to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than to block while waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED ISOLATION and ready only past ROW-LEVEL locks. Applies only to SELECT statements. |
| READUNCOMMITTED | Equivalent to NOLOCK hint |
| REPEATABLEREAD | This will perform a SCAN with the same locking semantics as a transaction running at the Repeatable Read isolation level. |
| ROWLOCK | The ROWLOCK table-locking hint tells SQL Server to use ROW-LEVEL locks rather than coarser-grained page level and table-level locks. |
| SERIALIZABLE | This is Equivalent to HOLDLOCK. This tells SQL Server to perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE ISOLATION LEVEL. |
| TABLOCK | The TABLOCK table-locking hint tells SQL Server to use a table lock rather than using finer-grained row-level or page-level locks. If you specify HOLDLOCK, the table lock is held until the end of the transaction. |
| TABLOCKX | The TABLOCKX table-locking hint tells SQL Server to use an exclusive lock on a table. This lock prevents other transactions from reading or updating the table and is held until the end of the statement or transaction. |
| UPDLOCK | This tells SQL Server to use UPDATE Locks instead of SHARED locks, while reading the table. This holds locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to READ date (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it. |
| XLOCK | This causes a EXCLUSIVE lock, to be set on all data affected by the transaction. XLOCK can be used with PAGLOCK or TABLOCK. XLOCK is a great way to prevent DEADLOCKING if you apply this at the BEGINNING of the TRANSACTION. |
Loading ...