- Tuning Queries Through Locking Hints
- Changing Locking Behavior with SET Options
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Changing Locking Behavior with SET Options
In addition to SET TRANSACTION ISOLATION LEVEL, there are several other SET options that let you customize SQL Server's locking behavior. Set options affect only a single connection; they don't alter locking on any other connections. It is usually a good idea to execute SET statements at the beginning of the session so you don't get two behaviors on the same connection. As soon as the user disconnects all the SET options go out of scope.
Table 2 lists the SET options affecting the default locking behavior, and describes each option:
Table 2 SET options affecting SQL Server locking behavior.
|
SET Option |
Description |
|
SET IMPLICIT_TRANSACTONS ON (OFF) |
This setting defaults to OFF, and determines whether certain statements should be explicitly committed or rolled back. This setting affects how SQL Server treats the following statements: ALTER TABLE TRUNCATE TABLE CREATE (any object) DELETE DROP (any object) FETCH GRANT INSERT OPEN REVOKE SELECT UPDATE Note that if the statement happens within a transaction, it does not start a new transaction. In addition, this setting has no effect on the transactions that are open when this statement is executed. |
|
SET XACT_ABORT ON (OFF) |
When this option is turned on, it automatically rolls back all transactions in a batch if any statement within the batch encounters an error. If this option is turned off (default), then only the statements within the transaction that encountered the error are rolled back while all other commands are allowed to complete. |
|
SET CURSOR_CLOSE_ON_COMMIT ON (OFF) |
This setting closes all open cursors when a transaction containing the cursor is committed or rolled back. If the setting is OFF (default), then cursors remain open and must be explicitly closed. Make a note, though, that it's a good coding practice to explicitly close and deallocate your cursors, regardless of the setting. |
|
SET DEADLOCK_PRIORITY LOW (NORMAL) |
The LOW setting advises SQL Server that the current connection should have a "better" chance of being chosen as a victim if a deadlock occurs. Otherwise, it's up to SQL Server to determine which of the connections involved in the deadlock is the "lucky" victim. The default priority is NORMAL. |
|
SET LOCK_TIMEOUT timeout_period |
This setting specifies the number of milliseconds to wait until the error is returned if the requested lock cannot be acquired. By default, SQL Server will try indefinitely. The default value of this setting is –1. The value of zero will instruct SQL Server to return an error as soon as the lock is encountered. |
|
SET REMOTE_PROC_TRANSACTIONS ON (OFF) |
Occasionally, you might have a transaction that spans multiple servers. The distributed transactions are managed by the Distributed Transaction Coordinator (MS DTC) by using a two-step process referred to as two-phase commit (2PC). Such transactions are supported only if MS DTC is running on all participating servers. Distributed transactions are difficult to debug because they go across the network and execute on multiple servers. We suggest using them sparingly. The SET REMOTE_PROC_TRANSACTIONS setting starts a distributed transaction when a remote stored procedure is executed within a local transaction. The default is OFF. |