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 theseupdate 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 typestart transaction;normally nothing will be committed until you manually typecommit;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 ed 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.
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 theseupdate 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 typestart transaction;normally nothing will be committed until you manually typecommit;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 ed 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.
Transactional database management systems are generally striving for the same goals, using differing approaches. To isolate transactions, InnoDB uses a fine-grained, row-level locking mechanism. This means that different transactions can run on the same table at the same time as long as they are all only reading or do not use the same rows if they are writing.Uncommitted changes lock other threads out of only affected rows, not a whole table. This is one of the features that gives InnoDB high performance while delivering the kinds of features you expect from a modern RDBMS. One of these features, or sets of features, is ACID compliance.ACID Compliance
An important database term that we have not yet defined is the acronym ACID. ACID stands for Atomicity, Consistency, Isolation, and Durability. Much used to be made of the fact that MySQL using MyISAM tables did not pass the "ACID Test." Using InnoDB tables, MySQL is ACID compliant.Atomicity means that transactions are atomic and indivisible. Either all of a transaction's changes are stored in the database, or none of them are stored. In the event of an external error, it is obviously ideal if the recovery process can complete any transactions that were in progress at the time; however, it is also acceptable for those transactions to be completely rolled back.Consistency means that operations transform the database from one valid state to another. There are no intermediate stages where the data is inconsistent. The database should also disallow operations that violate consistency constraints. If you are storing bank accounts that relate to bank customers, it should not be possible to create an account for a customer who does not exist, and it should not be possible to delete a customer from the customers table if there are still accounts referring to them in the accounts table.Isolation means that transactions do not affect each other while they are running. Each transaction should be able to view the world as though it is the only one reading and altering things. In practice this is not usually the case, but locks are used to achieve the illusion. Depending on the database and option settings, you will have different levels of isolation in practice. (See the "Transaction Isolation" section in this chapter for more detail.)Durability means that after a transaction has been committed to the database, its effects are permanent. This would be a fairly simple requirement to satisfy in a simple program, but in a complex RDBMS that uses locking and multiversioning to allow concurrent multiuser access and caching to improve performance, it is a minefield. In addition, durability implies that we should be able to recover the current state of the database in the event of a failure. If a power failure, hard-disk crash, or other catastrophe occurs between a client sending a transaction to the database and that transaction being recorded on disk, then we should be able to combine a backup and a log to bring the database back to its precrash state and perhaps process transactions that had been logged but not yet executed or committed.If you are using InnoDB tables (or BerkeleyDB tables), MySQL is ACID compliant. Using the transaction syntax gives you atomicity. Transactions and foreign key constraints give you consistency. You can choose the level of isolation that transactions have from one another. The binary log and repair tools provide durability. (Using replication, you can have a highly durable system without any single point of failure.)Transaction Isolation
InnoDB tables can run in four different transaction isolation levels. In order from strongest to weakest, they areSerializableRepeatable readRead committedRead uncommittedAs with many options, you have a trade-off between robustness and performance.Serializable isolation is the ideal from a purity and robustness angle. With serializable isolation, reads and writes on the database should appear to be happening in a sequence, with changes from a write being completely recorded before the next read starts. Transactions will not always have to be performed in a noninterleaved sequence to achieve this appearance because many do not interfere with each other, but in cases in which there are clashes, they will. This locking and waiting, combined with the overhead of predicting which combinations of transactions will interfere, makes serializable isolation the slowest isolation mode. If you want to use this mode, this is the command to run:set transaction isolation level serializable;The default level for InnoDB is repeatable read. In this isolation mode, each transaction gets to work in an isolated version of the table where each row remains as it was when the transaction started. Reading a row is guaranteed to be repeatable. If you callselect * from account where number=1;at the start of the transaction and perform the same query later in the transaction, you will get the same results both times. You can, however, get what are called phantom reads. It is possible that another transaction which commits before yours is adding new rows to the table. If you perform the same query with a condition twice, such asselect * from account where balance>1000;it is possible that you will get new rows—phantom rows—the second time.In practice you should very rarely see phantom reads from MySQL. InnoDB uses an algorithm called next key locking to solve the problem, as long as the column that your condition applies to is indexed. You probably already know that InnoDB has row-level locking. When a transaction uses a row, it locks that row so that the transaction can be isolated from others. As well as locking the rows used, next key locking also locks the gaps between rows found in the index. Because phantom reads are addressed in this way, few systems really need to be put in serialized isolation mode.If you set the server to read committed, your transactions are no longer very isolated. If you perform a query and repeat it later in the same transaction, you will get different results the second time if another transaction has modified the data in the meantime and committed. Should you want to do this, the command isset transaction isolation level read committed;At the weakest isolation level, read uncommitted, it is distinctly arguable not only that your transactions are no longer isolated, consistent, and therefore ACID compliant, but that you no longer really have transactions. In this mode, it is possible for transactions to read changes that other transactions have made before the changes have been committed. This is called a dirty read. You would tolerate this only in fairly unusual circumstances, such as at a time when you know all active threads will be reading or writing, but not both. To enable read uncommitted mode, use this:set transaction isolation level read uncommitted;
alter table tablename type = InnoDB;
然后，新创建表采用create table ...... type=innodb
修改现有表采用楼上的方法就可以了