SET_ISOLATION_LEVEL¶

This command sets the locking and row versioning behavior for SQL statements. See the options below for information on the various effects they can have on transactions.

DEFAULT - The default isolation level for the server (READ_COMMITTED for MSSQL).

READ_COMMITTED - This option is used to prevent dirty reads. A dirty read is when one transaction reads data from another transaction that has yet to be committed. If one transaction fails, the other now is dealing with invalid data. By setting the isolation level to READ_COMMITTED, dirty reads will be avoided by not allowing transactions to deal with any other data that has not been committed yet. However, in between statements within the current transaction, data can be changed by other transactions. This can result in phantom data or non-repeatable reads. Note that this can slow down performance speeds but is usually good practice.

READ_UNCOMMITTED - This option is the least restrictive isolation level. With this option, dirty reads are allowed, thus no exclusive locks are honored and modified data from one uncommitted transaction can be read by other transactions.

REPEATABLE_READ - This option is similar to the READ_COMMITTED option in that locks all placed on all transactions and uncommitted data from other transactions cannot be read, preventing dirty reads. However, unlike READ_COMMITTED, new rows that are added by other transactions can be read by the current transaction if it is retried, possibly resulting in phantom reads. This option should not be used unless there is a specific reason to do so.

SERIALIZABLE - This option is the most restrictive isolation level. With this option, no interaction is allowed between transactions until they have been committed. Similar to READ_COMMITTED, no transaction can deal with any uncommitted data. On top of that though, range locks are placed on all rows being modified until the current transaction is complete. Because concurrency is so low with this option, performance speeds can be much lower and so this should only be used when there is a specific reason to do so.