Avoide race conditions in SQLite

Question

I am concerned about race conditions in my database backed application. My application allows users to reserve a spot in a class. If all the spots are taken, then they are placed on a wait list. In greatly simplified pseudo code, I'm doing something like:

1  spots = SELECT total_spots FROM classes WHERE class_id = 200;
2  regs  = SELECT COUNT(*) FROM registrations WHERE class_id = 200;
3  wl    = spots > regs
4  INSERT INTO registrations ( user_id, class_id, on_waitlist ) VALUES ( 500, 200, wl ); 

What happens if total_spots is changed in the classes table, or another user does an INSERT on the registrations table just after we read it (row 2).

Is it adequate to wrap the entire thing in a transaction? I've read a little about SELECT FOR UPDATE in MySQL. I'm currently using SQLite, but I can migrate to MySQL if necessary.

Answer 1

SQLite's transactions are fully atomic.

If you wrap the entire thing in a transaction, other connections to the same database cannot make any changes at the same time.