When a Change Keeps Waiting

In one line

The lock wait, the SQL execution, and the number of retries are different budgets. Receiving a timeout does not by itself lead you to conclude that no business change happened, or that running it again is safe.

Why this was needed

You try to undo the cancellation of two festival orders, but the screen keeps spinning. The first order has been fixed, but another owner is holding the second order. If you blindly send a new request at this point, the number of waiting executions grows. Pressing the button quickly does not solve the problem; it only lengthens the line of those waiting for the same resource. To explain to the customer when it is fine to press again, you have to know where you are waiting, for how long, and what has already been committed.

The previous lab put the approved version into the UPDATE condition so as not to overwrite another change. But a condition being safe and a response being fast are different properties. A condition that does not match also cannot be evaluated until the lock is released. You have to set a limit so that you do not wait forever, and after a failure check whether the same connection is in a state where it can be handed to the next request. If a connection whose settings you changed goes back to the pool and then cuts off other users' requests in a short time, that is an incident too.

How it works

PostgreSQL's lock_timeout limits the wait of each individual attempt to acquire a lock. statement_timeout limits the time the server spends processing a statement. If one batch has several UPDATEs, each has its own statement and its own lock acquisition, so neither setting equals an upper bound on the elapsed time of the whole batch. The time budget of the whole API also includes the connection, the multiple SQL statements, the waits between retries, and sending the response.

This lab first observes the DB wait and the execution of a small batch separately. Set the lock limit at 50–500ms and the statement limit larger than that and at most 2000ms. If the statement limit is shorter than the lock limit, the whole SQL is canceled first, and it becomes hard to tell what the lock-only limit did. These numbers are a contract of the learning environment, not a recommended value to use as is in production.

You apply the settings inside the transaction by passing true as the third argument of set_config. After the function succeeds or rolls back on an error, the original settings of the borrowed connection must remain. If you use a session-scoped setting, the limit can remain even after a successful transaction. A single test that it rolled back on failure misses that leak. That is why you check both the setting values and whether a transaction is open, on both the success and failure paths.

If a timeout occurs at the second row while undoing two rows one after another, the first row must also be rolled back. What undoes the first UPDATE that already ran is not the name of a Python exception but the outer DB transaction. Propagate the exception so that you exit the context where the error occurred normally, and check that the connection is in the IDLE state. If you retry from the next SQL while leaving the failed transaction open, the errors can only pile up.

What it looks like in the field

This time you retry only on the lock acquisition failure SQLSTATE 55P03, and only in a limited way. The statement cancellation 57014, an approval content conflict, an input error, and a connection error of unknown cause are passed to the caller. You do not compare error messages as Korean or English strings; you use the driver's error classification. The classification alone does not guarantee retry safety in every situation. This example uses together a contract in which the compensation request ID and the approval contents are fixed and the transaction of a failed attempt is rolled back.

The attempts of retry_undo is the total number of attempts including the first call. If it is 3, that is the first one time and two retries. The wait function receives the failed attempt numbers 1 and 2 and is not called after the last failure. In a service you can put a bounded delay and jitter into this wait function, but this lesson implements only the count limit. We do not call it an implementation of an overall elapsed-time deadline or of a limit on the number of concurrent requests across all servers.

Even after the lock is released, there is no guarantee that the approved values are the same. If the owner who made you wait changed a value and committed, the next attempt must stop with a Conflict. If you replace the expected version with the current value to make the retry succeed, the original approval protection disappears. That is why the next action must differ between a request that failed because of waiting and a request that was rejected because its approval contents were stale.

What you will do in the next check

In the quiz, you distinguish the lock, statement, and attempt budgets, and a settings leak. In the capstone lab of the next module, you have another connection hold the second row to observe a real 55P03, and also trigger 57014 with a delay trigger. You check with SQL whether the same connection was cleaned up, whether the first row did not remain, and whether a retry with the same compensation ID after the lock was released took effect only once.

References: PostgreSQL client connection defaults, Error codes, psycopg transaction management. Checked on 2026-09-13; the actual execution environment is PostgreSQL 16 and psycopg 3.2.3.