home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

Book HomeMySQL and mSQLSearch this book

7.2. What MySQL and mSQL Lack

The word "lack" is chosen here in absence of a better term. As we noted earlier in the book, both MySQL and mSQL have consciously chosen to leave out features that will impact performance. In other words, for MySQL and mSQL, performance is the name of the game. Some mid-range users, however, may be willing to sacrifice a little performance for some other features. In order to understand what other mid-range databases offer, then, it helps to know the things that MySQL and mSQL have left out.


MySQL intends to eventually include some of these features with the ability to turn them off if you do not want them. At the time of printing, we are aware that Monty wants to implement a stored procedure mechanism and subselects and perhaps even transactions.

7.2.1. Transactions

Transactions enable you to group multiple SQL statements together as one unit of work. By grouping statements together, you can be certain that no one else will see a partially changed database. You also know that if one of the statements fail, the entire unit of work fails. One way of visualizing transactions is as the intersection of a busy road. A single-threaded queuing system, such as mSQL, is like having a four-way stop at the intersection. Each car takes its turn, one at a time. If two cars are in a caravan through the intersection, they risk being split up at the stop sign.

A multithreaded locking system, such as MySQL, is more like having a traffic officer instead of stop signs. The traffic can fly through the intersection in any order and at any speed and the officer intelligently makes sure there are no collisions. If two cars come through the intersection from opposite directions at the same time, the officer tells one of them to halt and wait for the other to get through the intersection.

Transactions resemble a stop-light system. Incoming traffic halts at a red light for a period while traffic traveling together in the other direction moves through the intersection.

A practical example might be a banking application where a transfer from savings to checking involves changing the balance in the savings and then changing the balance in the checking. This application might have these two SQL statements:

# Deduct $100 from the $110 in the savings account
UPDATE account
SET balance = 10.00
WHERE id = 1234
# Add $100 to the $55 in the checking account
UPDATE account
SET balance = 155.00
WHERE id = 5678

Between the two updates, another transaction could be issued by another client that checks the balance of the checking and savings accounts to see if there is enough money for a check. If that were to happen, the check would bounce. Worse still, if the server crashed between the two updates, your client would have just lost $100 to the bit bucket.

By wrapping those two statements in a transaction, you are saying that they both must succeed or fail together. If the first one succeeds but the second one fails, you can issue something called a "rollback" that returns the database to its state before you began the transaction. Similarly, no one else can touch the files you are modifying until you are done with your work.[10] MySQL lets you partially emulate transactions by using LOCK TABLES. Locks should work adequately for preventing corruption, but they do not provide the ability to roll back operations. mSQL has no transaction support.

[10]There are special nuances to this feature called "transaction isolation" values. Sometimes you may not care if people have read-only views of inconsistent data. By allowing them to do so, you speed up the database by not having them wait on transactions to complete.

Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.