ALLBASE/SQL Reference Manual
> Chapter 2 Using ALLBASE/SQLManaging Transactions |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
BEGIN WORK UPDATE PurchDB.Parts SET PartName = 'Defibrillator' WHERE PartNumber = '1152-DE-95683' COMMIT WORKThe SQL statements used in transaction management are as follows:
Objectives of Transaction ManagementThe objectives of transaction management are related to one another. Data integrity is enforced by proper transaction management, but must be balanced by the need for high concurrency. The use of transactions facilitates the recovery of data after a crash, maintaining data integrity. Ensuring Logical Data IntegrityThe data in the database must be accurate and consistent. For example, adding a part to the warehouse inventory entails inserting a row into three tables: PurchDB.Parts, PurchDB.SupplyPrice, and PurchDB.Inventory. All three inserts must succeed, or else the database is left in an inconsistent state. To enforce data integrity, the three inserts are contained in a single transaction. If any one insert fails, then the entire transaction fails and none of the other inserts takes effect. The following example shows how this transaction might be coded:BEGIN WORK INSERT INTO PurchDB.Parts ... If the insert into PurchDB.Parts fails then ROLLBACK else INSERT INTO PurchDB.SupplyPrice ... If the insert into PurchDB.SupplyPrice fails then ROLLBACK else INSERT INTO PurchDB.Inventory ... If the insert into PurchDB.Inventory fails then ROLLBACK else COMMIT WORK endif endif endif Maximizing ConcurrencyConcurrency is the degree to which data can be accessed simultaneously by multiple users. For example, an application that allows one hundred users to access a table simultaneously has higher concurrency, and therefore better performance, than an application that allows only one user at a time to access the table. Locking regulates the simultaneous access of data. For example, if one user updates a row, the row is locked and other users cannot access the row until the first user is finished. Locking the row enforces data integrity, but reduces concurrency because other users are forced to wait. The isolation level specified in a BEGIN WORK statement affects the duration and types of locks held within a transaction. Isolation levels are fully discussed in Chapter 5 "Concurrency Control through Locks and Isolation Levels" Well-managed transactions balance the conflicting requirements of minimal lock contention and maximum concurrency.Facilitating RecoveryWhen a soft crash occurs, incomplete transactions are automatically rolled back when the DBEnvironment is restarted. If archive logging is in effect when a hard crash occurs, committed transactions are applied to the database during rollforward recovery. In both cases, only those transactions that were uncommitted when the crash occurred need to be redone.Starting TransactionsA transaction is initiated with either an implicit or explicit BEGIN WORK statement. An implicit BEGIN WORK statement is issued by ALLBASE/SQL when any SQL statement is executed, except for the following:
Ending TransactionsA transaction ends when either a COMMIT WORK or a ROLLBACK WORK statement is issued. All locks held by the session are released when the transaction ends, except those held by a kept cursor. Using COMMIT WORKIssue the COMMIT WORK statement when the transaction is successful and you want the changes made permanent. Unlike the BEGIN WORK and ROLLBACK WORK statements, the COMMIT WORK statement is never issued automatically by ALLBASE/SQL. You must issue the COMMIT WORK explicitly for each transaction. The COMMIT WORK statement causes the contents of the log buffer to be written to a log file. If rollforward recovery is needed at a later time, the transactions recorded in the log file are applied to the database.Using ROLLBACK WORKThe ROLLBACK WORK statement ends the transaction and undoes all data modifications made since the BEGIN WORK statement, unless it references a savepoint. (See the discussion of savepoints in the following section.) The ROLLBACK WORK statement is issued automatically by ALLBASE/SQL under the following conditions:
Using SAVEPOINTThe SAVEPOINT statement allows you to rollback part of a transaction. Multiple savepoints are permitted within a transaction anywhere between the BEGIN WORK and COMMIT WORK statements. Each SAVEPOINT statement places a unique marker, called a savepoint number, within the transaction. When a subsequent ROLLBACK references the savepoint number, only those database changes made after the savepoint are rolled back. Rolling back to a savepoint does not end the transaction, but it does release locks obtained after the savepoint was issued. In the following ISQL example, the number identifying the savepoint marker is 6. The update performed after the SAVEPOINT statement is undone by the ROLLBACK statement, but any database changes made before savepoint 6 are unaffected. isql=> SAVEPOINT;Savepoint number is 6. Use this number to do ROLLBACK WORK to 6. isql=> UPDATE PurchDBParts > SET SalesPrice = 244.00 > WHERE PartNumber = '1243-MU-01'; isql=> ROLLBACK WORK to 6;After a rollback to a savepoint has been executed, use the COMMIT WORK statement to make the changes that were not rolled back permanent. If you want to rollback the entire transaction, issue the ROLLBACK statement without a savepoint. Savepoints are suitable for transactions that perform several operations, any of which may need to be rolled back. In the following example, a travel agency is booking tour reservations for 15 people. When the first attempt to make a hotel reservation fails, only that part of the transaction is rolled back. The car reservations are unaffected by the roll back because they were made prior to the savepoint. BEGIN WORKMake 15 car reservations. SAVEPOINTSavepoint number is 1. An attempt to make 15 hotel reservations fails because the designated hotel is full. ROLLBACK WORK TO 1 SAVEPOINTSavepoint number is 2. Make 15 hotel reservations at another hotel. COMMIT WORK Scoping of Transaction and Session AttributesA set of attributes is associated with each transaction and user session. This section discusses the statements used to specify the following transaction and session attributes:
. . . SET SESSION ISOLATION LEVEL RC . . .Note that each transaction starts implicitly. In this example, there is no need for any BEGIN WORK statements. However, you might choose to include BEGIN WORK statements to make your code more readable or to set a different isolation level for a particular transaction. SELECT * FROM PurchDB.OrderItems WHERE VendPartNumber = '2310' COMMIT WORK . . . SELECT * FROM PurchDB.Vendors WHERE VendorNumber = 1234 COMMIT WORK . . . SELECT * FROM PurchDB.SupplyPrice WHERE VendorNumber = 1234 AND VendPartNumber = '2310' COMMIT WORK . . .For more information on isolation levels, refer to Chapter 5 "Concurrency Control through Locks and Isolation Levels" in this manual. Transaction Limits and TimeoutsThe maximum number of concurrent transactions is determined by the MaxTransactions parameter of the DBECon file. Use either the START DBE statement or the SQLUtil ALTDBE command to set MaxTransactions. The SQLUtil SHOWDBE command displays the current setting of MaxTransactions in the DBECon file. If a session attempts to start a transaction, but the maximum number of concurrent transactions has already been reached, the new transaction is placed in the throttled wait queue. The transaction must wait until it reaches the head of the queue and one of the active transactions terminates. The throttled wait queue is serviced on a first in, first out basis. The transaction priority parameter of the BEGIN WORK statement determines which transaction is aborted to break a deadlock, not the transaction's position on the throttled wait queue. If the transaction is still waiting when its timeout limit is reached, the transaction is aborted. The timeout action can also be set to abort the command being processed instead of the entire transaction. Set the timeout limit for the DBEnvironment with the STARTDBE statement or the SQLUtil ALTDBE command. To specify a timeout limit for a particular session, use the SET USER TIMEOUT statement. Both SET SESSION and SET TRANSACTION have parameters to specify which action the system should take when a timer expires. The setting of timeout values is also incorporated into these commands. The SQLUtil SHOWDBE command displays the current, default, and maximum values of the timeout parameter in the DBECon file. Monitoring TransactionsThe SYSTEM.TRANSACTION pseudo-table contains the user identifier, connection-id, session identifier, transaction identifier, transaction priority, and isolation level of every current transaction. To view this information with ISQL, issue the following statement: isql=> SELECT * FROM System.Transaction;To identify the transactions on the throttle wait queue, query the SYSTEM.CALL pseudo-table as follows: isql=> SELECT * FROM System.Call WHERE Status = 'Throttle wait';For more information on transaction activity, consult Load subsystem in SQLMON, the ALLBASE/SQL on-line monitoring tool. SQLMON provides the following transaction information:
Tips on Transaction ManagementKeep transactions short. As the length of a transaction increases, so does the chance that other transactions are forced to wait for the locks it holds. In addition to increasing concurrency, short transactions minimize the amount of data that must be re-entered after a system crash. When archive logging is in effect, changes made to the database are written to the log file whenever a COMMIT WORK is issued. If the system crashes during a long transaction, a large number of uncommitted changes will be rolled back. To shorten a transaction, place program statements not essential to the logical unit of work outside of the transaction. Retrieve all user input before the start of a transaction, to ensure that locks are not held if the user walks away from the terminal. Because terminal writes can also be time consuming, they should not be performed within a transaction. Careful use of savepoints can decrease the amount of time locks are held, and reduces the need to resubmit transactions because part of a transaction was unsuccessful. Set the maximum number of transactions (MaxTransactions) and timeout limit parameters correctly. If MaxTransactions is too low, transactions will wait for no reason. However, the overall throughput of the DBEnvironment may be reduced if MaxTransactions is too high. If the timeout limit is too low, transactions will abort, but if set too high, the session might wait indefinitely for a transaction slot.
|