Kbase 34501: How Transactions work in Progress
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
Solution ID: P4501
GOAL:
How Transactions work in Progress
GOAL:
This solution explains how PROGRESS transactions, record scope and record-locking work within a multi-user application.
Understanding how PROGRESS treats transactions by default, how you can modify default transaction behavior and the impact of transactions on other users is essential to the integrity of your database.
FIX:
Transactions
---------------
A transaction is a set of changes to the database which should be done completely or not at all. A transaction can be as small as updating or adding a single order with the debits and credits or as large as increasing the prices of all items in an inventory by 10%. What is common between these two examples is that if not finished completely there could be a loss of data integrity.
With PROGRESS if an error occurs while processing a transaction, the transaction is backed out. This means that every record that was changed during the transaction is restored to the value it contained before the transaction started.
In PROGRESS a transaction is one iteration of the outermost FOR EACH, REPEAT, or procedure block that contains direct updates to the database.
The following will start a transaction:
o Any block using the TRANSACTION keyword on the block
statement (DO, FOR EACH, or REPEAT).
o A procedure and each iteration of a DO ON ERROR, FOR EACH, or
REPEAT block that directly updates the database or directly
reads records with an EXCLUSIVE-LOCK.
Directly updating the database means that the block contains
at least one statement that can change the database. CREATE
DELETE, and UPDATE are examples.
note: DO blocks do not by default have transaction properties.
An important thing to remember when determining the size of a transaction is that if a transaction is active when a procedure (a program started with the run statement) is called, the scope of the transaction is defined by the calling program.
Transactions can start by default or they can be explicitly with the TRANSACTION keyword.
By using the TRANSACTION keyword it is possible to control the size of a transaction, to make it smaller or bigger to meet the demands of the application.
Record Scope
----------------
When a FIND or a FOR EACH statement reads a record, it places that record in an area of memory called the record buffer. The record is available in the buffer during the scope of the record, which is the duration of that block.
By default, the scope of a record is the outermost FOR EACH, REPEAT, or procedure block that references the record. Use the COMPILE statement with the LISTING option to see where your record buffers are scoped.
PROGRESS automatically scopes records to REPEAT, FOR EACH, or procedure blocks. You can scope a record explicitly to REPEAT, and DO blocks using the FOR keyword.
Record scope affects several things:
o At the end of a record's scope, if it has been modified it is
written out to the database.
o At the end of the record scope PROGRESS releases the record and
any locks that were placed on it, so long as it is not involved
in an active transaction.
o At the end of the record scope validation is performed against
unique indexes and mandatory fields.
Record Locking
-----------------
By default whenever PROGRESS reads a record it is read with a SHARE-LOCK. This means that other users may read the record but may not update it until the SHARE-LOCK is released. If you try to update a record that is SHARE-LOCKED you receive a message that it is in use.
By default when a record is updated PROGRESS puts an EXCLUSIVE-LOCK on the record. Other users cannot read or update the record until the EXCLUSIVE-LOCK is removed. When you try to EXCLUSIVE-LOCK a record that currently has a SHARE-LOCK or an EXCLUSIVE-LOCK you will get a message that the record is in use.
You may override the default record locking by specifying NO-LOCK or EXCLUSIVE-LOCK when reading the record.
The duration of a lock depends on the type of lock and when it was acquired.
A SHARE-LOCK acquired during a transaction is held until the end of the transaction or the record release whichever happens later. A SHARE-LOCK acquired outside a transaction is held until the record release. A SHARE-LOCK acquired outside a transaction but held into a transaction is held until. the later of the end of the transaction or the record release.
An EXCLUSIVE-LOCK is only acquired during a transaction, and is held until the end of the transaction. It is then converted to a SHARE-LOCK if the record scope is larger than the transaction and the record is still active in a buffer.
Notice that when an EXCLUSIVE-LOCK is downgraded it is to a SHARE-LOCK even if the record was first read with a NO-LOCK. This is because when the NO-LOCK is upgraded to a EXCLUSIVE-LOCK it must be reread from the database before the lock is applied. If you want to override the SHARE-LOCK in the case where the transaction has ended but the record scope has not, use the RELEASE statement to release the record from the record buffer.
Record release occurs at the end of the record scope, or when a RELEASE statement is executed.
PROGRESS uses a lock table in shared memory to manage record locks. In version 5 of PROGRESS whenever a record is read, an entry is made in the lock table. This includes records read EXCLUSIVE-LOCK, SHARE-LOCK, and NO-LOCK. In PROGRESS version 6 no entries are made for records read NO-LOCK. The size of the lock table defaults to 500 entries and can be adjusted by using the -L parameter when starting the server..