Activates transaction management
Definition
A lower-level DBH option of SYSTEM-STRATEGIES
Function
Transaction management coordinates concurrent database accesses. In the event of an error, it guarantees data consistency by means of rollback mechanisms.
Every DBH session runs with transaction management.
The DBH option TRANSACTION-SECURITY allows you to modify the rollback criteria for transactions locking other transactions to suit the requirements of the DBH session.
You can change the LOCK-TIME, INACTIVITY-TIME and LOCK-ESCALATION operands during the DBH session by using the MODIFY-TRANSACTION-SECURITY administration statement (see "MODIFY-TRANSACTION-SECURITY").
You can adjust the values of the MAX-ISOLATION-LEVEL operand during the DBH session by means of the RECONFIGURE-DBH-SESSION administration statement (see "RECONFIGURE-DBH-SESSION").
TRANSACTION-SECURITY | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
1For reasons of compatibility, the value TRANSACTION-SECURITY = *NO of SESAM/SQL < V7.0 can still be specified. However, it is ignored. A warning is issued. TRANSACTION-SECURITY = *YES(...) is always used.
Operands
TRANSACTION-SECURITY = *YES(...)
The DBH always runs with transaction management.
LOCK-TIME = 4 / <integer 1..999>
Specifies the number of minutes after which an inactive transaction locking other transactions is to be rolled back. The default is 4 minutes.
INACTIVITY-TIME = *STD / <integer 1..999>
Specifies the number of minutes after which an open but inactive transaction is to be rolled back. The value for INACTIVITY-TIME must be greater than or equal to that for LOCK-TIME. The default value is 10 times the value for LOCK-TIME.
MAX-ISOLATION-LEVEL = *STD / *REPEATABLE-READ
The maximum isolation level for access to user tables is defined. If *STD is specified, access occurs under the isolation level SERIALIZABLE.
If you specified the value *REPEATABLE-READ, all attempts of applications to access data under the isolation level SERIALIZABLE are rejected with SQLSTATE in the DBH session.
If *REPEATABLE-READ is specified, no transaction locks are used by the DBH on index values, provided the index is not a unique index nor an index of a reference condition.
If an SQL statement is to work with a DBH for which the value *REPEATABLE-READ is specified for MAX-ISOLATION-LEVEL, you must either execute a SET TRANSACTION in the application before each transaction, or set the option ISOL-LEVEL=REPEATABLE-READ in the user configuration file.
LOCK-ESCALATION =
Specifies what happens when there are transaction locks. If a specified number of locked values in an index or number of locked records in a table is exceeded, SESAM/SQL attempts to lock the entire index or table. This is referred to as lock escalation.
A change to the settings for LOCK-ESCALATION can have a considerable effect on the behavior of the DBH as regards storage space requirements and the probability of transaction conflicts:
If low values are selected, an attempt is made at a very early stage to lock the entire table or index. This saves storage space, since the locks do not have to be managed individually. However, it increases the probability of transaction conflicts occurring.
Higher values reduce the risk of transaction conflicts. In this case, however, the storage space requirements increase because the locks have to be managed individually.
The current number of records as of which the entire table is to be locked is obtained from PERCENTAGE-RECORDS and compared with the value of NUMBER-RECORDS. The lower value is used as the limit.
If lock escalation is not successful immediately, only the directly affected record or index value is locked. After this unsuccessful escalation attempt, the next attempt is not started for another 100 locks.
LOCK-ESCALATION = *STD
The default values for the size of the escalation operands are indicated under *PARAMETERS(...).
LOCK-ESCALATION = *PARAMETERS(...)
NUMBER-RECORDS = 4000 / <integer 1..2147483647>
Specifies the number of records in a table that a user can lock. If more records than this are locked, the whole table is locked.
PERCENTAGE-RECORDS = 50 / <integer 0..100>
Specifies the percentage of all the records in the table that a user can lock. If a higher percentage is locked, an attempt is made to lock all the records of the table. If 0 is selected as the percentage, an attempt is made to lock the entire table immediately.
NUMBER-INDEX-VALUES = 1000 / <integer 1..2147483647>
Specifies the number of values that a user can lock in an index. If a larger number than this are locked, an attempt is made to lock the entire index rather than just individual values.