HomeMySQL Page 6 - Using Transactions In MySQL (Part 2)
Nothing Like the Real Thing - MySQL
This concluding segment looks at the MySQL transactional model in a multi-user scenario, illustrating some of the data corruption problems that are likely to arise and explaining how to control them using MySQL's various isolation levels. It also includes a sample Perl application demonstrating transaction usage at the application level, and shows you how to emulate transactions with non-transactional MyISAM tables.
MyISAM tables support two types of table locks: read locks and write locks. A read lock means that the table is available to all clients only for reads; a write lock means that the table is only available to the client creating the lock for reads and writes, while all other clients will be denied access.
Table locks are initiated through the use of the LOCK TABLES command, which may be followed by one or more table names and the type of lock needed. This is illustrated below:
In this example, the LOCK TABLES command is used to lock all the tables needed by the "transaction" to successfully complete. Once the tables have been locked, SQL can be used to make changes to the tables without any fear that other sessions will interfere with the transaction. On successful completion, the tables are unlocked and other sessions can then view the changed data.
You'll notice that this type of simulated transaction does not include any recovery mechanism in case things go wrong; there is no way, for example, to ROLLBACK the changes made, and the durability of the transaction is also not as high as with InnoDB/BDB tables.