Two Transfers, One Deadlock | MySQL | InnoDB | Indexes Explained

Two bank transfers hit MySQL at the same moment. Alice sends Bob a hundred, Bob sends Alice fifty. One waits, the other gets "Deadlock found", and not a single rupee goes missing. Why? This interview-prep deep dive goes under the hood of MySQL and InnoDB: the buffer pool, redo and undo logs, ACID, isolation levels and MVCC, row locks, gap and next-key locks, deadlocks, B+tree indexes, EXPLAIN, normalization, replication, partitioning, sharding, connection pooling from Node.js with mysql2, and the slow query log. It ends with a rapid-fire round. ⏱️ Chapters 0:00 Two transfers, one deadlock 0:41 InnoDB: buffer pool, redo log, undo log 1:32 ACID with a bank transfer 2:30 Isolation levels and MVCC 3:43 Locks and deadlocks (error 1213) 5:05 Halftime recap 5:29 Indexes: B+tree, clustered, secondary, composite, covering 6:58 Reading EXPLAIN 7:37 Normalization and deliberate denormalization 8:10 Replication, partitioning and sharding 9:04 MySQL from Node.js: mysql2 pools and transactions 10:06 The slow query log 10:40 Rapid-fire interview questions 11:34 Wrap-up 📌 In this video • What InnoDB gives you: transactions, row-level locking, foreign keys, crash recovery, and why the buffer pool is the first tuning knob • ACID on a real transfer, and the durability dial: innodb_flush_log_at_trx_commit = 1 (default) vs 2 or 0 • Read uncommitted, read committed, repeatable read (InnoDB default) and serializable: dirty reads, non-repeatable reads, phantoms • MVCC: undo-log versions and read views, and why locking reads take next-key locks • Shared vs exclusive locks, gap and next-key locks, deadlock detection, victim rollback, lock order, retries, SHOW ENGINE INNODB STATUS • B+tree pages, clustered vs secondary indexes, short increasing primary keys vs random UUIDs, the leftmost prefix rule, covering indexes • EXPLAIN type from const to ALL, key, rows, Using filesort, Using temporary, Using index, and EXPLAIN ANALYZE • 1NF, 2NF, 3NF, binlog replication with GTIDs, partitioning by month, sharding with Vitess • mysql2 pools, connectionLimit, prepared statements with execute, transactions with release in finally, max_connections (default 151) • Slow query log, long_query_time (default 10 s), mysqldumpslow and pt-query-digest 💻 Try it tonight SHOW ENGINE INNODB STATUS; -- see the LATEST DETECTED DEADLOCK section SELECT @@transaction_isolation; -- REPEATABLE-READ by default SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; EXPLAIN SELECT id, status FROM orders WHERE customer_id = 42 AND status = 'PAID'; 📚 Sources • InnoDB architecture: https://dev.mysql.com/doc/refman/8.0/... • InnoDB transaction isolation levels: https://dev.mysql.com/doc/refman/8.0/... • InnoDB multi-versioning: https://dev.mysql.com/doc/refman/8.0/... • InnoDB locking and deadlocks: https://dev.mysql.com/doc/refman/8.0/... • Clustered and secondary indexes: https://dev.mysql.com/doc/refman/8.0/... • EXPLAIN output format: https://dev.mysql.com/doc/refman/8.0/... • Replication with GTIDs: https://dev.mysql.com/doc/refman/8.0/... • The slow query log: https://dev.mysql.com/doc/refman/8.0/... • mysql2 for Node.js: https://sidorares.github.io/node-mysq... 🎬 Credits • Music: "Dark Fog", "Lightless Dawn" and "Ether Vox" by Kevin MacLeod (incompetech.com), licensed under CC BY 4.0: http://creativecommons.org/licenses/b... • Narration: Kokoro TTS 🔔 If this made InnoDB click before your interview, subscribe. Watch it with "The SQL Query That Fails Interviews" for the full SQL + MySQL prep. #MySQL #InnoDB #Database #InterviewPrep #NodeJS TAGS mysql interview questions, innodb explained, acid properties, transaction isolation levels, mvcc, mysql deadlock, innodb locking, gap lock next key lock, b+tree index, clustered index, secondary index, composite index leftmost prefix, covering index, mysql explain, normalization 1nf 2nf 3nf, mysql replication, partitioning vs sharding, mysql2 node, connection pool, slow query log, database interview prep, backend interview