HomeMySQL Page 5 - Using Transactions In MySQL (Part 2)
Locks and Keys - 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.
Thus far, I've been working with the InnoDB and BDB table types, which natively support transactions. However, these two table types are relatively new to MySQL. In many situations, users are still limited to the older MyISAM table type, which does not support transactions and commits table changes immediately, with no mechanism for rollback in case of error. Implementing a transactional environment with such tables is, therefore, a challenging problem, and one which can only be solved - and that too, partially - through the use of table locks.
In order to understand this, a little background is necessary. You've already seen that MySQL does not allow a session to view the changes made by other in-progress transactions to avoid data corruption and faulty calculations. MySQL accomplishes this by locking the rows being changed during a transaction. MySQL supports a number of different table types, and each one uses a different locking mechanism.
Table locks: Table locks apply to a table as a whole, and are used by MyISAM tables. While a table is locked by a client, other clients will not be able to write from it and (depending on the nature of the lock) may not even be able to read it.
Page locks: Page locks apply to a block of records in a table, and are used by BDB tables. When a client sets a page lock on a set of rows, other clients will not be able to access the locked rows, though they can still access other, unlocked rows within the same table.
Row locks: Row locks are set on a per-row basis, and are used by InnoDB tables. Though these locks are the most flexible, allowing maximum access to the rows in a table by multiple clients, they also require the maximum amount of system resources.
In BDB and InnoDB tables, the relatively more-precise locking mechanisms make it possible for multiple sessions to access the same tables without too many conflicts. Since MyISAM tables only support table locks, simulating a transactional environment with these tables usually implies the use of table locks to block more than one session from making changes to the table at a time.
/ 41 
