Using Transactions in MySQL


To use transactions as demonstrated in the preceding section, you must be using a transaction-safe table type ”either InnoDB or BDB. There are various pieces of syntax that will get the same effect.

The syntax START TRANSACTION is synonymous with BEGIN or BEGIN WORK . You may like to use one of these forms to make your code more compatible with another database you use, but because START TRANSACTION is the SQL-99 syntax, it is generally recommended.

Setting the Autocommit Mode

Normally, MySQL runs in autocommit mode. Each query you run is effectively isolated in a transaction. You can think of it as adding START TRANSACTION and COMMIT to each of your queries for you. A couple of simple queries like these

 
 update account set balance = balance - 1000 where number = 2; update account set balance = balance + 1000 where number = 1; 

will be run as though they were written like this:

 
 start transaction; update account set balance = balance - 1000 where number = 2; commit; start transaction; update account set balance = balance + 1000 where number = 1; commit; 

Note that if you manually type

 
 start transaction; 

normally nothing will be committed until you manually type

 
 commit; 

You can disable the autocommit behavior using the SET command as follows :

 
 set autocommit=0; 

As you would probably guess, the following command will put MySQL back into autocommit mode:

 
 set autocommit=1; 

The autocommit variable is local to a single session, so changing the mode will affect only queries run from your session and only for as long as your session is connected.

If you turn autocommit off, you will not need to call START TRANSACTION to start a transaction. It is very important, though, that you remember to call COMMIT periodically to commit any changes you have made to the database.

Regardless of whether autocommit is on or off, there are times when your changes will automatically be committed. If you are using a non-transaction-safe table type, such as MyISAM, all of your changes will be committed immediately, regardless of the autocommit setting. You can happily group your statements with START TRANSACTION and COMMIT ; it is just that this will have no effect on the non-transaction-safe tables. You can even call ROLLBACK . It will not give an error ”it will just have no effect on anything you have altered in a non-transaction-safe table. This might be useful if you are testing code or reloading a dump file on servers with different table types.

For transaction-safe tables, there are actions (other than typing COMMIT ) that will automatically trigger a COMMIT . Requesting a lock will implicitly commit any outstanding queries.

Using Locks

An alternative way of obtaining some of the benefits of a transaction is to manually lock and unlock tables.

If we really wanted to write our bank account deposit code as two separate transactions, we could implement it as follows:

 
 lock tables account write; select balance from account where number = 2; update account set balance = 1500 where number = 2; unlock tables; 

A call to LOCK TABLES tries to lock any tables you list so that the current thread can work with it without interference. A call to UNLOCK TABLES releases any locks that this thread holds. Unlocking is straightforward. The only thing to note about it is that if you have locked a table, you should unlock it as soon as possible to limit the impact on other threads. Locking is a more complicated issue.

You need to request all the locks you need at once. The preceding example requested only one, but if we intended to access multiple tables or even multiple aliases to the one table, we would need to add them to the same call, for example:

 
 lock tables account write, account as a read, othertable low_priority write; 

Calling LOCK TABLES releases all locks you currently hold, so if you attempt to collect the locks you need over multiple statements, you will release all the early ones and will only actually hold the locks requested in the final statement.

There are two main types of locks: read and write. If you need access to a table to write, and you cannot allow other threads to use the table at the same time, a write lock will stop any other thread from reading or writing to the table until you release it. A read lock is less extreme. If you only intend to read from a table, there is no harm in allowing other threads to read at the same time. A read lock bars other threads only from writing to the table during the period that your thread holds the lock.

A write lock can also be marked as low_priority . Any system that distributes locks, including MySQL, needs a policy to decide who gets locks first when there are conflicting demands. MySQL generally gives write lock requests priority over read lock requests to ensure that updates to the stored data are made as soon as possible. If you do not want this behavior, you can request a low-priority write lock, as we did for the table named othertable in the preceding example. There is a catch, though. Whenever you request a lock, you may have to wait before it is granted. A low-priority lock will be granted only if there are no other threads requesting read or write locks on that table. It is possible on a busy server that this might never happen.

You will probably not often manually control locking in this way, but there are some reasons to do it. If you have an application that requires very high performance but needs transaction-like behavior only occasionally, it might be worth using a fast non-transaction-safe table type and using locks to solve your transaction issue.

Another common instance in which you would call LOCK TABLES is while manipulating MySQL's data files directly. For instance, if you wanted to ensure that the disk files stayed consistent and unmodified while you backed them up, you would need to lock them.

The most important thing to bear in mind about locking is that you should release your locks as soon as possible because other systems and users will be kept waiting. Some tasks you may lock while performing, such as reindexing or backing up large files, can take significant amounts of time.



MySQL Tutorial
MySQL Tutorial
ISBN: 0672325845
EAN: 2147483647
Year: 2003
Pages: 261

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net