Databases Reference
In-Depth Information
+-----------+---------------------------+
| 1 | New Order |
| 2 | Nick Cave & The Bad Seeds |
| 3 | Miles Davis |
| 4 | The Rolling Stones |
| 5 | The Stone Roses |
| 6 | Kylie Minogue |
| 7 | The Beatles |
| 8 | The Cure |
+-----------+---------------------------+
8 rows in set (0.06 sec)
You can see in the second statement that we've misspelled the name of the band The
Who, so we've decided to
ROLLBACK
the transaction. You can see that the rollback was
successful, since the
SELECT
statement shows the artist wasn't added.
BDB
The Berkeley Database (BDB) table type can survive the same types of database crashes
as the InnoDB table type, and also has the
COMMIT
and
ROLLBACK
functionality we showed
you in the previous section. The Berkeley DB software itself is developed by Sleepycat
Software (
http://www.sleepycat.com
)
and is modified to work with MySQL. While Ber-
keley DB is very stable and is used by over 200 million installations in a very wide range
of products, the interface between MySQL and Berkeley DB is still under development
—and so the MySQL BDB table type is not yet widely used. Inbuilt support for this
table type was dropped from MySQL version 5.1.12 onward. If you really need to use
this table type, you can add support for it as a plug-in storage table, but that's outside
the scope of this topic.
This table type includes the following features:
Transaction support
See our earlier description of transactions in “Transactions and Locking.”
Advanced crash-recovery features
See our earlier description in “InnoDB” for a discussion of logging and recovery.
Page-level locking
We've explained how MyISAM and InnoDB lock at the table and row levels, re-
spectively. The BDB locking philosophy lies somewhere between the two, locking
typically a block of rows that reside in a physical disk-drive block.
Fast primary-key indexing
The primary key index is stored with the data, and MySQL can avoid accessing the
data itself if you require only columns that are part of the primary key. However,
this also means that you must have a primary-key index. MySQL will automatically
create a hidden five-byte primary key if you don't specify one. It's also slower to
scan all rows in a table if required for a query.
The BDB type has the following limitations:
