Transactional and Locking Statements •Both constructs exist for the scope of a single statement. •A derived table can be referenced only a single time within a query. A CTE can be referenced multiple times. To use multiple instances of a derived table result, you must derive the result multiple times. •A CTE can be self-referencing (recursive). •One CTE can refer to another. •A CTE may be easier to read when its definition appears at the beginning of the statement rather than embedded within it. Because of these similarities, CTEs and derived tables often can be used interchangeably. As a trivial example, these statements are equivalent: However, CTEs have some advantages over derived tables: CTEs are similar to tables created with  CREATE [TEMPORARY] TABLE  but need not be defined or dropped explicitly. For a CTE, you need no privileges to create tables. WITH cte AS (SELECT 1) SELECT * FROM cte; SELECT * FROM (SELECT 1) AS dt; 13.3 Transactional and Locking Statements 13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements MySQL supports local transactions (within a given client session) through statements such as  SET autocommit ,  START TRANSACTION ,  COMMIT , and  ROLLBACK . See Section 13.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements” . XA transaction support enables MySQL to participate in distributed transactions as well. See Section 13.3.8, “XA Transactions” . START TRANSACTION [ transaction_characteristic  [,  transaction_characteristic ] ...] transaction_characteristic : { WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY } BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1} These statements provide control over use of transactions: By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by  START TRANSACTION  and  COMMIT . • SET autocommit  disables or enables the default autocommit mode for the current session. • ROLLBACK  rolls back the current transaction, canceling its changes. • COMMIT  commits the current transaction, making its changes permanent. • START TRANSACTION  or  BEGIN  start a new transaction. 2720