|
|
|
The begin transaction SQL statement declares the beginning of a multi-statement transaction (MST). MSTs contain one or more SQL statements to be processed as a single, indivisible database action. Many SQL statements are allowed within an MST; others, however, are not. The phrase, within an MST, is strictly defined to indicate statements appearing between an initial begin transaction statement and a final end transaction statement.
After beginning an MST with begin transaction, the MST can be terminated by either committing or aborting the transaction. Use the end transaction statement to commit the MST and the abort statement to undo the MST. Ingres automatically aborts the MST in cases of deadlock.
Note: Set lockmode is not permitted within an MST. Begin transaction and end transaction are not allowed to be nested within an MST.
This statement has the following format:
BEGIN TRANSACTION
|
|
|
The following example begins an MST, executes SQL statements, and commits the updates to the database:
begin transaction;
insert into emp (name, sal, bdate)
values ('Jones,Bill', 10000, 1914);
insert into emp (name, sal, bdate)
values ('Smith,Stan', 20000, 1948);
end transaction; \g
/* commits both inserts to table */
The following example begins an MST, executes SQL statement, and aborts the transaction, thus canceling the updates:
begin transaction;
insert into emp (name, sal, bdate)
values ('Jones,Bill', 1000000, 1814);
insert into emp (name, sal, bdate)
values ('Wrong,Tony', 150, 2021);
abort; \g
/* undoes both inserts; table is unchanged */