Locking in the Microsoft SQL Server Interface

The following LIBNAME and data set options let you control how the Microsoft SQL Server interface handles locking. For general information about an option, see LIBNAME Options for Relational Databases.

READ_LOCK_TYPE= ROW | TABLE | NOLOCK

UPDATE_LOCK_TYPE= ROW | TABLE | NOLOCK

READ_ISOLATION_LEVEL= S | RR | RC | RU | V

The Microsoft SQL Server ODBC driver manager supports the S, RR, RC, RU, and V isolation levels, as defined in this table.

Isolation Levels for Microsoft SQL Server

Isolation Level	Definition
S (serializable)	Does not allow dirty Reads, nonrepeatable Reads, or phantom Reads.
RR (repeatable read)	Does not allow dirty Reads or nonrepeatable Reads; does allow phantom Reads.
RC (read committed)	Does not allow dirty Reads or nonrepeatable Reads; does allow phantom Reads.
RU (read uncommitted)	Allows dirty Reads, nonrepeatable Reads, and phantom Reads.
V (versioning)	Does not allow dirty Reads, nonrepeatable Reads, or phantom Reads. These transactions are serializable but higher concurrency is possible than with the serializable isolation level. Typically, a nonlocking protocol is used.

Here is how the terms in the table are defined.

Dirty read

A transaction that exhibits this phenomenon has very minimal isolation from concurrent transactions. In fact, the transaction can see changes that are made by those concurrent transactions even before they commit.

For example, if transaction T1 performs an update on a row, transaction T2 then retrieves that row, and transaction T1 then terminates with rollback. Transaction T2 has then seen a row that no longer exists.

Nonrepeatable read

If a transaction exhibits this phenomenon, it is possible that it might read a row once and, if it attempts to read that row again later in the course of the same transaction, the row might have been changed or even deleted by another concurrent transaction. Therefore, the read is not necessarily repeatable.

For example, if transaction T1 retrieves a row, transaction T2 updates that row, and transaction T1 then retrieves the same row again. Transaction T1 has now retrieved the same row twice but has seen two different values for it.

Phantom reads

When a transaction exhibits this phenomenon, a set of rows that it reads once might be a different set of rows if the transaction attempts to read them again.

For example, transaction T1 retrieves the set of all rows that satisfy some condition. If transaction T2 inserts a new row that satisfies that same condition and transaction T1 repeats its retrieval request, it sees a row that did not previously exist, a phantom.

UPDATE_ISOLATION_LEVEL= S | RR | RC | V

The Microsoft SQL Server ODBC driver manager supports the S, RR, RC, and V isolation levels that are defined in the preceding table.