Overview

In this tell-all tutorial, Jay Greenspan, author of MySQL Weekend Crash Course and co-author of MySQL/PHP Database Applications, starts with a tour of the basics: He answers the age-old Q: “What’s the big deal with Transactions?”; investigates the four properties that a database must have to be considered transaction-capable; takes a closer look at locking mechanisms; and finishes up with a look at MyISAM tables, the lesser cousin of fully transaction-capable tables.

Once you have a taste of the limitations of MyISAM tables, you’ll be hungry for the real deal. In Lesson 2, Jay satiates that hunger with a thorough introduction to MySQL’s different transactional table types: BDB, Gemini, and InnoDB.

Get started: Lesson 1

(Note: This article is adapted from Jay’s new book, MySQL Weekend Crash Course look for it at a store near you. A book store! -Ed.)

MySQL has become the database of choice for many Web developers over the last few years and for good reason. It’s fast, free, easy to use, and has great community support.

But many experienced developers refused to touch MySQL because, they complained, the product didn’t implement features that were absolutely critical in an SQL server. MySQL’s most egregious omission, according to some, was its lack of transaction support. But thanks to recent developments in MySQL land, that’s no longer the case.

When it first hit the cyber-street, MySQL offered only one table type for data storage, the ISAM table now upgraded to the MyISAM type for all recent versions of MySQL. But MyISAM tables were limited. Very limited.

Then the folks from Sleepycat Software came into the picture. Sleepycat creates and sells a database storage engine which is used mostly with embedded devices. The storage engine comes with an API that allows developers to integrate Sleepycat’s data storage software into their products. And that’s just what the folks at MySQL did, they integrated the Berkeley DB (or BDB) table from Sleepycat. This was the first transactional table type included available to MySQL users.

Berkeley DB tables were followed shortly by two other transactional table types: InnoDB and Gemini. Gemini tables are adopted from another embedded storage mechanism; this one from NuSphere, a Progress Software property. InnoDB tables were designed specifically for MySQL.

But we’re getting ahead of ourselves here. Before we take a closer look at each of these different options, we need to start at square one: Why you want transaction support in the first place.

Continue Reading “Manage Transactions in MySQL – Lesson 1″ »

In Lesson 1 of this tutorial, you leaned how to approximate transaction-like behavior with MyISAM tables. In this lesson, you’ll see how you can achieve real transactions with each of MySQL’s three transactional table types: BDB, InnoDB, and Gemini. But before I get to specifics surrounding each table, I need to revisit the notion of locking.

Remember that MyISAM supports only table-level locks of two types: read locks and write locks. The transactional tables types offer more granular locking mechanisms: They can place locks on specific subsets of data. You’ll read more about each table’s locking mechanisms as I get to them in the following pages.