Difference between revisions of "SQL BEGIN TRANSACTION"

Revision as of 16:04, 25 November 2009

Purpose

Flag the beginning of a transaction

Syntax

BEGIN TRANSACTION [<transaction>]

<statements>

END TRANSACTION [<transaction>]

See Also

COMMIT, END TRANSACTION, ROLLBACK, SAVE TRANSACTION, SAVEPOINT, SET TRANSACTION, TXNISOLATION(), TXNLEVEL()

Description

The BEGIN TRANSACTION statement is used to flag the beginning of a transaction. The END TRANSACTION statement is used to commit changes made during the transaction and close the transaction. The COMMIT statement and the ROLLBACK statement can also be used to close a transaction. The COMMIT statement will save the changes made and the ROLLBACK statement will discard the changes made.

Transactions can be nested by issuing a second or subsequent BEGIN TRANSACTION before an existing transaction has been closed. The TXNLEVEL() function returns the current transaction nesting level. When a transaction is closed, transactions nested within it are also closed.

Savepoints can be set during a transaction. These identify stages within the transaction which can subsequently be used as ROLLBACK points.

The optional <transaction> is a name for the transaction. This name can be used by the COMMIT and ROLLBACK statements.

Example

// config.db
set sql to recital
set sql on
// end of config.db
 
// Transactions
BEGIN TRANSACTION trans1;
INSERT INTO customer
(TITLE, LAST_NAME, FIRST_NAME, INITIAL, STREET,
CITY, STATE, ZIP,LIMIT, START_DATE)
VALUES
('Ms', 'Jones', 'Susan', 'B', '177 High Street','Beverly', 'MA', '01915', 2000, date());
INSERT INTO accounts (ORD_VALUE) VALUES (30);
BEGIN TRANSACTION trans2;
INSERT INTO accounts (ORD_VALUE) VALUES (60);
// Rollback the trans1 transaction and any transactions
// nested in trans1
ROLLBACK TRANSACTION trans1;
END TRANSACTION;
// End of program

Products

Recital Server, Recital