6.7 MySQL Transactional and Locking Commands

6.7.1 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

After disabling autocommit mode by setting the AUTOCOMMIT variable to zero, you must use COMMIT to store your changes to disk or ROLLBACK if you want to ignore the changes you have made since the 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;

BEGIN and BEGIN WORK can be used instead of START TRANSACTION to initiate a transaction. START TRANSACTION was added to MySQL 4.0.11; it is SQL-99 syntax and is the recommended way to start 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.

If you issue a ROLLBACK statement after updating a non-transactional table, you will get an error (ER_WARNING_NOT_COMPLETE_ROLLBACK) as a warning. All transaction-safe tables will be restored but 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. See section The Binary Log.

The following commands implicitly end a transaction (as if you had done a COMMIT before executing the command):

UNLOCK TABLES also ends a transaction if any tables currently are locked.

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.

You can change the isolation level for transactions with SET TRANSACTION ISOLATION LEVEL .... See section SET TRANSACTION Syntax.

6.7.2 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

This statement sets a named transaction savepoint whose name is identifier. If the current transaction already has a savepoint with the same name, the old savepoint is deleted and a new one is set.

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.

6.7.3 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

LOCK TABLES locks tables for the current thread. UNLOCK TABLES releases any locks held by the current thread. All tables that are locked by the current thread are implicitly unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.

To use LOCK TABLES in MySQL 4.0.2 you need the global LOCK TABLES privilege and a SELECT privilege on the involved tables. In MySQL 3.23 you need to have SELECT, insert, DELETE and UPDATE 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.

If a thread obtains a READ lock on a table, that thread (and all other threads) can only read from the table. If a thread obtains a WRITE lock on a table, then only the thread holding the lock can 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. See section KILL Syntax.

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. See section Miscellaneous Functions.

See How MySQL Locks Tables, 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. See section FLUSH Syntax. 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.

6.7.4 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. You will 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 InnoDB and SET ... TRANSACTION ISOLATION LEVEL ....

You can set the default global isolation level for mysqld with --transaction-isolation=.... See section mysqld Command-line Options.

Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:45