# Storage Engines

MySQL supports multiple storage engines, with InnoDB and MyISAM being the most commonly used.

\------------------------------------------------------------------------------------------------------------------------------------------------

#### MyISAM

1. **Table-Level Locking:**
    - **Description:**
        - MyISAM uses table-level locking, which means that the entire table is locked for the duration of a read or write operation.
    - **Types of Locks:**
        - **Read Lock (Shared Lock):**
            - Multiple read operations can occur simultaneously.
            - Write operations are blocked until all read locks are released.
        - **Write Lock (Exclusive Lock):**
            - Only one write operation can occur at a time.
            - All other read and write operations are blocked until the write lock is released.
    - **Implications:**
        - **Advantages:**
            - Simpler to implement and manage.
            - Can be faster for read-heavy workloads with fewer write operations.
        - **Disadvantages:**
            - Poor concurrency for write-heavy workloads.
            - Write operations can block reads, leading to potential bottlenecks.

\------------------------------------------------------------------------------------------------------------------------------------------------

#### InnoDB

1. **Row-Level Locking:**
    - **Description:**
        - InnoDB uses row-level locking, which means that only the specific rows involved in a transaction are locked.
    - **Types of Locks:**
        - **Shared Lock (S Lock):**
            - Allows multiple transactions to read the same rows simultaneously.
            - Prevents other transactions from writing to the locked rows.
        - **Exclusive Lock (X Lock):**
            - Prevents other transactions from reading or writing to the locked rows.
        - **Intent Locks:**
            - **Intent Shared Lock (IS Lock):**
                - Indicates that a transaction intends to read rows in a table.
                - Compatible with other IS locks and S locks, but not with IX or X locks.
            - **Intent Exclusive Lock (IX Lock):**
                - Indicates that a transaction intends to write rows in a table.
                - Compatible with other IX locks, but not with S or X locks.
    - **Implications:**
        - **Advantages:**
            - Higher concurrency as only specific rows are locked.
            - Better performance for mixed read/write workloads.
            - Supports transactions and ACID compliance.
        - **Disadvantages:**
            - More complex locking mechanism.
            - Slightly higher overhead compared to table-level locking.

====================================================================================