Rechercher dans le manuel MySQL
13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax
- [transaction_characteristic [, transaction_characteristic] ...]
- transaction_characteristic: {
- | READ ONLY
- }
These statements provide control over use of transactions:
START TRANSACTION
orBEGIN
start a new transaction.COMMIT
commits the current transaction, making its changes permanent.ROLLBACK
rolls back the current transaction, canceling its changes.SET autocommit
disables or enables the default autocommit mode for the current session.
By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.
To disable autocommit mode implicitly for a single series of
statements, use the START TRANSACTION
statement:
With START TRANSACTION
, autocommit remains
disabled until you end the transaction with
COMMIT
or ROLLBACK
. The
autocommit mode then reverts to its previous state.
START TRANSACTION
permits several modifiers
that control transaction characteristics. To specify multiple
modifiers, separate them by commas.
The
WITH CONSISTENT SNAPSHOT
modifier starts a consistent read for storage engines that are capable of it. This applies only toInnoDB
. The effect is the same as issuing aSTART TRANSACTION
followed by aSELECT
from anyInnoDB
table. See Section 15.7.2.3, “Consistent Nonlocking Reads”. TheWITH CONSISTENT SNAPSHOT
modifier does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits a consistent read. The only isolation level that permits a consistent read isREPEATABLE READ
. For all other isolation levels, theWITH CONSISTENT SNAPSHOT
clause is ignored. A warning is generated when theWITH CONSISTENT SNAPSHOT
clause is ignored.The
READ WRITE
andREAD ONLY
modifiers set the transaction access mode. They permit or prohibit changes to tables used in the transaction. TheREAD ONLY
restriction prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions; the transaction can still modify or lock temporary tables.MySQL enables extra optimizations for queries on
InnoDB
tables when the transaction is known to be read-only. SpecifyingREAD ONLY
ensures these optimizations are applied in cases where the read-only status cannot be determined automatically. See Section 8.5.3, “Optimizing InnoDB Read-Only Transactions” for more information.If no access mode is specified, the default mode applies. Unless the default has been changed, it is read/write. It is not permitted to specify both
READ WRITE
andREAD ONLY
in the same statement.In read-only mode, it remains possible to change tables created with the
TEMPORARY
keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent tables.For additional information about transaction access mode, including ways to change the default mode, see Section 13.3.7, “SET TRANSACTION Syntax”.
If the
read_only
system variable is enabled, explicitly starting a transaction withSTART TRANSACTION READ WRITE
requires theCONNECTION_ADMIN
orSUPER
privilege.
Many APIs used for writing MySQL client applications (such as
JDBC) provide their own methods for starting transactions that
can (and sometimes should) be used instead of sending a
START TRANSACTION
statement from the client.
See Chapter 28, Connectors and APIs, or the documentation for
your API, for more information.
To disable autocommit mode explicitly, use the following statement:
After disabling autocommit mode by setting the
autocommit
variable to zero,
changes to transaction-safe tables (such as those for
InnoDB
or
NDB
) are not made permanent
immediately. You must use COMMIT
to
store your changes to disk or ROLLBACK
to
ignore the changes.
autocommit
is a session variable
and must be set for each session. To disable autocommit mode for
each new connection, see the description of the
autocommit
system variable at
Section 5.1.8, “Server System Variables”.
BEGIN
and BEGIN WORK
are
supported as aliases of START TRANSACTION
for
initiating a transaction. START TRANSACTION
is
standard SQL syntax, is the recommended way to start an ad-hoc
transaction, and permits modifiers that BEGIN
does not.
The BEGIN
statement differs from the use of the
BEGIN
keyword that starts a
BEGIN ... END
compound statement. The latter does not begin a transaction. See
Section 13.6.1, “BEGIN ... END Compound-Statement Syntax”.
Within all stored programs (stored procedures and functions,
triggers, and events), the parser treats BEGIN
[WORK]
as the beginning of a
BEGIN ...
END
block. Begin a transaction in this context with
START
TRANSACTION
instead.
The optional WORK
keyword is supported for
COMMIT
and ROLLBACK
, as are
the CHAIN
and RELEASE
clauses. CHAIN
and RELEASE
can be used for additional control over transaction completion.
The value of the completion_type
system variable determines the default completion behavior. See
Section 5.1.8, “Server System Variables”.
The AND CHAIN
clause causes a new transaction
to begin as soon as the current one ends, and the new transaction
has the same isolation level as the just-terminated transaction.
The new transaction also uses the same access mode (READ
WRITE
or READ ONLY
) as the
just-terminated transaction. The RELEASE
clause
causes the server to disconnect the current client session after
terminating the current transaction. Including the
NO
keyword suppresses CHAIN
or RELEASE
completion, which can be useful if
the completion_type
system
variable is set to cause chaining or release completion by
default.
Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.
Beginning a transaction also causes table locks acquired with
LOCK TABLES
to be released, as
though you had executed
UNLOCK
TABLES
. Beginning a transaction does not release a
global read lock acquired with FLUSH TABLES
WITH READ LOCK
.
For best results, transactions should be performed using only tables managed by a single transaction-safe storage engine. Otherwise, the following problems can occur:
If you use tables from more than one transaction-safe storage engine (such as
InnoDB
), and the transaction isolation level is notSERIALIZABLE
, it is possible that when one transaction commits, another ongoing transaction that uses the same tables will see only some of the changes made by the first transaction. That is, the atomicity of transactions is not guaranteed with mixed engines and inconsistencies can result. (If mixed-engine transactions are infrequent, you can useSET TRANSACTION ISOLATION LEVEL
to set the isolation level toSERIALIZABLE
on a per-transaction basis as necessary.)If you use tables that are not transaction-safe within a transaction, changes to those tables are stored at once, regardless of the status of autocommit mode.
If you issue a
ROLLBACK
statement after updating a nontransactional table within a transaction, anER_WARNING_NOT_COMPLETE_ROLLBACK
warning occurs. Changes to transaction-safe tables are rolled back, but not changes to nontransaction-safe tables.
Each transaction is stored in the binary log in one chunk, upon
COMMIT
. Transactions that are
rolled back are not logged.
(Exception: Modifications to
nontransactional tables cannot be rolled back. If a transaction
that is rolled back includes modifications to nontransactional
tables, the entire transaction is logged with a
ROLLBACK
statement at the end to ensure that modifications to the
nontransactional tables are replicated.) See
Section 5.4.4, “The Binary Log”.
You can change the isolation level or access mode for transactions
with the SET TRANSACTION
statement.
See Section 13.3.7, “SET TRANSACTION Syntax”.
Rolling back can be a slow operation that may occur implicitly
without the user having explicitly asked for it (for example, when
an error occurs). Because of this, SHOW
PROCESSLIST
displays Rolling back
in
the State
column for the session, not only for
explicit rollbacks performed with the
ROLLBACK
statement but also for implicit rollbacks.
In MySQL 8.0, BEGIN
,
COMMIT
, and ROLLBACK
are
not affected by --replicate-do-db
or --replicate-ignore-db
rules.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-commit.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.