Transaction Control Language

Transaction Control Language (TCL) covers the MySQL statements that group work into atomic units, coordinate that work across multiple resource managers, and control concurrent access to tables while a transaction is in progress. This chapter is organized into four topics.

Transactions introduces START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, autocommit behavior, the statements that trigger an implicit commit, and how to run transactions from PHP.

XA Transactions covers distributed transactions based on the X/Open XA specification, including the two-phase commit protocol and MySQL 8.0.29's support for detached XA transactions.

Locks describes explicit table locking with LOCK TABLES and UNLOCK TABLES, how locking interacts with views, triggers and transactions, InnoDB locking reads (FOR SHARE, FOR UPDATE, NOWAIT, SKIP LOCKED), and the global read lock acquired by FLUSH TABLES WITH READ LOCK.

Session Reuse explains how MySQL client programs can resume a prior TLS/SSL session to reduce the cost of establishing new encrypted connections, from both the server and client side.

-- Minimal shape of a MySQL transaction, illustrating the statements
-- covered in this chapter.

START TRANSACTION;

   INSERT INTO tbl VALUES (100);
   SAVEPOINT sp;
   INSERT INTO tbl VALUES (200);
   ROLLBACK TO sp;

COMMIT;

SELECT * FROM tbl;

-- Minimal shape of a MySQL transaction, illustrating the statements
-- covered in this chapter.

START TRANSACTION;

   INSERT INTO tbl VALUES (100);
   SAVEPOINT sp;
   INSERT INTO tbl VALUES (200);
   ROLLBACK TO sp;

COMMIT;

SELECT * FROM tbl;

a
100

MENU

Transaction Control Language