> There were probably multiple common logic bugs. However one that sticks out in my memory was when the submission system would first check if the team already submitted that flag (fast check in session) and if not, it would check the flag in the database (slow), award points (slow), and finally add the flag to the session (fast). Yup, that's a race condition.
How is "insert into found_flag (team_id, flag_id, found_at) values ($1, $2, now()) on conflict do nothing" slower than this 4 step race-condition-prone operation? (To get the score, "select count(1) from found_flag where team_id=$1".) You don't even need transactions for this, as long as you can't transition from found to not found somehow ("delete from found_flag where team_id=$1 and flag_id=$2").
The only problem I see with this is where validating the correct answer is expensive; without another piece of data to show that validation has started, you can overload the checker by submitting your answer before the first validation routine succeeds. But that is also easy to track, with a timeout even, and you still don't need transactions.
How is "insert into found_flag (team_id, flag_id, found_at) values ($1, $2, now()) on conflict do nothing" slower than this 4 step race-condition-prone operation? (To get the score, "select count(1) from found_flag where team_id=$1".) You don't even need transactions for this, as long as you can't transition from found to not found somehow ("delete from found_flag where team_id=$1 and flag_id=$2").
The only problem I see with this is where validating the correct answer is expensive; without another piece of data to show that validation has started, you can overload the checker by submitting your answer before the first validation routine succeeds. But that is also easy to track, with a timeout even, and you still don't need transactions.