Database Error Fixes -- Connection, Deadlock & Constraint Violation

Database errors like connection refused, deadlocks, and constraint violations stop applications immediately -- this guide shows how to diagnose and fix them in MySQL and PostgreSQL with exact SQL commands.

What You'll Learn

Why It Matters

Database errors are among the hardest to debug because they often only appear under production load. Knowing how to inspect locks, connections, and queries in real time is essential for any backend developer.

Real-World Use

When your web app returns a 500 with "deadlock detected" or your migration script fails with "unique constraint violation", these techniques help you resolve the issue and prevent recurrence.

Common Database Errors Table

Step-by-Step Fixes

Fix 1: Database Connection Refused

# Check if MySQL is running
sudo systemctl status mysql

# Check PostgreSQL status
sudo systemctl status postgresql

# Verify MySQL port
sudo netstat -tulpn | grep 3306

# Verify PostgreSQL port
sudo netstat -tulpn | grep 5432

# Start MySQL
sudo systemctl start mysql

# Start PostgreSQL
sudo systemctl start postgresql

Expected output:

● mysql.service - MySQL Community Server
     Loaded: loaded
     Active: active (running) since Mon 2026-06-22 10:00:00 UTC

Fix 2: Deadlock Detected

-- MySQL: Check deadlock info
SHOW ENGINE INNODB STATUS\G

-- Look for the "LATEST DETECTED DEADLOCK" section
-- It shows the transactions involved and the SQL that caused the deadlock

-- Prevent deadlocks by ordering your transactions consistently
-- Transaction A: UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction B: UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- Both transactions should lock in the same order (id 1 then id 2)

Expected output:

LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s)

Fix 3: Unique Constraint Violation

-- MySQL: Use ON DUPLICATE KEY UPDATE
INSERT INTO users (email, name) VALUES ('test@example.com', 'Test')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- PostgreSQL: Use ON CONFLICT
INSERT INTO users (email, name) VALUES ('test@example.com', 'Test')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- Find duplicates before inserting
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Expected output:

Query OK, 2 rows affected (0.01 sec)  -- 1 inserted, 1 updated

Fix 4: Query Timeout

-- Find slow queries in MySQL
SHOW FULL PROCESSLIST;

-- Kill a slow query
KILL QUERY 12345;

-- Analyze query performance
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Add an index to speed it up
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Expected output:

+-----+------+-----------+------+---------+------+----------+------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info             |
+-----+------+-----------+------+---------+------+----------+------------------+
| 45  | root | localhost | shop | Query   | 120  | Sending  | SELECT * FROM... |
+-----+------+-----------+------+---------+------+----------+------------------+

Fix 5: Database Disk Full

# Check disk usage for MySQL data directory
df -h /var/lib/mysql

# Find large tables in MySQL
SELECT TABLE_NAME, ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY SIZE_MB DESC;

# Clean old binary logs
PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;

# Vacuum dead rows in PostgreSQL
VACUUM ANALYZE your_table;

Expected output:

Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        50G   48G   2G   96% /var/lib/mysql

Database Error Diagnosis Flowchart

flowchart TD
    A[Database Error] --> B{Error Type?}
    B -->|Connection Refused| C[Check systemctl status]
    C --> D[Verify port with netstat]
    D --> E[Check pg_hba.conf or bind-address]
    B -->|Deadlock| F[Run SHOW ENGINE INNODB STATUS]
    F --> G[Identify conflicting transactions]
    G --> H[Standardize lock ordering in code]
    B -->|Constraint Violation| I[Identify the constraint name]
    I --> J[Use ON DUPLICATE KEY or ON CONFLICT]
    B -->|Query Timeout| K[Run EXPLAIN ANALYZE]
    K --> L[Add indexes and optimize joins]
    B -->|Disk Full| M[Run df -h on data dir]
    M --> N[Purge old logs and optimize tables]

Prevention Tips

• Always use connection pooling to avoid exhausting database connections
• Add retry logic for deadlock errors (they are normal under high concurrency)
• Run migrations with ON CONFLICT / ON DUPLICATE KEY to handle constraint violations gracefully
• Set lock_wait_timeout in MySQL or Deadlock_timeout in PostgreSQL to fail fast
• Monitor disk usage and set up alerts when data directories exceed 80% capacity

Practice Questions

1. How do you find and kill a long-running query in MySQL? Answer: Use SHOW FULL PROCESSLIST to find the query ID, then KILL QUERY <id> to terminate it. Check the Time column to find queries running longer than expected.

2. What is a database deadlock and how do you detect one? Answer: A deadlock happens when two transactions each hold locks the other needs. In MySQL, run SHOW ENGINE INNODB STATUS and look for the "LATEST DETECTED DEADLOCK" section.

3. How do you prevent unique constraint violations when inserting data? Answer: In MySQL use INSERT ... ON DUPLICATE KEY UPDATE, in PostgreSQL use INSERT ... ON CONFLICT ... DO UPDATE SET .... This handles duplicates gracefully instead of throwing an error.

4. What does EXPLAIN ANALYZE show that EXPLAIN does not? Answer: EXPLAIN ANALYZE actually executes the query and shows real execution time, row counts at each step, and timing breakdowns. EXPLAIN only shows the query plan estimate.

5. Challenge: Write a SQL script that checks for duplicate email addresses in a users table, deletes all but the oldest entry per duplicate, and adds a unique constraint on the email column. Answer:

   DELETE FROM users
   WHERE id NOT IN (
       SELECT MIN(id) FROM users GROUP BY email
   );
   ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);
   

Quick Reference

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.