adminDatabase Lock Transaction and Deadlock Checklist
Databases must protect data consistency. Locks and transactions help prevent conflicting writes, but they can also create waits, deadlocks and slow application behavior when many operations compete.
Core principle
A transaction is a unit of work. A lock protects data during that work. Problems happen when transactions stay open too long, touch rows in conflicting order or block other queries.
Checklist
- Identify whether the issue affects writes, checkout, imports or admin actions.
- Check active database processes.
- Look for long-running queries.
- Check for lock waits or deadlocks.
- Identify the application action creating the lock.
- Review recent imports or bulk updates.
- Check whether transactions are too large.
- Stop only clearly stuck operations when necessary.
- Retry failed operation after pressure drops.
- Document the pattern for future prevention.
Reusable lesson
This applies to ecommerce orders, WordPress imports, analytics writes, queue workers, automation logs and any system with concurrent database writes.
When to Use This Checklist
Use this checklist when database writes hang, imports freeze, admin actions stall or logs show lock waits or deadlocks.
Required Tools
Database admin access, MySQL CLI, application logs, import logs, backup, maintenance window
Before You Start
Do not kill database processes blindly. Identify the query, user, database and application action before stopping anything.
Structured Checklist Steps
- Identify affected write operation.
- Check process list.
- Find long queries.
- Check lock waits.
- Identify app action.
- Review imports.
- Review transaction size.
- Stop stuck operation if safe.
- Retry after pressure drops.
- Document pattern.
Verification Steps
- Blocking query is identified.
- Affected application action is known.
- Database becomes responsive again.
- Failed operation can be retried safely.
- Prevention note is documented.
Rollback Plan
If stopping a query breaks an operation, restore from backup if data is inconsistent and rerun the operation in a safer batch size.
Common Mistakes
- Killing random database processes.
- Running huge imports during peak traffic.
- Ignoring long transactions.
- No backup before bulk writes.
- Not reducing batch size after repeated locks.
Related Commands
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUSG
SELECT * FROM information_schema.innodb_trxG
KILL QUERY process_id;
mysqladmin -u root -p processlist