Storage Engines

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

------------------------------------------------------------------------------------------------------------------------------------------------

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.

------------------------------------------------------------------------------------------------------------------------------------------------

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.

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