Oracle 5.0, Reference Manual

Page: 1131 / 3232

START TRANSACTION
,
COMMIT
, and
ROLLBACK
Syntax
1111
These statements provide control over use of transactions:
•
START TRANSACTION
or
BEGIN
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.
To disable autocommit mode implicitly for a single series of statements, use the
START TRANSACTION
statement:
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
With
START TRANSACTION
, autocommit remains disabled until you end the transaction with
COMMIT
or
ROLLBACK
. The autocommit mode then reverts to its previous state.
You can also begin a transaction like this:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
The
WITH CONSISTENT SNAPSHOT
option starts a consistent read for storage engines that are
capable of it. This applies only to
InnoDB
. The effect is the same as issuing a
START TRANSACTION
followed by a
SELECT
from any
InnoDB
table. See Section 14.2.7.2, “Consistent Nonlocking Reads” .
The
WITH CONSISTENT SNAPSHOT
option does not change the current transaction isolation level, so
it provides a consistent snapshot only if the current isolation level is one that permits consistent read
(
REPEATABLE READ
[1120] or
SERIALIZABLE
[1121] ).
Important
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 20, Connectors and APIs , or the documentation for your API, for more
information.
To disable autocommit mode explicitly, use the following statement:
SET autocommit=0;
After disabling autocommit mode by setting the
autocommit
[436] variable to zero, changes to
transaction-safe tables (such as those for
InnoDB
,
BDB
, or
NDBCLUSTER
) are not made permanent
immediately. You must use
COMMIT
to store your changes to disk or
ROLLBACK
to ignore the changes.
autocommit
[436] 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
[436] system variable at
Section 5.1.4, “Server System Variables” .
BEGIN
and
BEGIN WORK
are supported as aliases of
START TRANSACTION
for initiating a transaction.
START TRANSACTION
is standard SQL syntax and is the recommended way to start an ad-hoc
transaction.
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” .