So far, you’ve seen transactions in the context of InnoDB tables, the only native MySQL storage engine to support ACID-compliant transactions. The older MySQL table types, still in use in many MySQL installations, do not support transactions, but MySQL still enables users to implement a primitive form of transactions through the use of table locks. This section examines these “pseudo-transactions” in greater detail, with a view to offering some general guidelines on performing secure transactions with nontransactional tables.

MySQL supports a number of different table types, and the locking mechanisms available differ from type to type. Therefore, a clear understanding of the different levels of locking available is essential to implementing a pseudo-transaction environment with MySQL’s nontransactional tables.

• Table locks. The entire table is locked by a client for a particular kind of access. Depending on the type of lock, other clients will not be allowed to insert records into the table, and could even be restricted from reading data from it.

• Page locks. MySQL will lock a certain number of rows (called a page) from the table. The locked rows are only available to the thread initiating the lock. If another thread wants to write to data in these rows, it must wait until the lock is released. Rows in other pages, however, remain available for use.

• Row locks. Row-level locks offer finer control over the locking process than either table-level locks or page-level locks. In this case, only the rows that are being used by the thread are locked. All other rows in the table are available to other threads. In multiuser environments, row-level locking reduces conflicts between threads, making it possible for multiple users to read and even write to the same table simultaneously. This flexibility must be balanced, however, against the fact that it also has the highest performance overhead of the three locking levels.

The MyISAM table type supports only table-level locking, which offers performance benefits over row- and page-level locking in situations involving a larger number of reads than writes. The InnoDB table type automatically performs row-level locking in transactions.

Source of Information : McGraw Hill - MySQL Database Usage and Administration


Subscribe to Developer Techno ?
Enter your email address:

Delivered by FeedBurner