Concurrency issue on "submit"

[Gregoire Detrez]

The issue appears as follow: a student gets 2 different rows in the submissions table for the same lab, with the same number and both pending review. This is pretty bad because it violates the assertion in fire/models/submitters.py line 35, which then prevents the student from even logging back in.

When looking at the faulty rows, they always seem to be created very close to each other (maybe a couple of milliseconds apart) so I suspect a concurrency issue where a student is somehow requesting the corresponding route twice. (accidental double-click on the submit button?)

Then, the two parallel execution of student_submit may be interleaved as follow:

T1: can_submit -> True
T2: can_submit -> True
T1: create submission
T2: create submission

Now the problem is that the usual fix would be either to use stricter transaction isolation for this view or to use a trigger to enforce the invariant in the database (which is kind of the same think now that I think about it...) but I don't think either solution is possible with sqlite.

None of the other solutions I can think about seems really satisfying:

• shoehorning the invariant in a table-level constraint. Like storing the csrf token in the table with a unicity constraint (feels really hackish).
• Implementing our own isolation mechanism using locks or something similar (might be difficult to get right, hard to test properly).
• Make fire handle duplicate submission gracefully (yuck...)

One obvious solution would be to drop sqlite in favor of postgresql, but that's quite a big change.

Any thoughts?