SQL Database

October 9, 2022 6:00 PM PDT

---

This meeting focused on the design and technical aspects of SQL databases, particularly comparing MySQL's storage engines, index types, transaction models, and concurrency control mechanisms. The discussion included best practices for database design and the implications of different isolation levels on data integrity.

Presenter: Pauline

Key Topics Discussed

SQL Database Fundamentals

• Storage Engines:

  • MyISAM vs InnoDB
    • MyISAM: Non-clustered index; primary and secondary keys are similar.
    • InnoDB: Clustered index; requires a primary key.

• Indexing:

  • Definition: Ordered storage of data or pointers to data.
  • Types of Searches:
    • Sequential search
    • Binary search
    • B+ tree search
  • B+ Tree Characteristics:
    • Internal nodes and leaf nodes.
    • Leaf nodes in MyISAM point directly to data addresses, while in InnoDB, they point to primary keys.

• Index Types:

  • Composite index: Order in columns saves space.
  • Multiple indexes: One index per column increases memory consumption.
  • Unique index and unique constraint.

Best Practices

• Avoid using long strings as primary keys.
• Choose columns for indexing that can quickly eliminate choices (e.g., avoid low-cardinality columns like gender).

Transaction Model

• Definition: A transaction involves multiple changes that must be treated as a single unit.
• Key Properties:
  • Atomicity
  • Consistency
  • Isolation
  • Durability
• Importance of consistency and challenges with concurrent access.

Concurrency Issues

• Problems arising from concurrent access:
  • Read-only operations are safe.
  • Write-read operations may lead to incorrect reads.
  • Write-write operations may result in lost updates.

Isolation Levels

• Different isolation levels and their implications:
  • Read Uncommitted: Issues with dirty reads/writes.
  • Read Committed: Potential for unrepeatable reads.
  • Repeatable Read: Can encounter phantom reads.
  • Serializable: Highest isolation level.

Concurrency Control Mechanisms

• Optimistic Concurrency Control:

  • Process: Read -> Compute -> Validate -> Write.

• Pessimistic Concurrency Control:

  • Process: Validate -> Read -> Compute -> Write.

• Lock-Based Protocols:

  • Shared lock (S) and exclusive lock (X).
  • Issues: Deadlocks and starvation.

• Timestamp-Based Protocols:

  • Write timestamp (W_TS) and read timestamp (R_TS).
  • Rollback conditions based on timestamps.

Write-Ahead Logging (WAL)

• Functions:
  • Add, delete, and update records in the WAL.
  • Intermediate updates and delayed updates.
• Types of WAL:
  • Undo WAL: Records old values for rollback.
  • Redo WAL: Records new values for recovery after a crash.

Conclusion

The meeting provided a comprehensive overview of SQL database design, focusing on indexing, transaction management, and concurrency control. Participants were encouraged to apply best practices in their database implementations to ensure data integrity and performance.