Consultor Eletrônico

Status: Verified

GOAL:

SQL: How to start and end a transaction in SQL-92?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.x
OpenEdge 10.x

FIX:

A transaction is a sequence of operations on a database. Transactions are both atomic and durable. Atomicity refers to the property that either all the operations in a transaction are completed if the transaction is committed, or none of the operations are completed if the transaction is rolled back. Durability refers to the property that once you COMMIT a transaction, the changes made by the transaction are permanent.

Any SQL-92 executable statement is executed as part of a transaction. When you execute a statement and there is already an active transaction, the statement executes as part of the existing transaction.

If an active transaction does not exist, execution of the SQL statement starts a new transaction. This transaction becomes the active transaction and all subsequent SQL-92 statements will execute as part of this transaction until the application ends the transaction with an explicit COMMIT or ROLLBACK operation.

The COMMIT statement ends the currently active transaction. When a COMMIT statement is executed, all the changes made to the database by the transaction are made permanent.

The ROLLBACK statement undoes all the changes made to the database within the currently active transaction.
The following JAVA code starts and ends a transaction:
con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO customer (CustNum, Name, Country) VALUES (1001, 'New Customer Name', 'USA')");
con.commit();
con.setAutoCommit(true);