Describe Transaction-Safe Table Types in MySQL
While using transactions in MySQL a transaction –safe table type must be used. MyISAM is the default table type. Other transaction-safe table types are InnoDB and BerkeleyDB. MyISAM is much faster and uses less disk space comparatively. However using BDB or InnoDB are much safer in scenarios like hardware failure or pc shut down.
Transaction-Safe Table Types in MySQL.
Transaction-Safe tables allow the transaction to be compact. It should either complete the transaction or revert or roll back all changes. This property makes transaction-safe tables more safe compared to non transaction safe tables. When an update is performed and it fails, all changes are reverted. These tables can provide a better concurrency of tables.
Describe Transaction-Safe Table Types in MySQL
To perform transaction in MySQL, a transaction-safe table must be created. The default MySQL table type – MyISAM does not support transactions. BerkeleyDB and InnoDB are the transaction-safe table types available in MySQL 3.23.34 and greater.
BerkeleyDBBerkeleyDB is a product from Sleepycat Software. This support began with 3.23.34 of MySQL and requires specific compilation requirements at the time of compiling MySQL from source. They rely on ISP. If MySQL installation supports BerkeleyDB tables, use TYPE=BDB definition to the create table. For example;
CREATE TABLE test (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
sample_text VARCHAR(25)) TYPE=BDB;
InnoDB:InnoDB is most popular and stable type of transaction-safe table in MySQL. It was designed for high performance where volumes of data is large and efficiency of CPU – which are two very important features of web development. It is not appeared until version 3.23.34 of MySQL. If MySQL installation supports InnoDB tables, simply suffix TYPE=InnoDb for creating table.
For Example:CREATE TABLE test (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
sample_text VARCHAR(25)) TYPE=InnoDB;
