Database Services for High-Scale E-Commerce Platforms

Pinpoints root blocking PIDs holding exclusive locks on cart and inventory rows during high-concurrency checkout waves.

-- Non-blocking detection of root blocking queries on orders and inventory
SELECT blocked_locks.pid     AS blocked_pid,
       blocked_activity.usename  AS blocked_user,
       blocking_locks.pid    AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS blocking_statement,
       blocked_activity.wait_event_type,
       blocked_activity.wait_event
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Isolates slow checkout writes, DDL metadata lock blockers, and uncommitted order transactions causing connection backlog.

-- 1. Identify threads executing > 2s or stuck in metadata lock waits
SELECT id, user, host, db, command, time, state,
       LEFT(info, 120) AS query_sample
FROM information_schema.processlist
WHERE (command != 'Sleep' AND time > 2)
   OR state LIKE '%metadata lock%'
ORDER BY time DESC LIMIT 15;

-- 2. Inspect InnoDB transactions holding locks on checkout tables
SELECT trx_id, trx_state, trx_started, trx_query,
       trx_rows_locked, trx_rows_modified
FROM information_schema.innodb_trx
WHERE trx_state = 'LOCK WAIT' OR trx_rows_locked > 100
ORDER BY trx_started ASC;