What MySQL and mSQL Lack (MySQL & mSQL)

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.

NOTE

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.

7.2.2. Triggers

Triggers are a feature closely related to transactions. To carry the traffic analogy one step further, imagine a police officer sitting on a hill overlooking the intersection. Should one of the cars do something illegal, the officer enters traffic and pursues the offending car.

A trigger is one or more SQL statements stored in the database that are executed whenever some predefined event occurs. Triggers are a method of automating monitoring tasks. Whenever a certain condition is met, the trigger can act upon data or simply report that the triggering event took place.

7.2.3. Stored Procedures

At their simplest, stored procedures are simply one or more SQL statements stored in the database under some simple name to encapsulate a certain behavior. In the example of the account transfer mentioned above, you could simply store those two SQL statements as a single stored procedure called "transfer." Your application passes the stored procedure the two account numbers and an amount and it executes those two SQL statements in a single transaction.

On a more complex level, stored procedures may add to the basic SQL syntax so that it looks more like a traditional programming language. Oracle's PL/SQL and Sybase/Microsoft's TransactSQL are two examples of such SQL extensions. You may often hear of people using these kinds of stored procedures to "put the business logic in the database."

7.2.4. Subselects

The standard SQL SELECT statement enables complete access to all of the data stored within a table -- if you know what you are looking for. Unless you are willing to retrieve the entire contents of the table, the most basic form of SELECT requires you to input at least a portion of the data you wish to retrieve. For example, SELECT name FROM friends WHERE name LIKE'B%' requires you to know at least one letter of the name you are looking for. More specifically, what if you wanted to know who was making more than the average salary? The query would look something like:

SELECT name FROM people WHERE salary > ???

Greater than what? You do not have any idea what the average salary is unless you select that! You need to take the value of SELECT AVG(salary) FROM people and plug it into the earlier query. A subselect enables you to do this:

SELECT name 
FROM people
WHERE salary > (SELECT AVG(salary) FROM people)

7.2.5. Objects

Relational databases are not the end of the line for databases. You will also find plenty of object-relational and object-oriented databases. In the high-end market, the idea of a pure relational database is slowly disappearing. The new SQL3 standard will incorporate many of the changes in the high-end market for support of objects.

To a Relational Database Management (RDBMS), all data is stored in tables, which are simply a list of records, which in turn are collections of bits that represent text, numbers, or other kinds of data. In an Object-oriented Database Management System (OODBMS), the fundamental unit of data is an object. An object may not only contain the kinds of data found in relational systems, but it may contain other objects or multidimensional data like arrays or even executable functions -- more commonly called methods in the object world.