Both Microsoft® SQL Server™ and Oracle allow the developer to request non-default locking and isolation behavior. In Oracle, the most common mechanisms for this are the FOR UPDATE clause on a SELECT statement, the SET TRANSACTION READ ONLY statement, and the explicit LOCK TABLE command.
Because their overall 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, the options that SQL Server provides for changing its default behavior must be examined.
In SQL Server, the most common mechanisms are the SET TRANSACTION ISOLATION LEVEL statement and the locking hints that are supported in SELECT and UPDATE statements. The SQL Server SET TRANSACTION ISOLATION LEVEL statement is used to set 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 default isolation level for SQL Server is READ COMMITTED (SET TRANSACTION ISOLATION LEVEL READ COMMITTED). When using this option, your application cannot read data that has not yet been committed by other transactions. In this mode, shared locks are released as soon as the data has been read from a page. If the application goes to reread the same data range within the same transaction, it may see other users’ changes.
To prevent this from occurring in your application, set the transaction isolation level to SERIALIZABLE. This option directs SQL Server to hold 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 SELECT statement. Both of these options represent a tradeoff of concurrency for strict consistency, and should be used only when necessary.
The READ UNCOMMITTED option (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) implements dirty reads, which means that no shared locks are issued and no exclusive locks are honored by readers. With this option set, SQL Server readers are nonblocking, as in Oracle. When this option is set, an application can read data that has not yet been committed by another user. For some applications, such as statistical reporting summaries, this may not be a problem. For other applications, such as a database queuing table, this may be a problem.
Use READ UNCOMMITTED isolation only after you thoroughly analyze how it may affect the correctness of your application.
SQL Server does not directly support the READ ONLY transaction level offered by Oracle. If some transactions in an application require repeatable read behavior, you may need to use the SERIALIZABLE isolation level offered by SQL Server. If all of the database access is read only, you can improve performance by setting the SQL Server database option to READ ONLY.
The SELECT...FOR UPDATE statement in Oracle is used when an application needs to issue a positioned update or delete on a cursor using the WHERE CURRENT OF syntax. In this case, optionally remove the FOR UPDATE clause; SQL Server cursors are updatable by default.
SQL server cursors usually do not hold locks under the fetched row. Rather, they use an optimistic concurrency strategy to prevent updates from overwriting each other. If one user attempts to update or delete a row that has been changed since it was read into the cursor, SQL Server detects the problem and issues an error message. The application can trap this error message and retry the update or delete as appropriate.
The optimistic technique supports higher concurrency in the normal case where conflicts between updaters are rare. If your application needs to ensure that a row cannot be changed after it is fetched, you can use the UPDLOCK hint in your SELECT statement to achieve this effect.
This hint does not block other readers, but it prevents any other potential writers from obtaining an update lock on the data as well. When using ODBC, you can also achieve a similar effect using SQLSETSTMTOPTION (...,SQL_CONCURRENCY)= SQL_CONCUR_LOCK. Both of these options reduce concurrency.