Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Transaction Isolation Level: Proper Syntax and Definitions

« Go Back

Information

 
Article Number000001326
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
How to set the SQL-92 Transaction Isolation Level?
What are the definitions of the SQL-92 Transaction Isolation Levels ?
What is the record locking scheme used by the SQL-92 Transaction Isolation Levels ?
What is the Default Isolation Level ?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
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
or
@TRANSACTIONISOLATION COMMITTED READ
or
@TRANSACTIONISOLATION REPEATABLE READ
or
@TRANSACTIONISOLATION SERIALIZABLE

In addition, the levels may be set by number (instead of by name) as follows:
 
@TRANSACTIONISOLATION 0
or
@TRANSACTIONISOLATION 1
or
@TRANSACTIONISOLATION 2
or
@TRANSACTIONISOLATION 3

where:

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 scheme

READ 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 for

Dirty 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.
Workaround
Notes
Attachment 
Last Modified Date2/6/2018 2:02 PM