START TRANSACTION

START TRANSACTION signals the start of a logical transaction and must always be paired with a COMMIT or a ROLLBACK.

Syntax

START TRANSACTION 
Sql-statements 

COMMIT | ROLLBACK [WORK]

Remarks

START TRANSACTION is supported only within stored procedures. You cannot use START TRANSACTION within SQL Editor. (SQL Editor sets AUTOCOMMIT to "on.")

This statement is not supported outside of a stored procedure because the ODBC standard specifies that every statement is by default inside a transaction. The ODBC standard does not have an API to start a transaction.

ODBC provides for the application to decide if each SQL statement is in its own transaction or if the application will specify when each transaction is completed. ODBC automatically opens a transaction prior to any statement that is not in a transaction. Thus, with the first statement of a given connection, or with the first statement after a COMMIT or ROLLBACK, ODBC automatically starts a new transaction.

Within the ODBC standard, SQLSetConnectOption is used to specify whether each statement is in its own transaction or the application groups statements within a transaction.

Each statement is in its own transaction if SQLSetConnectOption is called specifying the option SQL_AUTOCOMMIT and the value SQL_AUTOCOMMIT_ON (this is the default). This usage means that a transaction is started at the beginning of executing a statement and the transaction is either automatically committed, if no error occurs, or rolled back, if error occurred, upon completion of statement execution.

The application can group statements in a transaction if SQLSetConnectOption is called specifying the option SQL_AUTOCOMMIT and the value SQL_AUTOCOMMIT_OFF value. This usage means that a transaction is started at the beginning of the first statement executed. The application then decides when and how to end the transaction by calling SQLTransact or executing a 'COMMIT WORK' or 'ROLLBACK WORK' statement. When the application ends one transaction, another transaction is automatically started on execution of the next statement.

Examples

The following example, within a stored procedure, begins a transaction which updates the Amount_Owed column in the Billing table. This work is committed; another transaction updates the Amount_Paid column and sets it to zero. The final COMMIT WORK statement ends the second transaction.

START TRANSACTION; 
UPDATE Billing B 
SET Amount_Owed = Amount_Owed - Amount_Paid 
WHERE Student_ID IN 
(SELECT DISTINCT E.Student_ID 
FROM Enrolls E, Billing B 
WHERE E.Student_ID = B.Student_ID); 

COMMIT WORK; 
START TRANSACTION; 
UPDATE Billing B 
SET Amount_Paid = 0 
WHERE Student_ID IN 
(SELECT DISTINCT E.Student_ID 
FROM Enrolls E, Billing B 
WHERE E.Student_ID = B.Student_ID); 

COMMIT WORK;