If a system failure interrupts a transaction, the warm restart facility guarantees the security of your data. In the event of a failure in executing a task, a failure of the operating system, or a disk failure, the warm restart facility recovers your databases to a consistent state - provided that database operations were carried out within transactions at the time of failure.
For a warm restart to recover databases, disk corruption should not have occurred. If physical data has been lost from a disk, use the emprecov utility to recover from an archived database.
For the warm restart facility to guarantee the consistency of your databases, the Empress variable MSTRANSSYNC must be set. Setting this variable causes all data to be written immediately to disk during a transaction. MSTRANSSYNC is set by default.
When you run a transaction in Empress, you retain the option of either ultimately committing all the operations in the transaction permanently to the database(s), or rolling them back. Should you be interrupted in the midst of a transaction, the transaction is unresolved and the database(s) may contain inconsistent or corrupted data. The warm restart facility will reset the affected databases and resolve any interrupted transactions. When a database is restarted, an interrupted transaction involving that database can either be completed or rolled back.
The following discussion covers the utilities and Query Language commands available to restart the database, and the conditions governing whether a transaction is completed or rolled back.
The utility empwarm is an automatic warm restart utility. It can be run on a database as:
empwarm [-p] [-v] database
where database is the name of the database.
The -p option overrides transaction protection so that protected transactions are resolved. The -v option runs the warm restart in verbose mode.
The automatic warm restart utility completes interrupted transactions whenever it is feasible to do so. That is, if the Query Language command to commit a transaction is entered and received by the database before the system failure, the transaction will be completed. If the command to commit the transaction was not entered or received by the database, the transaction will be rolled back.
The warm restart facility treats each transaction as atomic, regardless of the length of the transaction (there is no pre-defined limit on the length of transactions). Each transaction is controlled as a unit. When a transaction is committed, every operation in the transaction is committed, and when a transaction is rolled back, every operation is rolled back.
The warm restart utility can be run repeatedly on a database. If, for instance, a system failure occurs during a warm restart, running the warm restart again will recover all data consistently. The warm restart utility cannot corrupt or damage your database.
A transaction can be protected so that in the event it is interrupted, and it is possible to recover it to a save point, the warm restart utility will ignore it. The procedure is given in a later subsection. Running empwarm with the -p option overrides transaction protection, and protected transactions will be resolved.
A transaction can affect tables in several databases. Although a transaction affecting several databases can normally be treated as a single transaction, Empress internally manages and coordinates a separate transaction on each database involved. To secure data integrity in this situation, transaction processing is coordinated using a two-phase commit protocol.
The warm restart utility recognizes transactions that span multiple databases, and enforces the two-phase commit protocol to cover all the affected databases. The transaction you run is controlled as a unit, and the warm restart utility will resolve (commit or cancel) the transaction over all databases so that data integrity is preserved. Under normal circumstances you need not be concerned with the individual databases affected in a transaction.
If a transaction spans several databases, the first database accessed is the coordinator for that transaction. The other databases involved in the transaction are known as participants.
In the event of an automatic warm restart, the coordinator is the final arbiter of whether the transaction is to be committed or rolled back. The coordinator maintains status information on the progress of the transaction (including any actions taken on the transaction after the system failure) both for itself and for the other databases involved.
The coordinator is fully informed of the status of the transaction at all times. If a warm restart is necessary, the coordinator can always determine whether the transaction is to be committed or rolled back. Most of the time, a participant also has information on the status of the transaction, and can also determine whether the transaction is to be committed or rolled back. However, there are moments in a transaction when a participant will require access to the coordinator to determine the correct action.
When the warm restart utility is run on a database, it may encounter several transactions involving the database that were in progress at the time of the system failure. For each transaction it encounters, it must determine the correct course of action to recover the transaction on the database. For those transactions where the database is the coordinator, the status of the transaction is determined and any appropriate action taken. For those transactions where the database is a participant, an attempt is made to determine the status of the transaction, but no action is taken.
When the automatic warm restart utility is run on the coordinator, it determines whether the transaction should be committed or rolled back. If all the participants are accessible, it will proceed with the full commit or rollback over all affected databases.
If any of the participants are not accessible (for instance due to network failures) it will commit or roll back all operations on the coordinator and the accessible participants, then display information on outstanding unresolved tasks. The warm restart may be re-run repeatedly on the coordinator when the outstanding participants become accessible. The unresolved operations will then be committed or rolled back as appropriate.
Warm restart can be run in verbose mode so that status information on the coordinator and all participants is printed immediately before action is taken on each transaction.
If the automatic warm restart utility is run on a participant, no action is taken on the transaction. The warm restart utility will attempt to determine whether the transaction should be committed or rolled back, and confirm the status of the transaction by accessing the coordinator database.
When the warm restart utility is run in verbose mode the status information may be printed in three ways depending on the status of the transaction:
If the status of the transaction can be determined unambiguously, and the coordinator can be accessed, it prints status information on the coordinator and all participants, followed by a transaction status message.
Transactions at a participant can be resolved by either:
This manual override option should be used with discretion and in conjunction with other sites involved in the overall transaction.
Given several databases with concurrent transactions in progress, any database can be a coordinator with respect to some transactions, and a participant with respect to others.
The automatic warm restart utility will treat a database as a coordinator for those transactions where it is a coordinator, and as a participant for those transactions where it is a participant. The automatic warm restart utility will process all transactions affecting the database.
The Query Language DISPLAY WORK command can be used to list full details of the transaction status, including status information on the coordinator and each participant, as well as the overall status of the transaction.
This subsection discusses the general use of the command. The full syntax for the command is presented in Transaction Related Query Language Commands in this chapter.
There are three forms of the DISPLAY WORK command:
Table 6-1: Display Transaction Status Commands
| Command | Description |
| DISPLAY WORK; | Displays the status of current transaction. |
| DISPLAY WORK ON database [ALL]; | Displays all transactions in progress (that is, not completed) that involve the database. |
| DISPLAY WORK [database:]id; | Displays the status of the transaction specified. |
The command:
DISPLAY WORK;
can only be used while in a transaction. It displays the status of the current transaction.
The other two forms are provided to identify transactions in progress and determine their status. They can only be used when not currently in a transaction. A transaction that has been interrupted by a system failure is considered still "in progress" and is so considered until it is resolved.
The command:
DISPLAY WORK ON database [ALL];
prints information (summary information without the ALL option, full information with the ALL option.) on all of the transactions involving the specified database. If entered after a system failure, it lists all unresolved transactions affecting the database.
The command:
DISPLAY WORK [database:]id;
prints full status information on the transaction id. The id is the identification code of the transaction (transaction ID), optionally preceded by a database name. If no database is specified, the default database is used. In any case, the command queries the status of the transaction on the database. If the transaction does not affect the database, the transaction ID will not be recognized, even if the identification code is valid.
Sample output from the DISPLAY WORK command is presented in section on Transaction Examples in this chapter. Explanations of the status messages that are printed by the command are presented in Transaction Status Messages.
Manual overrides are provided for either of two circumstances:
You do not want to lose an entire transaction by allowing the automatic warm restart utility to roll it back.
If a transaction is valuable to you, because of time or effort invested, for example, you may wish to set save points in the transaction. In the event the transaction is interrupted, the save points provide you an alternate way of recovering it. In this case, you can examine the status of the transaction, and if it would be rolled back by a warm restart, you can recover the transaction to a save point instead.
If you need to resolve a transaction on a participant and communication cannot be established with the coordinator, you can force a resolution on the participant. In most cases, the DISPLAY WORK command at the participant will tell you the correct resolution (commit or rollback).
However, if a transaction spanning several databases is interrupted, a participant may be unable to determine whether to commit or roll the transaction back. This applies if the participant cannot independently determine the appropriate course of action, and cannot access the coordinator (recall that the coordinator can always determine whether the transaction should be committed or rolled back). In this case, you should either wait until communication is re-established or contact someone at the coordinator's site and have them use the DISPLAY WORK command to determine the correct course of action.
When you have determined the correct action to take to resolve the transaction, use either the COMMIT WORK or ROLLBACK WORK command to commit or roll back the transaction at the participant.
If you force a transaction to be committed or rolled back at a participant, the status information for the participant is removed from the participant database. Normally the participant will inform the coordinator of the forced action. However, if the coordinator is not accessible, it is not possible to inform it of the final status of the participant. Moreover, the coordinator will not be able to obtain it when communication is re-established. The coordinator will assess this situation as one in which disk corruption may have occurred, and the automatic warm restart utility will not take any action.
You must use manual overrides to force the resolution of the transaction at the coordinator and all other participants. It will also be your responsibility to ensure that manual overrides are used consistently.
Not that manual overrides should be used with caution and only in conjunction with all sites involved in the transaction. Careless or irresponsible use of manual overrides can result in inconsistent or incorrect data since a forced commit or rollback at a participant can conflict with recovery procedures at the coordinator or other participants.
If an unprotected transaction is interrupted before a commit command is issued and logged, the automatic warm restart utility will roll back the transaction. To prevent the loss of entire transactions, Empress offers you a way to recover partially completed transactions using save points. By setting save points in the transaction, you can recover the transaction to any save point after an interrupt. Recovering to a save point resolves the transaction. When a transaction has been recovered to a save point, it will not be affected by subsequent execution of the warm restart utility.
If a transaction is unprotected, the recovery to a save point must occur before any execution of the warm restart utility. If a transaction is protected, the warm restart utility will ignore the transaction if it can be recovered to a save point (and the -p option to override protection is not used). The recovery to a save point can therefore occur after the warm restart utility is run.
A save point is set in a transaction with the command:
SAVEPOINT savepoint;
where savepoint is the name of a save point. It must be a string starting with a letter.
Save points are also used to start a nested transaction within the current transaction. See the Empress SQL: User's Guide and Empress SQL: Reference manuals for more information on nested transactions.
A transaction is recovered to a save point with the command:
START WORK [database:]id [FROM savepoint];
where database is the name of a database. If no database is specified the default database is used.
Each transaction has an ID that can be determined using the DISPLAY WORK command, described in the Transaction Related Query Language Commands section. If no savepoint is specified the transaction will be recovered to the last defined save point.
Note that a transaction with no save points cannot be recovered with this command.
You can use this command when you are not in a transaction. It restarts the transaction at the appropriate save point, and you can then continue the transaction.
Transactions can be protected from the automatic warm restart utility. The system variable MSTRANSWARMPROTECT is a flag that controls whether transactions will be resolved by the automatic warm restart utility empwarm.
If this variable is set to any value when a transaction is started, the transaction is protected from the warm restart utility. That is, the utility empwarm ignores the transaction if it can be recovered to a save point. This feature is useful if the automatic warm restart is included as part of the system re-boot process.
Since an unprotected transaction will certainly be rolled back unless a commit command has been issued, long unprotected transactions that are interrupted will tend to be rolled back. Protecting a transaction allows you to recover a transaction to a save point with the START WORK FROM savepoint command after the warm restart utility has been run.
The variable is not set by default, so by default, the warm restart utility will resolve all interrupted transactions. To set the variable while in a SQL session, use:
SET MSTRANSWARMPROTECT TO x;
The following Query Language commands control and examine the status of transactions:
The various transaction-oriented commands available in the Query Language are illustrated below. They are grouped into commands used to run transactions and commands used to recover transactions. This summary does not give the full syntax of the commands. Syntax diagrams are found in following pages.
The following commands are used to initiate a transaction and to control the current transaction:
Table 6-2: Transaction Initiation Commands
| Command | Description |
| START WORK; | Initiate a transaction. |
| SAVEPOINT savepoint; | Define a save point. |
| ROLLBACK WORK TO savepoint; | Roll back to a save point |
| DISPLAY WORK; | Display status of current transaction. |
| ROLLBACK WORK; | Roll back transaction. |
| COMMIT WORK; | Commit (terminate) transaction. |
The following commands are used to identify transactions, display their status, and recover/resolve them. They can only be used when not in a transaction.
Table 6-3: Transaction Recovery Commands
| Command | Description |
| DISPLAY WORK ON database; | Display all transactions in progress (that is, not completed) that involve the database. |
| DISPLAY WORK id; | Display status of the transaction. |
| START WORK id FROM savepoint; | Recover the transaction by rolling back to the save point. |
| ROLLBACK WORK id; | Force a rollback of the transaction. |
| COMMIT WORK id; | Force a commit of the transaction. |
The syntax for the DISPLAY WORK command is:
| DISPLAY | |WORK |TRANSACTION |
| [ | |
|ON | |
|DB |database |
| [ALL] | |
|]; | |
|||
| | [ | database:]id | | |
Note that:
database is the name of a database. If no database name is specified the default database is used. This command is used to determine the ID of any transaction.
The form:
DISPLAY WORK;
which you enter while a transaction is in progress, displays the status of the transaction.
This can only be used when a transaction is in progress and is the only form of the display transaction command that can be typed while a transaction is in progress.
The form:
DISPLAY WORK ON |DB | [ALL];
| |
|database|
can only be entered when not currently in a transaction.
With the ALL option, full details of every transaction affecting the database are displayed.
Without the ALL option, Empress displays a summary of information on all of the transactions associated with the current database (with the DB option) or with the database you name database.
The summary information includes the transaction ID, any comment on the transaction, the name of the user running the transaction, the date of the transaction, a message if the transaction is protected, and the coordinator database for the transaction.
The form:
DISPLAY WORK [database:]id;
displays the full status information on the transaction id.
This command can only be typed when not in a transaction. The status information includes the transaction ID, any comment on the transaction, the name of the user running the transaction, the date of the transaction, a message if the transaction is protected, the coordinator and each participant of the transaction, any save points in the transaction, and an overall status evaluation of the transaction.
The syntax for the START WORK command is:
| START | |WORK |TRANSACTION |
| [[database:]id [FROM savepoint]];
| |
Note that:
database is the name of a database. If no database name is specified the current default database is assumed. Each transaction has an ID that can be determined using the DISPLAY WORK command. savepoint is a name for a save point in the transaction. It must be a string starting with a letter.
This command can only be typed when not in a transaction. It recovers transactions interrupted by a system failure to the specified save point, restarting the transaction so that work can continue. If a save point is not specified, the last (or most up-to-date) defined save point is used. A transaction with no save points cannot be rolled back to a save point.
This command must be run on the coordinator. That is, the transaction identification must specify the coordinator of the transaction.
Setting a save point is described as part of the SAVEPOINT command in the Empress SQL: Reference.
The syntax for COMMIT WORK command is:
COMMIT |WORK | [database:] id;
|TRANSACTION|
Note that:
database is the name of a database. If no database name is specified the current default database is assumed. Each transaction has an ID that can be determined using the DISPLAY WORK command.
This command can only be typed when not currently in a transaction. It forces the named transaction to be committed. All operations in the transaction are made permanent in the database.
When run on a coordinator, the operations affecting the coordinator and all available participants are committed. When run on a participant only the operations affecting that participant and the coordinator (if available) are committed.
This command cannot be used to contradict the correct resolution of the transaction as printed by the DISPLAY WORK command. For instance, if the correct resolution is to roll back the transaction, the forced COMMIT WORK attempt will fail. Care should be taken, however, if a participant cannot determine whether the transaction should be committed or rolled back. In such a case an incorrect resolution can be forced on the participant.
If the DISPLAY WORK at the participant cannot give an unambiguous resolution, contact the coordinator's site and determine what the correct action should be.
If you force a transaction to be committed at a participant, the status information for the participant is removed from the participant database.
If the coordinator is not accessible it will not be informed of the final status of the participant. Moreover, the coordinator will not be able to obtain it when communication is re-established. The coordinator will assess this situation as one in which disk corruption may have occurred, and the automatic warm restart utility will not take any action. It will therefore be necessary to use manual overrides to force the resolution of the transaction at the coordinator and all other participants. It will also be your responsibility to ensure that manual overrides are used consistently.
The syntax of ROLLBACK WORK command is:
ROLLBACK |WORK | [database:] id;
|TRANSACTION|
Note that:
database is the name of a database. If no database is specified the current default database is assumed. Each transaction has an ID that can be determined using the DISPLAY WORK command.
This command can only be typed when not in a transaction. It forces the named transaction to be rolled back. All operations in the transaction are cancelled for the database. When run on a coordinator the operations affecting the coordinator and all available participants are rolled back. When run on a participant only the operations affecting that participant and the coordinator (if available) are rolled back.
This command cannot be used to contradict the correct resolution of the transaction as printed by the DISPLAY WORK command. For instance, if the correct resolution is to commit the transaction, the forced ROLLBACK WORK attempt will fail.
Care should be taken, however, if a participant cannot determine whether the transaction should be committed or rolled back. In such a case an incorrect resolution can be forced on the participant.
If the DISPLAY WORK at the participant cannot give an unambiguous resolution, contact the coordinator's site and determine what the correct action should be.
If you force a transaction to be rolled back at a participant the status information for the participant is removed from the participant database.
If the coordinator is not accessible it will not be informed of the final status of the participant. Moreover, the coordinator will not be able to obtain it when communication is re-established. The coordinator will assess this situation as one in which disk corruption may have occurred, and the automatic warm restart utility will not take any action.
It will therefore be necessary to use manual overrides to force the resolution of the transaction at the coordinator and all other participants. It will also be your responsibility to ensure that manual overrides are used consistently.
To roll back to a save point see the ROLLBACK WORK command in the Empress SQL: Reference manual.
To restart a transaction from a save point see START WORK.
When the automatic warm restart utility is run, or when a DISPLAY WORK command is issued, the status of each transaction is examined and messages printed to the screen. The messages you may encounter are listed below. Two kinds of messages can be printed: messages pertaining to individual databases involved in the transaction, and messages pertaining to the status of the transaction as a whole.
The following is a list of messages pertaining to individual databases involved in the transaction:
cancelled
The transaction has been cancelled at the database. Commitment is no longer possible. Status information has been removed from the database.
committed
The transaction has been committed at the database. Rollback is no longer possible. Status information has been removed from the database.
committed phase-1
The transaction is ready to be committed at the database. However, it can still be either committed or rolled back. Status information is still in place at the database.
in progress
The transaction was (and still is) in progress at the database. No commit or rollback instructions were received. Status information is still in place at the database. The transaction cannot be committed.
status unavailable
The database cannot be accessed. This may be due, for instance, to network failures, the disk not being mounted, or inability to read the database directory. If you are at a participant and the coordinator is not available, you should contact someone at the coordinator's site to confirm the status of the transaction.
status unrecoverable
Status information is missing at the database. This may be due to:
If the status is unrecoverable at the coordinator, you should contact someone at the coordinator's site to determine what action has been taken on the transaction. Under normal circumstances, status information is retained at the coordinator until the transaction is fully resolved at all databases.
The following is a list of messages pertaining to the status of the transaction as a whole:
Transaction ready for cancel
The transaction should be rolled back. If printed at a coordinator, all sites have been polled and are ready. The warm restart utility (run at the coordinator) will roll back the transaction at all sites.
If this message is printed at a participant, it is valid only for the status of the transaction at the participant. There is a possibility that you may roll back to a save point at the coordinator. You must run the DISPLAY WORK command at the coordinator to find out if that is possible.
If this message is printed at a participant and if the warm restart utility or the DISPLAY WORK command run at the coordinator reports that a participant is not accessible, there is still a possibility that you may rollback to a save point at the coordinator when communication is re-established.
If you force a rollback at the participant your data will be secure but you will destroy the possibility of rolling back to a save point at the coordinator.
Transaction ready for cancel on all available databases
This message can only be printed at the coordinator for the transaction. The transaction should be rolled back. All sites accessible to the coordinator have been polled and are ready. However, there is at least one site that the coordinator cannot access (the network may be down, the file system not mounted or the database directory unreadable). The warm restart utility will roll back the transaction at all the sites it can access.
You can run the warm restart utility to roll back the transaction at the available sites. As communication is restored with unavailable sites, you can run the warm restart utility repeatedly to roll back the transaction at those sites.
If you run the automatic warm restart you may wish to notify someone at the currently unavailable sites of the status of the transaction, if only to ensure that an inappropriate forced action is not taken anywhere. Check the status report generated by the warm restart utility (or the DISPLAY WORK command) for that transaction on future runs. Should a "Warning" or "Error" message be generated, you will have to take appropriate action.
It is possible that you may rollback to a save point when all participants are accessible to the coordinator. Run the DISPLAY WORK command at the coordinator when communication is re-established.
If you must resolve the transaction at sites currently inaccessible, you can use the ROLLBACK WORK command at the participant. If you force a rollback at the participant your data will be secured but you will destroy the possibility of rolling back to a save point at the coordinator.
Transaction ready for commit
The transaction should be committed. If printed at the coordinator of the transaction, all sites have been polled and are ready. The warm restart utility (run at the coordinator) will commit the transaction at all sites.
If this message is printed at a participant of the transaction, it is valid only for the status of the transaction at the participant. To determine the status of the entire transaction across all sites, run the DISPLAY WORK command at the coordinator.
If this message is printed at a participant and the participant is not accessible to the coordinator, a warm restart run at the coordinator will not affect the participant. The transaction at the participant will be committed if the warm restart utility is rerun when communication is re-established. If you must resolve the transaction at the participant immediately, you can use the COMMIT WORK command at the participant.
Transaction ready for commit on all available databases
This message can only be printed at the coordinator for the transaction. The transaction should be committed. All sites accessible to the coordinator have been polled and are ready. However, there is at least one site that the coordinator cannot access (the network may be down, the file system not mounted, or the database directory locked). The warm restart utility will commit the transaction at all the sites it can access.
You can run the warm restart utility to commit the transaction at available sites. When communication is restored with the sites currently not accessible, you can run the warm restart utility again to commit the transaction at those sites.
If you must resolve the transaction at sites currently inaccessible, you can use the COMMIT WORK command at the participant.
Transaction ready for restart or rollback
This message can only be printed at the coordinator for the transaction. The transaction can be rolled back completely or rolled back to a save point. All sites have been polled and are ready. If you run the warm restart utility now it will do a full rollback of the transaction.
You can roll back to a save point using the START WORK (from savepoint) command at the coordinator. If you wish to roll back to a save point, do not run the warm restart utility on the coordinator first, as this will do a full rollback of the transaction. You can run the warm restart utility after rolling back to a save point. The restarted transaction will not be affected by it.
Transaction status unavailable, resolve at Coordinator database
This message can only be printed when running the warm restart utility or executing a DISPLAY WORK command at a participant of the transaction. The participant cannot determine unambiguously whether the transaction is to be committed or rolled back, and cannot access the coordinator to determine the correct action. The warm restart utility will take no action.
If access to the coordinator will be restored soon, you can wait and run the warm restart at the coordinator later. If you must resolve the transaction at the participant immediately, contact the person responsible at the coordinator's site and have them run the DISPLAY WORK to get the coordinator's status report on the transaction. You can then force the transaction as indicated by the coordinator.
Unknown Transaction
This message can be printed from a DISPLAY WORK command. Either the status information for the transaction is missing, or you have typed the identification for the transaction incorrectly. The warm restart utility will take no action.
Check the identification for the transaction (database name, transaction code). If these are correct, then the status information for the transaction at that database is missing. Try to obtain the status at the coordinator, or, if that is not accessible, at another participant.
If the transaction is recognized at the coordinator but not at the participant then status information is missing at the participant. Follow the instructions for the status report from the coordinator. If the coordinator cannot access the participant, see the "Warning" status messages: if the coordinator indicates that the transaction should be cancelled, see under "Warning; possible corruption, attempt forced cancel at all sites"; if the coordinator indicates that the transaction should be committed, see under "Warning; possible corruption, attempt forced commit at all sites".
If the transaction is recognized at a participant but not at the coordinator, status information is missing at the coordinator. See under the message "Warning; corruption at Coordinator database".
Unrecoverable error, Participant in conflict with Coordinator
This message can only be printed at the coordinator for the transaction. A conflict has been created between the coordinator and one or more participants of the transaction. Either:
In either case, the warm restart utility will take no action.
Your data has been corrupted. This situation is a result of a forced commit/rollback applied at a participant when the participant could not determine the correct action unambiguously, and therefore could not veto the (incorrect) forced action. You must use emprecov to restore all databases involved from archives.
To prevent this situation from occurring, always check with the coordinator when a participant cannot determine unambiguously whether to commit or roll back a transaction.
Warning; corruption at Coordinator database
This message can only be printed when the warm restart utility is run on a participant, or when the DISPLAY WORK command is run on a participant. The status information at the coordinator has been lost to disk corruption or has been manually removed. The warm restart utility will take no further action.
If you have forced the transaction at the coordinator then manually removed the status information, you should force the participant in a consistent manner.
If you have not both forced the transaction at the coordinator and manually removed the status information, your data is corrupted. You must use emprecov to restore all databases involved from archives.
Warning; possible corruption, attempt forced cancel at all sites.
The coordinator indicates that the transaction should be cancelled, but status information is missing from a participant. Only a rollback will recover the transaction. The warm restart utility will take no further action.
The missing status information may be due to disk corruption (including having been manually removed) or a forced commit/rollback at the participant. If there has been no forced commit/rollback at the participant, your data has been lost due to disk corruption. You must use emprecov to restore all databases involved from archives.
If there has been a forced commit at the participant, your data is corrupted. You must use emprecov to restore all databases involved from archives. To prevent this situation from occurring, always check with the coordinator when a participant cannot determine unambiguously whether to commit or rollback a transaction.
If there has been a forced rollback at the participant, you should force the transaction to be rolled back at the coordinator and at every other participant. This recovers the transaction.
Warning; possible corruption, attempt forced commit at all sites.
The coordinator indicates that the transaction should be committed, but status information is missing from a participant. Apart from the missing status information, all data is safe. The warm restart utility will take no further action.
The missing status information may be due to disk corruption (including having been manually removed) or a forced commit/rollback at the participant. If there has been no forced commit/rollback at the participant, you should run empclrlk or restart the server at the participant. This clears all locks at the participant. You should also force the transaction to be committed at the coordinator and at every other participant. This recovers the transaction.
If there has been a forced commit at the participant, you should force the transaction to be committed at the coordinator and at every other participant. This recovers the transaction.
If there has been a forced rollback at the participant, your data is corrupted. This can have happened if:
To prevent this situation, always check with the coordinator when a participant cannot determine unambiguously whether to commit or roll back a transaction.
These examples illustrate the transaction related query language commands and the automatic warm restart utility. In the examples, a logical database name is used and the databases are all empty to begin with.
To start a transaction, issue the following command in the SQL session:
START WORK;
The system will respond:
Starting Transaction
At this point, any Query Language command can be executed. For instance to create a table:
CREATE db1: t(a);
This creates a table t in database db1. The table has an attribute called "a" with the default data type of INTEGER. To set a save point at this stage of the transaction:
SAVEPOINT sp1;
The system will respond:
Setting Save Point
Create tables in databases db2 and db3 and set another save point with:
CREATE db2: t(a); CREATE db3: t(a); SAVEPOINT sp2;
The system will respond:
Setting Save Point
The process can be repeated as often as you wish, for example:
CREATE db4: t(a); CREATE db5: t(a); SAVEPOINT sp3; Setting Save Point CREATE db6: t(a); CREATE db7: t(a);
To examine the status of the transaction so far
DISPLAY WORK; Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress Participant DBs: db7 -- in progress db6 -- in progress db5 -- in progress db4 -- in progress db3 -- in progress db2 -- in progress Save Points: sp1 sp2 sp3
The output shows that the coordinator of the transaction is db1 (it was the first database accessed in the transaction) and lists the participants. The status is "in progress" at each database, and there are three save points defined.
The transaction can be interrupted with the quit signal (the appropriate key sequence shown by the stty all command in UNIX). The quit signal initiates a controlled shutdown and rolls back the transaction.
When empsql is restarted there is no longer a current transaction.
DISPLAY WORK;
The system will respond:
*** User Error *** no transaction in progress
To find any transactions in progress involving database db1, issue the following command:
DISPLAY WORK ON db1;
The system will respond:
Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress
The same transaction will show up in an enquiry on database db2:
DISPLAY WORK ON db2; Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress
Full information on the transaction is printed with the following form of the DISPLAY WORK command:
DISPLAY WORK db1:21359; Determining Transaction status Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress Participant DBs: db7 -- in progress db6 -- in progress db5 -- in progress db4 -- in progress db3 -- in progress db2 -- in progress Save Points: sp1 sp2 sp3 Transaction ready for restart or rollback
This status report is similar to the status report shown for the transaction immediately prior to the interrupt. However, in addition to the status messages pertaining to individual databases, there is one regarding the entire transaction as well. This message is the message most commonly received.
This status report is from the coordinator for the transaction. The coordinator polls every participant to determine its status. Since no commit or rollback command is issued during the transaction, the transaction is "in progress" at all the databases and the transaction can be rolled back. Since there are save points defined in the transaction, it can also be restarted from a save point.
An enquiry on the transaction made on a participant shows:
DISPLAY WORK db2:21359; Determining Transaction status Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress Participant DBs: db7 -- status not determined db6 -- status not determined db5 -- status not determined db4 -- status not determined db3 -- status not determined db2 -- in progress Save Points: sp1 sp2 sp3 Transaction ready for cancel
The participant polls the coordinator to learn its status, the list of participants and the save points defined. Other participants are not polled, so their status is not determined.
A database can be made inaccessible by removing operating system permissions. The Empress "!" command can be used to execute the operating system command from the Query Language:
!chmod 0 db4;
To see what the DISPLAY WORK shows:
DISPLAY WORK db1:21359; Determining Transaction status Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress Participant DBs: db7 -- in progress db6 -- in progress db5 -- in progress db4 -- status unavailable db3 -- in progress db2 -- in progress Save Points: sp1 sp2 sp3 Transaction ready for cancel on all available databases
The status for db4 is now unavailable. In addition, the coordinator can no longer determine whether the transaction can be restarted from a save point.
The coordinator db1 can be made inaccessible as well:
!chmod 0 db1;
The DISPLAY WORK command run on a participant now shows:
DISPLAY WORK db2:21359; Determining Transaction status Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- status unavailable Participant DBs: db2 -- in progress Transaction ready for cancel
Since the participant cannot access the coordinator, it cannot learn its status. For the same reason, it cannot learn the names of the other participants and whether there are any save points defined.
6.10.7 Warm Restart on a Participant
Before running the warm restart, make the coordinator accessible again:
!chmod 755 db1;
Running the warm restart utility in verbose mode on a participant results in this:
!empwarm -v db2; User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress Participant DBs: db7 -- status not determined db6 -- status not determined db5 -- status not determined db4 -- status not determined db3 -- status not determined db2 -- in progress Save Points: sp1 sp2 sp3 Transaction ready for cancel This is a participant database The coordinator database is db1 No action taken
No action is ever taken by warm restart when run on a participant. The utility prints status information similar to that printed by the DISPLAY WORK command.
Running warm restart in verbose mode on the coordinator of the transaction results in this:
!empwarm -v db1 Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- in progress Participant DBs: db7 -- in progress db6 -- in progress db5 -- in progress db4 -- status unavailable db3 -- in progress db2 -- in progress Save Points: sp1 sp sp3 Transaction ready for cancel on all available databases Cancelling Transaction
After the restart, an enquiry on the transaction at the coordinator shows:
DISPLAY WORK db1:21359; Determining Transaction status Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- cancelled Participant DBs: db7 -- cancelled db6 -- cancelled db5 -- cancelled db4 -- status unavailable db3 -- cancelled db2 -- cancelled Transaction ready for cancel on all available databasesAn enquiry on the participant db2, where the transaction has been cancelled, shows:
DISPLAY WORK db2:21359; *** User Error *** unknown transaction 21359
Since the transaction has been resolved at this participant, the participant is no longer concerned with it and the status information has been removed:
Making db1 inaccessible and db4 accessible, then enquiring on the transaction at db4:
!chmod 755 db4; !chmod 0 db1; DISPLAY WORK db4:21359; Determining Transaction status Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- status unavailable Participant DBs: db4 -- in progress Transaction ready for cancel
The transaction is ready for cancel on db4 which is consistent with what we have seen and done so far.
A forced rollback on db4 is possible. (A forced rollback is neither necessary nor recommended unless communication cannot be re-established with the coordinator. The reason is illustrated below.) The result is:
ROLLBACK WORK db4:21359 Determining Transaction status Cancelling Transaction *** Warning *** Participant status lost since Coordinator unavailable
Since the coordinator was not available, the participant was unable to notify the coordinator that it cancelled the transaction. The forced rollback has also removed all status information on the transaction from db4. An enquiry on the transaction at db4 shows:
DISPLAY WORK db4:21359; Unknown Transaction 21359
Making the coordinator available, and enquiring there shows:
!chmod 755 db1 DISPLAY WORK db1:21359; Determining Transaction status Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- cancelled Participant DBs: db7 -- cancelled db6 -- cancelled db5 -- cancelled db4 -- status unrecoverable db3 -- cancelled db2 -- cancelled Warning; possible corruption, attempt forced cancel at all sites
The status of the transaction at db4 is "unrecoverable". The coordinator does not know that the transaction at db4 has been properly cancelled.
The warm restart utility will report the same situation:
!empwarm -v db1 Transaction ID: 21359 User: documentation Date: 29 Apr 1987 12:17:27 Coordinator DB: db1 -- cancelled Participant DBs: db7 -- cancelled db6 -- cancelled db5 -- cancelled db4 -- status unrecoverable db3 -- cancelled db2 -- cancelled Warning; possible corruption, attempt forced cancel at all sites No action takenNow the only way to resolve the transaction is to force a cancel at the coordinator:
ROLLBACK WORK db1:21359 Determining Transaction status Cancelling Transaction
To verify that the transaction is resolved:
DISPLAY WORK ON db1; No Transactions
Transaction 21359 has been resolved and all status information on it has been removed.