Kbase P10855: Where do I get the SQL-92 white paper that has more information on SQL-92 locking?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/11/2005 |
|
Status: Verified
GOAL:
Where do I get the SQL-92 white paper that has more information on SQL-92 locking?
FIX:
This information is available in our online White Paper section at http://psdn.progress.com and also in our online documentation at http://www.progress.com/support in the "OpenEdge Data Management: SQL Development" manual and for Progress 9.1x in the "SQL-92 Guide and Reference" manual. Hereby an excerpt from the documentation:
SET TRANSACTION ISOLATION LEVEL Statement
Explicitly sets the isolation level for a transaction. Isolation levels specify
the degree to which one transaction can modify data or database objects in use
by another concurrent transaction.
SYNTAX
SET TRANSACTION ISOLATION LEVEL isolation_level_name ;
isolation_level_name:
SYNTAX
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
READ UNCOMMITTED
Allows dirty reads, nonrepeatable reads, and phantoms. When a record is read, no record locks are acquired. This forces read-only use. Allows a user application to read records that were modified by other applications but have not yet been committed.
READ COMMITTED
Prohibits dirty reads; allows nonrepeatable reads and phantoms. Whenever a record is read, a share lock is acquired on that record. The duration of the lock varies. 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.
REPEATABLE READ
Prohibits dirty reads and nonrepeatable reads; allows phantoms. Whenever a record is read, a share lock is acquired on that record and held until the end of the current transaction. 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.
REPEATABLE READ is the default isolation level.
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 that concurrent transactions will not affect each other, and that they will behave as if they were executing serially, not concurrently.
Whenever a table is accessed, the entire table is locked with an appropriate lock. The table lock is held until the end of the current transaction.
NOTES
Progress Software recommends that you specify the transaction isolation level number by isolation_level_name.
See the LOCK TABLE Statement for information on record locking schemes used by
each isolation level.
AUTHORIZATION
None
SQL COMPLIANCE
SQL-92. The semantics to which it corresponds are standard.
The isolation level SERIALIZABLE guarantees the highest consistency. The isolation level READ UNCOMMITTED guarantees the least consistency. The default isolation level is REPEATABLE READ, which prohibits non-repeatable read operations. The ANSI/ISO SQL standard defines isolation levels in terms of the inconsistencies they allow:
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..