Starts a transaction.

Syntax

---

START TRANSACTION
[ON TABLES <TableName> [,<TableName>]]
[TIMEOUT <Timeout>]

<Timeout> = Milliseconds to wait for lock

Usage

---

Use this statement to start a transaction on the current database or a specific set of tables in the current database. Use the ON TABLES clause to specify a specific table or set of tables to start the transaction on.


Use the TIMEOUT clause to specify the amount of time, in milliseconds, to wait for the transaction lock to succeed before raising a lock exception.

Examples

---

-- This procedure uses an IF statement
-- to conditionally test if the State column
-- is equal to 'FL', and if so, to change it
-- to 'NY'

-- The whole update process is wrapped inside
-- of a transaction start..commit/rollback block

CREATE PROCEDURE UpdateState()
BEGIN
   DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
   DECLARE State CHAR(2) DEFAULT '';

   PREPARE Stmt FROM 'SELECT * FROM Customer';

   OPEN CustCursor;

   START TRANSACTION ON TABLES 'Customer';
   BEGIN

      FETCH FIRST FROM CustCursor ('State') INTO State;

      WHILE NOT EOF(CustCursor) DO
         IF (State='FL') THEN
            UPDATE CustCursor SET 'State'='NY';
         END IF;
         FETCH NEXT FROM CustCursor ('State') INTO State;
      END WHILE;

      COMMIT;

   EXCEPTION
      ROLLBACK;
   END;
END

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: