After you added a nightly batch job that updates customer balances, the application starts failing with "deadlock detected" errors. What is going on, and how do you fix it?

The batch job and the application lock the same rows in different orders. The batch holds row A and waits for row B while an application transaction holds B and waits for A. Neither can continue, so PostgreSQL aborts one of them. The fixes are to lock rows in a consistent order, keep transactions short, and retry the transaction that gets aborted.

Why it happens without any explicit locks

Every UPDATE locks the rows it changes until the transaction ends. Say the batch updates balances in order of customer_id, in one big transaction, and the application's transfer updates the sender and then the receiver:

Batch:  UPDATE customer 10  ... later wants customer 20
App:    UPDATE customer 20 (sender) ... then wants customer 10 (receiver)

Each waits on the other. After a short wait PostgreSQL detects the cycle and cancels one. It may cancel either side, so the application sees errors at random.

Diagnose

• The server log shows the processes involved, the lock types and the queries. Turn on log_lock_waits to see long lock waits too, not only deadlocks.
• Note which tables and which statements appear. It is usually 2 code paths touching the same rows in a different order.

Fix

1. Consistent lock order. Have every transaction that updates several customers update them in ascending customer_id. In the transfer, lock both rows first:

   SELECT id FROM customers WHERE id IN (10, 20) ORDER BY id FOR UPDATE;
   

2. Short transactions in the batch. Commit every few hundred or thousand rows instead of once at the end. Fewer locks held for less time means fewer collisions, and a failure loses less work.
3. Retry on deadlock. Deadlocks can be made rare but not impossible. Catch the deadlock error (SQLSTATE 40P01) and retry the whole transaction with a short random pause. Make the transaction safe to retry.
4. Separate in time, if possible: run the batch outside peak hours, or partition its work so it does not touch rows users are changing.

What interviewers want: understanding that ordinary UPDATEs take row locks, the lock-order cycle, consistent ordering as the main fix, short transactions, and retrying as a necessary part of the design.