HomeMySQL Page 7 - Using Transactions In MySQL (Part 2)
Holding Pattern - 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.
Obviously, this means that other users are blocked from the table while the transaction is in progress and the lock is active. This is clearly illustrated in the following snippet, which shows client B waiting until client A finishes with its tables and releases its locks.
This is the primary downside of using table locks to simulate a transactional environment with MyISAM tables; with long transactions, it can result in a significant speed reduction as different sessions "wait" for the session initiating the transaction to release its locks on various tables. Therefore, it's a good idea to always make sure that your transactions are small in size and execute quickly, else you'll have one very annoyed DBA after you!
This type of table locking is typically used in legacy MySQL environments where the new transactional formats are not supported; new users who need transactions will typically head straight for the InnoDB format, which eliminates both the problems above.
/ 41 
