Feedback
Did this article resolve your question/issue?

   

Article

Transaction Isolation Level: Proper Syntax and Definitions

« Go Back

Information

 
TitleTransaction Isolation Level: Proper Syntax and Definitions
URL Name20255
Article Number000173331
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
Last Modified Date2/6/2018 2:02 PM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.