Home arrow MySQL arrow Page 5 - Using Transactions In MySQL (Part 2)

Locks and Keys - MySQL

mysqlThis 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.

  1. Using Transactions In MySQL (Part 2)
  2. Isolating Yourself
  3. The Three Rís
  4. Peeping Tom
  5. Locks and Keys
  6. Nothing Like the Real Thing
  7. Holding Pattern
  8. Timberrrrrrrrrr!
  9. Perl of Wisdom
  10. End Work
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 41
December 22, 2003

print this article


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.

Let's look at a quick example.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: