Working with Transactions (Embedded SQL Guide)

Go Up to Embedded SQL Guide

All SQL data definition and data manipulation statements take place within the context of a transaction, a set of SQL statements that works to carry out a single task. This chapter explains how to open, control, and close transactions using the following SQL transaction management statements:

Statement	Purpose
SET TRANSACTION	Starts a transaction, assigns it a name, and specifies its behavior. The following behaviors can be specified: Access mode describes the actions that the statements of an action can perform. Lock resolution describes how a transaction should react if a lock conflict occurs. Isolation level describes the view of the database given a transaction as it relates to actions performed by other simultaneously occurring transactions. Table reservation, an optional list of tables to lock for access at the start of the transaction rather than at the time of explicit reads or writes. Database specification, an optional list limiting the open databases to which a transaction may have access.
COMMIT	Saves the changes of a transaction to the database and ends the transaction.
ROLLBACK	Undoes the changes of a transaction before they have been committed to the database, and ends the transaction.

Transaction management statements define the beginning and end of a transaction. They also control its behavior and interaction with other simultaneously running transactions that share access to the same data within and across applications.

There are two types of transactions in InterBase:

• GDS__TRANS is a default transaction that InterBase uses when it encounters a statement requiring a transaction without first finding a SET TRANSACTION statement. A default behavior is defined for GDS__TRANS, but it can be changed by starting the default transaction with SET TRANSACTION and specifying alternative behavior as parameters. Treat GDS__TRANS as a global variable of type isc_tr_handle.

Note:
When using the default transaction without explicitly starting it with SET TRANSACTION, applications must be preprocessed without the gpre -m switch.

• Named transactions are always started with SET TRANSACTION statements. These statements provide unique names for each transaction, and usually include parameters that specify the behavior of a transaction.

Except for naming conventions and use in multi-transaction programs, both the default and named transactions offer the same control over transactions. SET TRANSACTION has optional parameters for specifying access mode, lock resolution, and isolation level.

For more information about gpre, see Preprocessing, Compiling, and Linking. For more information about transaction behavior, see Specifying SET TRANSACTION Behavior.

Topics

• Starting the Default Transaction
• Starting a Named Transaction
• Using Transaction Names in Data Statements
• Ending a Transaction
• Working with Multiple Transactions
• Working with Multiple Transactions in DSQL

Advance To:

• Starting the Default Transaction