Introduction

Storage engines are the core components of a database management system responsible for handling data operations such as insertion, deletion, modification, and querying. Different storage engines offer distinct storage mechanisms, indexing strategies, and other functionalities. MySQL's architecture is built around its storage engines, allowing users to select or modify the engine according to their specific requirements. The available storage engines can be listed using the SHOW ENGINES command.

Common Storage Engines

Among the various storage engines, InnoDB and MyISAM are the most frequently used. InnoDB is the default engine in modern MySQL versions, known for its support for transactions, row-level locking, and foreign keys. MyISAM, on the other hand, is known for its speed and is suitable for read-heavy applications.

Operations

To manage storage engines, several SQL commands are available:

• View Default Storage Engine: The default storage engine can be checked using SHOW VARIABLES LIKE '%storage_engine%';. Note that prior to MySQL 5.5, MyISAM was the default, but it was replaced by InnoDB in later versions.
• Check Engine for a Table: The engine used by a specific table can be identified by examining the ENGINE attribute in the output of SHOW CREATE TABLE tablename;.
• Specify Engine During Table Creation: When creating a new table, the storage engine can be defined using the ENGINE clause: CREATE TABLE ... ENGINE=enginename;.
• Alter Table Engine: The storage engine of an existing table can be changed with the ALTER TABLE statement: ALTER TABLE tablename ENGINE=enginename;.

Transactions

Introduction

Transactions are a mechanism to ensure data integrity by grouping a series of SQL statements into a single unit of work. In MySQL, transactions are implemented by the storage engine, and currently, only the InnoDB engine fully supports transactional operations.

Purpose

The primary purpose of transactions is to guarantee that a set of SQL statements, such as INSERT, UPDATE, or DELETE, either all execute successfully or all fail, maintaining the database's consistency. By default, MySQL operates in auto-commit mode, where each SQL statement is treated as a separate transaction.

Operations

Transactions involve several key operations:

• Start Transacsion: A transaction begins with the first DML (Data Manipulation Language) statement or explicitly with START TRANSACTION or BEGIN.
• Commit Transaction: If all statements within the transaction execute successfully, the changes are permanently saved to the database using COMMIT.
• Rollback Transaction: If an error occurs or the operation needs to be canceled, the changes can be undone using ROLLBACK.

Auto-commit mode can be controlled with the SET autocommit command. Disabling auto-commit (SET autocommit=0;) allows multiple statements to be grouped in to a single transaction.

Example:

-- Create a database and table for demonstration
CREATE DATABASE transaction_demo;
USE transaction_demo;
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    holder_name VARCHAR(50),
    balance DECIMAL(10, 2)
);

-- Insert sample data
INSERT INTO accounts (account_id, holder_name, balance) VALUES (101, 'Alice', 1500.00);
INSERT INTO accounts (account_id, holder_name, balance) VALUES (102, 'Bob', 2500.00);

-- Transfer funds between accounts using a transaction
SET autocommit=0; -- Disable auto-commit
START TRANSACTION; -- Begin transaction
UPDATE accounts SET balance = balance - 500.00 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 500.00 WHERE account_id = 102;
COMMIT; -- Commit the transaction
-- ROLLBACK; -- Uncomment to roll back the transaction
SET autocommit=1; -- Re-enable auto-commit

ACID Properties

Transactions adhere to the ACID properties:

• Atomicity: Ensures that all operations within a transaction are treated as a single, indivisible unit. Either all operations are completed, or none are.
• Consistency: Guarantees that the database remains in a valid state before and after the transaction, adhering to all defined rules and constraints.
• Isolation: Prevents interference between concurrent transactions, ensuring that each transaction appears to be executed in isolation from others.
• Durability: Once a transaction is committed, its changes are permanent and survive any subsequent failures.

Transaction Isolation Levels

Isolation levels define the degree to which one transaction must be isolated from the effects of others. MySQL supports four isolation levels, each with different trade-offs between consistency and performance.

• Read Uncommitted: Allows a transaction to read data modified by other transactions that have not yet been committed. This can lead to "dirty reads."
• Read Committed: A transaction can only read data that has been committed by other transactions. This prevents dirty reads but can lead to "non-repeatable reads."
• Repeatable Read: Ensures that if a row is read twice in the same transaction, the value is the same. This is the default isolation level in MySQL. However, it can still lead to "phantom reads."
• Serializable: The highest isolation level, which completely isolates transactions from one another. This prevents all concurrency issues but can significantly impact performance.

To view the current isolation level:

SHOW VARIABLES LIKE '%isolation%';

To set the isolation level for the current session:

-- Set to Read Uncommitted
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Set to Read Committed
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set to Repeatable Read
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Set to Serializable
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;