MySQL Reference Manual

MySQL Transactional and Locking Commands

BEGIN/COMMIT/ROLLBACK Syntax

By default, MySQL runs in autocommit mode. This means that as soon as you execute an update, MySQL will store the update on disk.

If you are using transaction-safe tables (like InnoDB and BDB) you can put MySQL into non-autocommit mode with the following command:

SET AUTOCOMMIT=0

After this 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 switch from AUTOCOMMIT mode for one series of statements, you can use the BEGIN or BEGIN WORK statement:

BEGIN;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

Note that if you are using non-transaction-safe tables, the changes will be stored at once, independent of the status of the autocommit mode.

If you do a ROLLBACK when you have updated a non-transactional table you will get an error (ER_WARNING_NOT_COMPLETE_ROLLBACK) as a warning. All transactional safe tables will be restored but any non-transactional table will not change.

If you are using BEGIN 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 which are rolled back are not stored. See Section 4.9.4.

The following commands automatically 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 TABLE	RENAME TABLE
TRUNCATE

You can change the isolation level for transactions with SET TRANSACTION ISOLATION LEVEL .... See Section 6.7.3.

LOCK TABLES/UNLOCK TABLES Syntax

LOCK TABLES tbl_name [AS alias] {READ | [READ LOCAL] | [LOW_PRIORITY] WRITE}
            [, tbl_name {READ | [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 automatically unlocked when the thread issues another LOCK TABLES, or when the connection to the server is closed.

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, 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 an 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 are, 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 particular lock until there are no threads that want 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 4.5.5.

Note that you should not lock any tables that you are using with INSERT DELAYED because 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 other thread can update a READ-locked table and no other thread can read a WRITE-locked table.

  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/updating/deletes on MyISAM tables.

• If you are using a table handler 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 following example 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 6.3.6.2.

See Section 5.3.1, 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 4.5.3. This is a 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 automatically 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 behavior is to set the isolation level for the next (not started) transaction.

If you set the GLOBAL privilege it will affect all newly created threads. You will need the process privilege to do do this.

Setting the SESSION privilege will affect the following and all future transactions.

You can set the default isolation level for mysqld with --transaction-isolation=.... See Section 4.1.1.