MySQL 6.0 Transactions & Locking SQL

• START TRANSACTION / ROLLBACK / COMMIT
• SAVEPOINT / RELEASE SAVEPOINT
• LOCK TABLES / UNLOCK TABLES
• SET TRANSACTION
• XA

START TRANSACTION / ROLLBACK / COMMIT

START TRANSACTION [WITH CONSISTENT SNAPSHOT] | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}

SAVEPOINT / RELEASE SAVEPOINT

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

LOCK TABLES / UNLOCK TABLES

LOCK TABLES
    tbl_name [[AS] alias] lock_type

    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
  | IN SHARE MODE [NOWAIT]
  | IN EXCLUSIVE MODE [NOWAIT]

UNLOCK TABLES

SET TRANSACTION

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
  {
       READ UNCOMMITTED
     | READ COMMITTED
     | REPEATABLE READ
     | SERIALIZABLE
   }

XA

XA {START|BEGIN} xid [JOIN|RESUME]

XA END xid [SUSPEND [FOR MIGRATE]]

XA PREPARE xid

XA COMMIT xid [ONE PHASE]

XA ROLLBACK xid

XA RECOVER