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.
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, while 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);