InnoDB Gap Lock Mechanism

Gap locks are index interval locks that address phantom reads, active exclusively in InnoDB’s REPEATABLE READ isolation level. Instead of targeting existing index entries, they block operations on open ranges between index records, excluding the records themselves.

For a table with primary key values 10, 20, 30, valid gap lock ranges are (-∞,10), (10,20), (20,30), (30,+∞).

Key characteristics:

• Prevents phantom insersions into locked intervals
• Shared-compatible (multiple transactions can hold identical gap locks)
• No-op in READ COMMITTED
• Activates only when index conditions are used in FOR UPDATE, LOCK IN SHARE MODE, UPDATE, or DELETE statements

Consider a table emp with primary key emp_id populated with 2, 7, 14:

-- Transaction X
START TRANSACTION;
SELECT emp_name FROM emp WHERE emp_id >=7 AND emp_id <=14 FOR UPDATE;
-- Locks (7,14) and (14,+∞)

-- Transaction Y
INSERT INTO emp VALUES (9, 'Zoe'); -- ❌ Blocked
INSERT INTO emp VALUES (21, 'Liam'); -- ❌ Blocked

MySQL Storage Engine Overview

Engine	Core Traits
InnoDB	Default since 5.5; ACID-compliant, row-level locking, foreign keys, crash-safe
MyISAM	No transactions/row locks; fast read throughput, full-text support, no crash recovery
MEMORY	In-memory storage; lightning speed, volatile, max size limited by max_heap_table_size
ARCHIVE	High-compression, append-only; ideal for audit/historical logs, no indexes beyond primary
CSV	Plain-text comma-separated storage; no indexes, no transactions, easy data export
BLACKHOLE	Discards all writes, logs them; used for replication filtering/audit pipelines

Transaction Fundamentals & MySQL Support

A transaction is an atomic logical unit of database operations: all succeed or all roll back to maintain consistency.

MySQL enables transactions via:

• ACID-compliant engines (InnoDB, NDB Cluster)
• Control statements: START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT
• Isolation level configuration
• Undo/redo logs for crash recovery and rollback

ENUM Type Tradeoffs

Benefits

• Space-efficient: stored as 1-2 byte integers
• Human-readable values mapped internally
• Enforces predefined value constraints
• Sorts by creation order instead of lexicographical order (configurable via ORDER BY CAST(col AS CHAR) if needed)

Drawbacks

• Schema changes require ALTER TABLE to modify allowed values
• Non-portable across database systems
• Maximum 65,535 unique values
• Unintuitive sorting behavior by default

MySQL 5.7 Key Enhancements

Native JSON Support

CREATE TABLE customer_profile (
  cust_id INT AUTO_INCREMENT PRIMARY KEY,
  details JSON,
  INDEX idx_city ((JSON_UNQUOTE(details->>'$.residence.city')))
);

Includes JSON_SET(), JSON_REMOVE(), JSON_MERGE_PRESERVE(), and JSON-generated column indexing.

Performance Optimizations

• Enhanced query optimizer with cost model improvements
• innodb_file_per_table enabled by default
• InnoDB buffer pool dump/load on shutdown/startup
• More online DDL operations for ALTER TABLE
• Faster GROUP BY/ORDER BY execution

Replication Upgrades

• GTID-based replication for simplified failover
• Multi-source replication
• Semisynchronous replication improvements
• Fine-grained replication filtering with wildcards

Security Hardening

• Default root requires a strong password on installation
• Password expiration policies
• Encryption for specific column selections
• Improved SSL/TLS support

Other Improvements

• Full-text indexes for InnoDB
• GIS spatial types and indexes for InnoDB
• sys schema for simplified performance diagnostics
• Default utf8mb4 character set
• Default strict SQL mode
• Enhanced Performance Schema instrumentation

Connection Troubleshooting

Diagnostics

• Inspect client error messages
• Test port availability with nc -zv 192.168.1.10 3306
• Verify service status with systemctl status mysql
• Check error logs at /var/log/mysql/error.log
• Validate firewall rules

Fixes

• Adjust bind-address in my.cnf to 0.0.0.0 for remote access
• Grant correct user host permissions (e.g., 'app_user'@'192.168.1.%')
• Increase max_connections
• Use netstat -tulpn to check port conflicts

Table Corruption Resolution

Detection

• Error messages like Table './db/table' is marked as crashed
• CHECK TABLE customer_profile

Mitigation

• REPAIR TABLE customer_profile for MyISAM
• Restore from valid backups for InnoDB; use specialized tools like undrop-for-innodb if no backup exists

Accidental Data Deletion Recovery

Options

• Binlog replay: Use mysqlbinlog --start-position=12345 --stop-position=67890 mysql-bin.000123 | mysql -u root -p
• Backup restoration: Use mysqldump full backups + incremental binlog or Percona XtraBackup
• Delayed replica: Roll back a replica with CHANGE REPLICATION SOURCE TO SOURCE_DELAY=3600 before the deletion

Replication Inconsistency Solutions

Causes

• Non-deterministic functions (UUID(), NOW()) in statement-based binlog
• Direct writes to replicas
• Statement-based binlog format

Remedies

• Switch to binlog_format=ROW
• Enable read_only and super_read_only on replicas
• Use pt-table-checksum to detect discrepancies
• Use pt-table-sync to resolve issues
• Check SHOW REPLICA STATUS\G for errors

Deadlock Handling

Diagnosis

• Analyze SHOW ENGINE INNODB STATUS deadlock section

Prevention

• Keep transactions short and commit immediately
• Enforce consistent global lock ordering across all application queries
• Split large batch operations into smaller chunks

Memory Leak Detection & Fixes

Tools

• valgrind for deep memory analysis (use only on test instances)
• ps -eo pid,pmem,vsize,cmd | grep mysqld for memory usage snapshots
• Performance Schema memory_summary_global_by_event_name table

Fixes

• Avoid debug builds in production
• Schedule controlled periodic restarts
• Upgrade to a version with patched memory leaks

Too Many Connections Resolution

Causes

• Active connections exceed max_connections

Fixes

• Temporarily increase max_connections via SET GLOBAL max_connections=500
• Identify and kill idle/long-running connections with SHOW PROCESSLIST
• Implement application-level connection pooling
• Set wait_timeout and interactive_timeout to clean up idle connections
• Limit per-user connections with max_user_connections

Long Transaction Mitigation

Diagnosis

• Query information_schema.innodb_trx to find active transactions older than 60 seconds

Impacts

• Holds locks, blocks other transactions
• Prevents undo log purging, increasing disk usage

Fixes

• Refactor application logic to reduce transaction scope
• Kill problematic long transactions with KILL <thread_id>
• Split large bulk updates/deletes into batches

OOM Prevention & Recovery

Diagnosis

• Check dmesg for OOM killer messages

Causes

• Overprovisioned innodb_buffer_pool_size (should be ~50-70% of available RAM)
• Excessive concurrent connections with large sort/join buffers
• Memory-intensive queries

Fixes

• Adjust InnoDB and global buffer parameters
• Limit concurrent connections
• Use EXPLAIN ANALYZE to optimize expensive queries
• Monitor memory usage with Prometheus + Grafana or MySQL Enterprise Monitor

Disaster Recovery Planning

Key components:

• Regular, tested backups (mysqldump for logical, Percona XtraBackup for physical)
• Offsite backup storage
• Recovery drills performed quarterly
• High-availability setup (MHA, Orchestrator, InnoDB Cluster)
• Delayed replica with 1-24 hour delay
• Documented step-by-step recovery SOPs