Deadlocks

A deadlock is a circular dependency between transactions or processes competing for resources. In a database, transaction A might hold a lock on one row while requesting a lock on another row held by transaction B. If B is also waiting for A's row, neither transaction can finish and release its locks. Databases commonly detect this cycle and abort one participant so the others can continue.

Ordinary blocking is different. If one transaction waits for a lock held by another transaction that can still complete, there is no deadlock. A slow query, an idle transaction holding locks, or a lock-wait timeout does not by itself prove that a deadlock occurred. Diagnosis requires identifying the transactions, held locks, requested locks, and dependencies between them.

Consider two workers transferring balances between accounts 100 and 200. One worker updates account 100 and then account 200, while the other updates account 200 and then account 100. Their exclusive row locks can create a cycle. Having every worker acquire the account locks in ascending account-ID order removes this particular circular-wait pattern. The ordering must apply across all relevant application paths, including batch jobs and administrative scripts.

Locks coordinate concurrent data access; they are not authorization controls. An exclusive lock can prevent conflicting updates, but whether it also blocks reads depends on the database engine, isolation level, and read operation. Multiversion concurrency control often allows ordinary reads to use a consistent snapshot while writes proceed. It does not eliminate deadlocks among writers or operations requiring incompatible locks.

In cloud applications, concurrent ingestion jobs, application requests, and schema changes can all contend for the same database objects. Managed hosting does not remove these dependencies. For Amazon RDS and Amazon Aurora, lock granularity, detection behavior, and diagnostic tools depend on the database engine. PostgreSQL exposes lock and session information through pg_locks and pg_stat_activity; MySQL InnoDB provides deadlock details through SHOW ENGINE INNODB STATUS and additional engine-specific diagnostics.

Amazon Redshift also requires transaction coordination for concurrent warehouse operations. Overlapping write transactions and explicit table locking can introduce blocking or deadlocks. Use a consistent table-access order and inspect transaction and lock information, including SVV_TRANSACTIONS, when troubleshooting contention. A serializable-isolation violation is not necessarily a deadlock: a transaction can be rejected because its execution cannot be serialized even without a circular lock wait.

Prevention starts with keeping transactions short and acquiring locks consistently. Avoid network calls, user interaction, or lengthy computation while holding database locks. Suitable indexes can reduce the rows scanned and, depending on the engine and execution plan, the locking footprint. Splitting a large write into smaller transactions can reduce contention, but it changes atomicity: previously committed batches remain committed if a later batch fails. Explicitly locking broader resources may simplify coordination while reducing throughput.

When the database chooses a deadlock victim, application code should roll back as required by the engine and retry the complete logical transaction using a fresh transaction boundary. Use bounded retries with backoff and jitter, and ensure that external side effects are idempotent or coordinated through a transactional pattern. A deadlock victim's rollback does not undo an email or external API call already made. Lock timeouts can bound waiting, but they are not equivalent to cycle detection, and timeout rollback behavior varies by engine.

For data-store selection and exam scenarios, connect the workload's consistency requirements to its concurrency controls. Do not disable necessary locking or weaken isolation simply to hide contention. First distinguish blocking from deadlocks, identify conflicting operations, standardize resource ordering, shorten lock duration, and implement safe recovery. Increasing database capacity may shorten some transactions, but it does not eliminate an inconsistent locking order.