MySQL Transactional and Locking Commands
========================================
`START TRANSACTION', `COMMIT', and `ROLLBACK' Syntax
----------------------------------------------------
By default, MySQL runs in autocommit mode. This means that as soon as
you execute a statement that updates (modifies) a table, MySQL will
store the update on disk.
If you are using transaction-safe tables (like `InnoDB' or `BDB'), you
can put MySQL into non-autocommit mode with the following command:
SET AUTOCOMMIT=0
beginning of
your transaction.
If you want to disable autocommit mode for a single series of
statements, you can use the `START TRANSACTION' statement: :
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;
an ad-hoc
transaction. `BEGIN' and `BEGIN WORK' are available from MySQL 3.23.17
and 3.23.19, respectively.
Note that if you are not using transaction-safe tables, any changes
will be stored at once, regardless of the status of autocommit mode.
any
non-transaction-safe table will not change.
If you are using `START TRANSACTION' or `SET AUTOCOMMIT=0', you should
use the MySQL binary log for backups instead of the older update log.
Transactions are stored in the binary log in one chunk, upon `COMMIT',
to ensure that transactions that are rolled back are not stored. *Note
Binary log::.
You can change the isolation level for transactions with `SET
TRANSACTION ISOLATION LEVEL ...'. *Note SET TRANSACTION::.
Statements That Cannot Be Rolled Back
-------------------------------------
Some statements cannot be rolled back. In general, these will include
data definition language (DDL) statements, such as those that create or
drop databases, or those that create, drop, or alter tables.
You may wish to design your transactions not to include such statements.
If you issue a statement that cannot be rolled back early in a
transaction, and then another statement later fails, the full effect of
the transaction cannot be rolled back by issuing a `ROLLBACK' statement.
Statements That Cause an Implicit Commit
----------------------------------------
The following commands implicitly end a transaction (as if you had done
a `COMMIT' before executing the command):
*Command* *Command* *Command*
`ALTER TABLE' `BEGIN' `CREATE INDEX'
`DROP DATABASE' `DROP INDEX' `DROP TABLE'
`LOAD MASTER `LOCK TABLES' `RENAME TABLE'
DATA'
`SET `START `TRUNCATE'
AUTOCOMMIT=1' TRANSACTION'
`UNLOCK TABLES' also ends a transaction if any tables currently are
locked. Prior to MySQL 4.0.13, `CREATE TABLE' ends a transaction if
the binary update log is enabled.
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.
`SAVEPOINT' and `ROLLBACK TO SAVEPOINT' Syntax
----------------------------------------------
Starting from MySQL 4.0.14 and 4.1.1, `InnoDB' supports the SQL commands
`SAVEPOINT' and `ROLLBACK TO SAVEPOINT'.
SAVEPOINT identifier
ROLLBACK TO SAVEPOINT identifier
This statement rolls back a transaction to the named savepoint.
Modifications that this transaction made to rows after the savepoint
was set are undone in the rollback, but `InnoDB' does *not* release the
row locks that were stored in memory after the savepoint. (Note that
for a new inserted row, the lock information is carried by the
transaction ID stored in the row; the lock is not separately stored in
memory. In this case, the row lock is released in the undo.)
Savepoints that were set at a later time than the named savepoint are
deleted.
If the command returns the following error, it means that no savepoint
with the specified name exists:
ERROR 1181: Got error 153 during ROLLBACK
All savepoints of the current transaction are deleted if you execute a
`COMMIT', or a `ROLLBACK' that does not name a savepoint.
`LOCK TABLES' and `UNLOCK TABLES' Syntax
----------------------------------------
LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
issues another `LOCK TABLES', or when the connection to the server is
closed.
privileges for the tables.
The main reasons to use `LOCK TABLES' are for emulating transactions or
getting more speed when updating tables. This is explained in more
detail later.
read from or
write to the table. Other threads are blocked.
The difference between `READ LOCAL' and `READ' is that `READ LOCAL'
allows non-conflicting `INSERT' statements to execute while the lock is
held. This can't however be used if you are going to manipulate the
database files outside MySQL while you hold the lock.
When you use `LOCK TABLES', you must lock all tables that you are going
to use and you must use the same alias that you are going to use in
your queries! If you are using a table multiple times in a query (with
aliases), you must get a lock for each alias!
`WRITE' locks normally have higher priority than `READ' locks, to
ensure that updates are processed as soon as possible. This means that
if one thread obtains a `READ' lock and then another thread requests a
`WRITE' lock, subsequent `READ' lock requests will wait until the
`WRITE' thread has gotten the lock and released it. You can use
`LOW_PRIORITY WRITE' locks to allow other threads to obtain `READ'
locks while the thread is waiting for the `WRITE' lock. You should only
use `LOW_PRIORITY WRITE' locks if you are sure that there will
eventually be a time when no threads will have a `READ' lock.
`LOCK TABLES' works as follows:
1. Sort all tables to be locked in a internally defined order (from
the user standpoint the order is undefined).
2. If a table is locked with a read and a write lock, put the write
lock before the read lock.
3. Lock one table at a time until the thread gets all locks.
This policy ensures that table locking is deadlock free. There is
however other things one needs to be aware of with this schema:
If you are using a `LOW_PRIORITY WRITE' lock for a table, this means
only that MySQL will wait for this particlar lock until there is no
threads that wants a `READ' lock. When the thread has got the `WRITE'
lock and is waiting to get the lock for the next table in the lock
table list, all other threads will wait for the `WRITE' lock to be
released. If this becomes a serious problem with your application, you
should consider converting some of your tables to transaction safe
tables.
You can safely kill a thread that is waiting for a table lock with
`KILL'. *Note KILL::.
Note that you should *not* lock any tables that you are using with
`INSERT DELAYED'. This is because that in this case the `INSERT' is
done by a separate thread.
Normally, you don't have to lock tables, as all single `UPDATE'
statements are atomic; no other thread can interfere with any other
currently executing SQL statement. There are a few cases when you would
like to lock tables anyway:
* If you are going to run many operations on a bunch of tables, it's
much faster to lock the tables you are going to use. The downside
is, of course, that no thread can update a `READ'-locked table
(including the one holding the lock) and no thread can read a
`WRITE'-locked table other than the one holding the lock.
The reason some things are faster under `LOCK TABLES' is that
MySQL will not flush the key cache for the locked tables until
`UNLOCK TABLES' is called (normally the key cache is flushed after
each SQL statement). This speeds up inserting/updateing/deletes on
`MyISAM' tables.
* If you are using a storage engine in MySQL that doesn't support
transactions, you must use `LOCK TABLES' if you want to ensure that
no other thread comes between a `SELECT' and an `UPDATE'. The
example shown here requires `LOCK TABLES' in order to execute
safely:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
mysql> UPDATE customer SET total_value=sum_from_previous_statement
-> WHERE customer_id=some_id;
mysql> UNLOCK TABLES;
Without `LOCK TABLES', there is a chance that another thread might
insert a new row in the `trans' table between execution of the
`SELECT' and `UPDATE' statements.
By using incremental updates (`UPDATE customer SET
value=value+new_value') or the `LAST_INSERT_ID()' function, you can
avoid using `LOCK TABLES' in many cases.
You can also solve some cases by using the user-level lock functions
`GET_LOCK()' and `RELEASE_LOCK()'. These locks are saved in a hash
table in the server and implemented with `pthread_mutex_lock()' and
`pthread_mutex_unlock()' for high speed. *Note Miscellaneous
functions::.
See *Note Internal locking::, for more information on locking policy.
You can lock all tables in all databases with read locks with the
`FLUSH TABLES WITH READ LOCK' command. *Note FLUSH::. This is very
convenient way to get backups if you have a filesystem, like Veritas,
that can take snapshots in time.
*NOTE*: `LOCK TABLES' is not transaction-safe and will implicitly
commit any active transactions before attempting to lock the tables.
`SET TRANSACTION' Syntax
------------------------
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
Sets the transaction isolation level for the global, whole session or
the next transaction.
The default behaviour is to set the isolation level for the next (not
started) transaction. If you use the `GLOBAL' keyword, the statement
sets the default transaction level globally for all new connections
created from that point on (but not existing connections). You need
the `SUPER' privilege to do this. Using the `SESSION' keyword sets the
default transaction level for all future transactions performed on the
current connection.
For description of each `InnoDB' transaction isolation level, see *Note
InnoDB transaction isolation::. InnoDB supports each of these levels
from MySQL 4.0.5 on. The default level is `REPEATABLE READ'.
You can set the default global isolation level for `mysqld' with
`--transaction-isolation=...'. *Note Command-line options::.
[Назад] [Содержание] [Вперед]
| Главная |