START TRANSACTION

---

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

Syntax

START TRANSACTION 

Remarks

START TRANSACTION is supported only within stored procedures. You cannot use START TRANSACTION within the SQL Data Manager. (The SQL Data Manager 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.

Statements are delimited with a semi-colon inside stored procedures and triggers.

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; 

See Also

COMMIT

CREATE PROCEDURE

ROLLBACK