One early return froze production: a lesson in database transactions

On Sunday 17 August 2026, around 15:00 Singapore time, the portal started failing. Almost every request died with the same error:

PDOException: SQLSTATE[HY000]: General error: 1205
Lock wait timeout exceeded; try restarting transaction

The database was not slow. The database was not full. One worker process was holding locks on about 516,000 rows and would not let go. The cause was a single return; statement in JodJobService::repostJob().

This post explains the incident from first principles. It is written for engineers who use DB::beginTransaction() every week but have never watched it destroy a production system. Read it slowly. Every section builds on the one before it.

note

This is a study note for the team. The code fix is tracked in PORTAL_V2_BACKEND_GLOBAL#2675 — DB::beginTransaction left open in JodJobService::repostJob. Line numbers in this post are from the code as of 17 August 2026.

Part 1 — What a transaction is

A transaction is a group of database changes that MySQL holds in a waiting state.

• You open it with BEGIN.
• You close it with COMMIT (keep all the changes) or ROLLBACK (throw all of them away).
• Between open and close, the changes exist only inside your connection. Other connections cannot see them.

When you run a single statement with no transaction, MySQL wraps it for you. It commits the moment the statement finishes. This mode is called autocommit. Almost every query our app runs uses autocommit without us thinking about it.

Part 2 — A lock lives exactly as long as its transaction

While a transaction is open, every row it changed is locked. A lock is MySQL's way to stop two connections from changing the same row at the same time.

There are two kinds. The names are exact SQL terms:

• An exclusive lock (shown as X in MySQL tools) means: only my transaction may touch this row.
• A shared lock (shown as S) means: anyone may read this row, but nobody may change it until I finish.

Here is the sentence this whole post turns on:

A lock is released when the transaction closes. Not when the statement finishes. Not when the function returns. When the transaction closes.

In autocommit, "when the transaction closes" means "when the statement finishes". Locks live for milliseconds. Nobody notices them.

In an open transaction, locks live until someone calls COMMIT or ROLLBACK. If nobody ever calls them, the locks live until the connection dies.

Part 3 — A queue worker is one connection that lives for days

A web request is short-lived:

• The request starts. Laravel opens a fresh MySQL connection.
• The request ends after a second. The connection closes.
• If the code forgot to close a transaction, MySQL rolls it back at disconnect. The mistake heals itself.

A queue worker is different:

• It is one PHP process that runs for days.
• It holds one MySQL connection the whole time.
• It processes job after job on that same connection.

We checked the framework code (vendor/laravel/framework/src/Illuminate/Queue/Worker.php, Laravel 8). Between jobs, the worker does not check whether a transaction was left open. Nothing resets the connection.

So on a worker, a forgotten transaction does not heal. It sits there. Every later job on that worker runs inside it, without knowing.

Part 4 — How Laravel counts transactions

Laravel keeps a counter per connection. The counter decides what each call really sends to MySQL:

Counter before	You call	What MySQL receives	Counter after
0	DB::beginTransaction()	BEGIN (a real one)	1
1 or more	DB::beginTransaction()	SAVEPOINT (a bookmark)	2
2	DB::commit()	nothing real	1
1	DB::commit()	COMMIT (a real one)	0

Read the third row again. When the counter is at 2, DB::commit() saves nothing. It only moves the counter down. Your data is safe only when the counter reaches 0.

Now put Part 3 and Part 4 together. Imagine a function on a queue worker that opens a transaction and returns without closing it:

• The counter is stuck at 1.
• The next job calls DB::beginTransaction(). That is now a bookmark, not a real BEGIN.
• That job calls DB::commit(). The counter goes 2 → 1. Nothing is saved.
• Every write from every later job goes into one giant transaction that nobody will ever commit.
• Every row those writes touch stays locked. Forever.

This is exactly what happened to us.

Part 5 — The bug: one return statement

Here is the shape of JodJobService::repostJob() (app/Services/JodJobService.php):

DB::beginTransaction();               // line 1594 — counter: 0 → 1

if ($earliestSlotStartDate && $latestSlotEndDate) {
    // ... build the new job post ...
} else {
    return;                           // line 1643 — counter still 1
}

// ...
DB::commit();                         // line 1725 — the normal path is fine
} catch (\Exception $e) {
    DB::rollback();                   // line 1728 — errors are fine too
}

The function protects itself against exceptions. It does not protect itself against its own early return. That path fires when the job it wants to repost has no usable future slots. For example: every remaining slot starts within 30 minutes, or was already cancelled one by one before.

One line. That is the whole root cause.

Part 6 — The incident, minute by minute

Now we walk through the real Sunday, following one real person: user 6605. All times are Singapore time.

14:30. User 6605 did not show up for a morning shift. The auto-suspension command suspended them and dispatched a CancelFutureJob queue job.

