The Transaction Isolation Level must be set prior to any other transactions within the session.
The proper syntax to set the Default Isolation Level in SQL Explorer is as follows:
@TRANSACTIONISOLATION UNCOMMITTED READ
@TRANSACTIONISOLATION COMMITTED READ
@TRANSACTIONISOLATION REPEATABLE READ
In addition, the levels may be set by number (instead of by name) as follows:
0 = Uncommitted Read
1 = Committed Read
2 = Repeatable Read
3 = Serializable
You can also set the Transaction Isolation Level in the ODBC Data Source Administrator in the Advanced tab.The Transaction Isolation Level definitions and record locking schemeREAD UNCOMMITTED
Allows dirty reads, nonrepeatable reads, and phantoms. Allows a user application to read records that were modified by other applications but have not yet been committed.
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.
Record Locking scheme: It ensures that the when a record is read, no record lock are acquired.READ COMMITTED
Prohibits dirty reads; allows nonrepeatable reads and phantoms. Disallows the reading of uncommitted modified records. However, if a record is read multiple times in the same transaction, the state of the record can be different each time.
Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatable reads or phantom data.
Record Locking Scheme: It ensures that when a record is read a share lock is acquired on that record; the duration of the lock varies.REPEATABLE READ
Prohibits dirty reads and non-repeatable reads; allows phantoms. Disallows the reading of uncommitted modified records. If a record is read multiple times in the same transaction, the state of the record remains the same.
Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.
Note: REPEATABLE READ is the default isolation level.
Record Locking Scheme: It ensures that when a record is read, a share lock is acquired on that record and held until the end of the current transaction.SERIALIZABLE
Prohibits dirty reads, nonrepeatable reads, and phantoms. If an application executes the same SELECT statement more than once within the same transaction, the same set of rows is retrieved every time. Guarantees the concurrent transactions will not affect each other, and that they will behave as if they were executing serially, not concurrently.
Places a range lock on the data s.et, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Record Locking Scheme: It ensures that when a table is accessed the entire table is locked with a lock of appropriate strength; the lock is held until the end of the transaction.What does Dirty read, Nonrepeatable read, and Phantom stand forDirty read
Allows the transaction to read a row that has been inserted or modified by another transaction, but not committed. If the other transaction rolls back its changes, the transaction will read a row that never existed because it never committed.Nonrepeatable read
Allows the transaction to read a row that another transaction modifies or deletes before the next read operation. If the other transaction commits the change, the transaction receives modified values or discovers the row is deleted on subsequent read operations.Phantom
Allows the transaction to read a range of rows that satisfies a given search condition but to which another transaction adds rows before another read operation using the same search condition. The transaction receives a different collection of rows with the same search condition.