Consistent:

Transactions preserve database consistency. A transaction transforms the 

database from a consistent state to another consistent state. 

Isolated:

A transaction s updates do not interfere with other transactions or other users 

of the database. Until a transaction completely succeeds, the database 

system conceals the individual updates from other transactions. 

Durable:

Once a transaction completes (commits), the updates survive in the 

database. 

MySQL supports transactions with the InnoDB and BDB transactional storage 

engines. InnoDB provides full ACID compliance.

Example 1 1   Transactional Operation

BEGIN 

INSERT INTO table1 ... 

DELETE FROM table2 ... 

COMMIT

SAM tables

This table type uses the 

Index Sequential Access Method 

(ISAM) with a B tree 

index to  navigate  through the table. The index is stored in a file with the 

.ISM

extension, and the data is stored in a file with the 

.ISD

 extension. 

ISAM tables have the following properties: 

Compressed and fixed length keys

Fixed and dynamic record length

16 keys with 16 key parts per key

Max key length 256

Data is stored in machine format; therefore, machine or OS dependent

ISAM tables have some major disadvantages:

Not binary portable across OS/platforms

Only table sizes < 4 G supported

MyISAM tables

The MyISAM table type, based on ISAM code, was enhanced to overcome the 

disadvantages of the ISAM table, and to provide some more useful extensions. 

MyISAM is the default table type of MySQL since version 3.23. The index is 

stored in a file with the 

.MYI 

extension, and the data is stored in a file with the 

.MYD

 extension.

 Chapter 1. Introduction 

35