14:30 – 14:52. A worker picked the job up. JodJobService::cancelFutureJobs() collected the jobs where user 6605 was still selected. Then it looped:

foreach ($allJobsToRepost as $actualJob) {
    $this->ukgService->removeGigWorkerScheduleComment($actualJob);  // up to 2 UKG HTTP calls
    $this->repostJob($actualJob, $userId);
}

A word about UKG, because it confused us at first. The UKG API did not cause this incident. Before the bug fires, those HTTP calls run between transactions, in autocommit. They hold no locks. What the UKG calls did was stretch time. Two slow HTTP calls per job, across a loop of many jobs, is why the worker spent 22 minutes before reaching the bad one.

14:52:23. One job in the loop had no future slots to repost. repostJob() hit return; at line 1643. The counter froze at 1. MySQL's own records later showed this exact second as the birth time of the transaction we killed.

14:52 – 15:12. The worker kept going. It had no idea anything was wrong.

• More loop iterations ran. More UKG calls — now inside the open transaction, adding idle minutes while locks were held.
• Then JobUserRepositoryEloquent::cancelAllJobUserWithActiveJobByUserId() ran its big UPDATE. Its whereDoesntHave filter becomes a NOT EXISTS subquery that reads the slot_user table. MySQL puts a shared lock on every row an UPDATE's subquery reads. That is about 516,000 rows — most of the table. In autocommit those locks would die with the statement, in seconds. Inside the open transaction, they became permanent.
• cancelFutureJobs() also calls dispatch() several times. Each dispatch() is an INSERT INTO jobs — the queue table. Those inserted rows stayed exclusively locked, on jobs_queue_index, the index every dispatch in the whole application must write to.

That last bullet is why everyone felt it. Every web request that dispatched anything — a payment event, an SMS, a notification — queued up behind those locked index rows. Each one waited 50 seconds (innodb_lock_wait_timeout), then died with error 1205. The suspension of one worker froze the queue table for the whole company.

15:12 – 15:15. The job "finished". The framework tried to delete its row from the jobs table. But DatabaseQueue::deleteReserved() wraps that delete in a transaction — which was now just a bookmark. The delete was never really committed. That is why the job row was still in the table hours later, with attempts: 3.

15:15. We killed the connection with CALL mysql.rds_kill(...). MySQL rolled everything back — which also un-deleted the CancelFutureJob row. A second worker grabbed it within one second. Attempt 2 reached line 1643 much faster, because attempt 1 had already removed the comments in UKG. UKG is an external system; HTTP calls do not roll back. Within one minute the new transaction already held 517,000 locks. Same bug, new connection.

15:20. We stopped the queue workers. Their connections closed. MySQL rolled everything back. The application recovered immediately. Killing the transaction treated a symptom. Stopping the workers removed the process that kept re-creating it.

How we found the blocker

One query names the guilty connection directly. Keep it in your notes:

SELECT * FROM sys.innodb_lock_waits;

It shows, for every waiting query: which connection blocks it, how long the blocking transaction has lived (blocking_trx_started), how many rows it has locked (blocking_trx_rows_locked), and the exact kill command. Our blocker showed blocking_query: NULL — the connection was idle inside an open transaction. An idle blocker holding half a million locks is the clearest sign of this whole class of bug.

Part 7 — Every transaction site in the file, checked

After finding one bad exit path, we read every DB::beginTransaction() in JodJobService to the end:

Function	Begin at line	Verdict
repostJob	1594	The root cause. Early return; at line 1643 with no rollback. Runs on queue workers, where the connection never closes.
cancelSingleSlot	1768	Same bug, second copy. Early return at line 1779 with no rollback. Web path, so the connection closes at request end and MySQL cleans up. But every later write in that same request is silently thrown away.
cancelPostJob	1924	No bad exit of its own. But it calls repostJob at line 1950 inside its transaction. If repostJob leaves the counter raised there, cancelPostJob's own DB::commit() saves nothing.
applicantApplyJobService	1429	Commits and rolls back correctly. Separate concern: it makes a Google geocoder HTTP call and a dispatch() inside the transaction. Slow work inside a transaction is the same risk in miniature.
repostSingleSlot	2094	Commits and rolls back correctly.
Six DB::transaction(closure) calls	405 – 1184	Safe by construction. The closure form always commits or rolls back for you.

The lesson

Three sentences. Memorise them.

1. DB::beginTransaction() is a promise: every path out of the function ends in commit or rollback — including every return, not only exceptions.
2. Prefer the closure form, DB::transaction(function () { ... }), because it keeps that promise for you and cannot be exited around.
3. Nothing slow — no HTTP call, no sleep(), no external API — belongs between BEGIN and COMMIT, because a transaction's cost is not the work; it is the time it stays open.

When you review a pull request and see DB::beginTransaction(), do not read the happy path. Read the exits. Count them. Each one must close the transaction. If counting them is hard, that is the signal to use the closure form instead.