Changing Default Locking Behavior

Both Microsoft SQL Server and Oracle allow the developer to request nondefault locking and isolation behavior. In Oracle, the most common mechanisms for this are the FOR UPDATE clause on a SELECT command, the SET TRANSACTION READ ONLY command, and the explicit LOCK TABLE command.

Because their locking and isolation strategies are so different, it is difficult to map these locking options directly between Oracle and SQL Server. To obtain a better understanding of this process, it is important to understand the options that SQL Server provides for changing its default locking behavior.

In SQL Server, the most common mechanisms for changing default locking behavior are the SET TRANSACTION ISOLATION LEVEL statement and the locking hints that are supported in the SELECT and UPDATE statements. The SET TRANSACTION ISOLATION LEVEL statement sets transaction isolation levels for the duration of a user’s session. This becomes the default behavior for the session unless a locking hint is specified at the table level in the FROM clause of an SQL statement. The transaction isolation is set like this: 

SET TRANSACTION ISOLATION LEVEL 

    {

        READ COMMITTED 

        | READ UNCOMMITTED 

        | REPEATABLE READ 

        | SERIALIZABLE

    } 

  
READ COMMITTED
Is the default isolation level for SQL Server. When you use this option, your application cannot read data that has not yet been committed by other transactions. In this mode, however, shared locks are released as soon as the data has been read from a page. If the application rereads the same data range within the same transaction, it sees other users’ changes.
SERIALIZABLE
With this option set, transactions are isolated from one another. If you do not want to see other user’s changes during a query, set the transaction isolation level to SERIALIZABLE. SQL Server holds all shared locks until the end of a transaction. You can achieve the same effect on a more granular level by using the HOLDLOCK hint after the table name in the SELECT statement. Either of these options represents a trade-off of concurrency for strict consistency, and should be used only when necessary.
READ UNCOMMITTED
With this option set, SQL Server readers are nonblocking, as in Oracle. This option implements dirty read or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels. Use this isolation level only after you thoroughly analyze how it affects the accuracy of the results in your application.

SQL Server supports Oracle READ ONLY functionality in two ways: