Rechercher dans le manuel MySQL
13.3.3 Statements That Cause an Implicit Commit
The statements listed in this section (and any synonyms for them)
implicitly end any transaction active in the current session, as
if you had done a COMMIT
before
executing the statement.
Most of these statements also cause an implicit commit after executing. The intent is to handle each such statement in its own special transaction. Transaction-control and locking statements are exceptions: If an implicit commit occurs before execution, another does not occur after.
Data definition language (DDL) statements that define or modify database objects.
ALTER EVENT
,ALTER FUNCTION
,ALTER PROCEDURE
,ALTER SERVER
,ALTER TABLE
,ALTER VIEW
,CREATE DATABASE
,CREATE EVENT
,CREATE FUNCTION
,CREATE INDEX
,CREATE PROCEDURE
,CREATE ROLE
,CREATE SERVER
,CREATE SPATIAL REFERENCE SYSTEM
,CREATE TABLE
,CREATE TRIGGER
,CREATE VIEW
,DROP DATABASE
,DROP EVENT
,DROP FUNCTION
,DROP INDEX
,DROP PROCEDURE
,DROP ROLE
,DROP SERVER
,DROP SPATIAL REFERENCE SYSTEM
,DROP TABLE
,DROP TRIGGER
,DROP VIEW
,INSTALL PLUGIN
,RENAME TABLE
,TRUNCATE TABLE
,UNINSTALL PLUGIN
.CREATE TABLE
andDROP TABLE
statements do not commit a transaction if theTEMPORARY
keyword is used. (This does not apply to other operations on temporary tables such asALTER TABLE
andCREATE INDEX
, which do cause a commit.) However, although no implicit commit occurs, neither can the statement be rolled back, which means that the use of such statements causes transactional atomicity to be violated. For example, if you useCREATE TEMPORARY TABLE
and then roll back the transaction, the table remains in existence.The
CREATE TABLE
statement inInnoDB
is processed as a single transaction. This means that aROLLBACK
from the user does not undoCREATE TABLE
statements the user made during that transaction.CREATE TABLE ... SELECT
causes an implicit commit before and after the statement is executed when you are creating nontemporary tables. (No commit occurs forCREATE TEMPORARY TABLE ... SELECT
.)Statements that implicitly use or modify tables in the
mysql
database.ALTER USER
,CREATE USER
,DROP USER
,GRANT
,RENAME USER
,REVOKE
,SET PASSWORD
.Transaction-control and locking statements.
BEGIN
,LOCK TABLES
,SET autocommit = 1
(if the value is not already 1),START TRANSACTION
,UNLOCK TABLES
.UNLOCK TABLES
commits a transaction only if any tables currently have been locked withLOCK TABLES
to acquire nontransactional table locks. A commit does not occur forUNLOCK TABLES
followingFLUSH TABLES WITH READ LOCK
because the latter statement does not acquire table-level locks.Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a
START TRANSACTION
statement or one of its synonyms.Statements that cause an implicit commit cannot be used in an XA transaction while the transaction is in an
ACTIVE
state.The
BEGIN
statement differs from the use of theBEGIN
keyword that starts aBEGIN ... END
compound statement. The latter does not cause an implicit commit. See Section 13.6.1, “BEGIN ... END Compound-Statement Syntax”.Data loading statements.
LOAD DATA
.LOAD DATA
causes an implicit commit only for tables using theNDB
storage engine.Administrative statements.
ANALYZE TABLE
,CACHE INDEX
,CHECK TABLE
,FLUSH
,LOAD INDEX INTO CACHE
,OPTIMIZE TABLE
,REPAIR TABLE
,RESET
(but notRESET PERSIST
).Replication control statements.
START SLAVE
,STOP SLAVE
,RESET SLAVE
,CHANGE MASTER TO
.
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-implicit-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.