Storage Engines

MySQL supports multiple storage engines, with InnoDB and MyISAM being the most commonly used. 
 ------------------------------------------------------------------------------------------------------------------------------------------------ 
 MyISAM 
 
 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 
 
 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. 
 
 
 
 
 
 
 
 ====================================================================================