BUG: `PUT /sequences/:id/clips` uses `client.query('BEGIN')` without error rollback on the inner INSERT loop #71

New issue

Closed

opened 2026-05-25 05:37:33 -04:00 by zgaetano · 0 comments

zgaetano commented

2026-05-25 05:37:33 -04:00

Owner

Bug

PUT /sequences/:id/clips in services/mam-api/src/routes/sequences.js:190-215 correctly uses BEGIN/COMMIT/ROLLBACK for the transaction. However, if the for-loop's INSERT for clip N+1 fails (e.g., FK violation on asset_id), the catch block executes ROLLBACK — but next(e) is called AFTER rollback. The next(e) sends a 500 to the client, which is correct.

BUT: the sequence_check query at line 178 happens outside the transaction. Between that check and the BEGIN, another request could delete the sequence. The INSERT would then fail with a FK violation.

Impact

Low probability race condition
Would produce a 500 error for a legitimate operation if the sequence was deleted between the check and the INSERT

Location

services/mam-api/src/routes/sequences.js:178-215

Fix

Move the existence check inside the transaction, or use SELECT ... FOR UPDATE on the sequence row at the start.

## Bug `PUT /sequences/:id/clips` in `services/mam-api/src/routes/sequences.js:190-215` correctly uses BEGIN/COMMIT/ROLLBACK for the transaction. However, if the for-loop's `INSERT` for clip N+1 fails (e.g., FK violation on asset_id), the catch block executes `ROLLBACK` — **but `next(e)` is called AFTER rollback**. The `next(e)` sends a 500 to the client, which is correct. BUT: the sequence_check query at line 178 happens *outside* the transaction. Between that check and the BEGIN, another request could delete the sequence. The INSERT would then fail with a FK violation. ## Impact - Low probability race condition - Would produce a 500 error for a legitimate operation if the sequence was deleted between the check and the INSERT ## Location `services/mam-api/src/routes/sequences.js:178-215` ## Fix Move the existence check inside the transaction, or use `SELECT ... FOR UPDATE` on the sequence row at the start.