Kbase 20255: Transaction Isolation Level: Proper Syntax and Definitions
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
19/03/2009 |
|
Status: Verified
GOAL:
How to set the TRANSACTION ISOLATION LEVELS via SQL EXPLORER and the proper syntax.
GOAL:
The definitions of the TRANSACTION ISOLATION LEVELS and an explanation of their record locking scheme.
GOAL:
What is Default Isolation Level
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
The current syntax for setting the default transaction isolation level through the SQL-92 Explorer tool that is documented in the Progress SQL-92 Guide and Reference Version V9.1A, page 3-119 does not work in SQL Explorer.
Note: The transaction isolation level must be set prior to any other transactions within the session or it will fail with syntax errors.
The Guide lists the syntax as:
SET TRANSACTION ISOLATION LEVEL UNCOMMITTED READ ( Serializable | Committed Read | Repeatable Read). The proper syntax 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 These commands only work in the SQL Explorer tool.
Note: 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 nonrepeatable 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..