inflearn logo

Practical MySQL Locks and Deadlocks for Spring·JPA Developers — Reproduce and Fix Production-Only Hangs with Code

Why do requests wait when queries are fast? Locks. In a hands-on project using MySQL 8.0, Spring Boot, and JPA, reproduce record and gap locks, four types of deadlocks, long transactions and connection pool exhaustion, metadata locks during deployment, and lost updates with tests. Then fix them in code and verify the fixes with the same tests.

1 learners are taking this course

Level Intermediate

Course period Unlimited

Java
Java
SQL
SQL
MySQL
MySQL
Docker
Docker
Spring Boot
Spring Boot
Java
Java
SQL
SQL
MySQL
MySQL
Docker
Docker
Spring Boot
Spring Boot

What you will gain after the course

  • Use performance_schema.data_locks, data_lock_waits, and innodb_trx to find who holds which locks and what they’re waiting on, and interpret errors 1205 and 1213.

  • We verify through tests how indexes, isolation levels, @Transactional scope, and flush timing affect lock scope and duration.

  • Read the raw deadlock log and fix inconsistent ordering, insert-if-absent behavior, batch-versus-individual operations, and foreign key deadlocks at the structural level. Keep retries as a safety net.

  • We reproduce and prevent how external calls inside transactions can lead to connection pool exhaustion, and how metadata lock queues form during deployments.

  • We handle hot rows like first-come-first-served coupons and counters, as well as lost updates and write skew, using atomic UPDATEs, locking reads, and representative-row locks.

Recommended for
these people

Who is this course right for?

  • A Spring backend developer with 1–5 years of experience who has learned the basics of execution plans, indexes, and JPA, and wants to learn how to handle production incidents next.

  • Anyone who has encountered 1205/1213 errors or connection timeouts but couldn’t explain the cause

  • A lead developer who wants to flag transaction and lock risks in code reviews

Need to know before starting?

  • Basics of @Transactional, JPA entities, and repositories

  • Index and Execution Plan Basics

  • JDK 17 or later, Docker (or local MySQL 8.0)

Hello
This is dorian05202988

Career Verified

A data craftsman with over 20 years of experience in databases and IT. As a DBA and manager of a DB Platform team, I work with database platforms and specialize in MySQL, PostgreSQL, Oracle, MongoDB, SQL tuning, AWS, Terraform, and Python. Drawing on experience in the gaming, finance, and IT services industries, I explain database and cloud architecture. I have published 『Oracle Practical Tuning: Learning SQL Patterns』 and 『Oracle Pro*C Practical Project Guide』, and have presented on serverless architectures, database selection, and multi-database strategies at AWS Summit 2018 and 2019 and DataYa Nolja 2020. Recently, I have been working on slow-query analysis and tuning recommendations using Bedrock LLM, automated DB specification sizing, and query workflows through a DB metadata MCP server and Slack bot. I explain database automation for developers and AI applications for non-developers through hands-on exercises tailored to the audience and objectives of each course. Rather than applying AI suggestions as-is, I teach a decision-making framework that verifies result equivalence, execution plans, and observability conditions, and defers action when the supporting evidence is insufficient. Company operational data and credentials are not included in the course materials.

More

Curriculum

All

52 lectures ∙ (3hr 12min)

Course Materials:

Lecture resources
Published: 
Last updated: 

Reviews

Not enough reviews.
Please write a valuable review that helps everyone!

dorian05202988's other courses

Check out other courses by the instructor!

Similar courses

Explore other courses in the same field!

Limited time deal

$52,800.00

20%

$51.70