How to only update if nothing has changed? I need to validate several things before I insert

Question

Say I have two tables: runners and waypoint_checkins. I want to add waypoint checkin entries but I can never add a waypoint if the runner has finished the race, (see below for schema). Finally. A runner can never check into a waypoint if the waypoint is listed as closed.

table: runners
id | name | is_finished
---+------+------------
15 | abe  | false

table: waypoints
id  | currently_open
----+---------------
1   | true
2   | true
3   | false
... | ...

table: waypoint_checkins
runner_id | waypoint_id | time
----------+-------------+-------
15        | 1           | 5:00
15        | 2           | 5:23
15        | 4           | 5:52

So on a checkin I'm given (runner_name, waypoint_id, time). First I need to find the runner and make sure they are still running:

SELECT id, is_finished FROM runners WHERE name = %name;

Then I need to check if the runner is at an open waypoint;

SELECT currently_open FROM waypoints WHERE id = %waypoint_id;

Finally I need to insert a new waypoint checkin:

INSERT INTO waypoint_checkins VALUES (%runner_id, %wp_id, %time);

But between query 1 and 3 the runner may have finished. How can I do the insert statement where I can guarantee that the earlier select is still valid?

Answer 1

Instead of issuing those checks as separate SELECT statements, make them part of the INSERT statement. You will need to replace the INSERT...VALUES syntax with the INSERT...SELECT one to be able to use a WHERE clause:

INSERT INTO
  waypoint_checkins (runner_id, waypoint_id, time)
SELECT
  runners.id,
  %waypoint_id,
  %time
FROM
  runners
WHERE
  name = %runner_name
  AND is_finished = 'false'
  AND EXISTS (
    SELECT
      *
    FROM
      waypoints
    WHERE id = %waypoint_id
      AND currently_open = 'true'
  )
;

You can see that the first check is directly integrated into the INSERT statement, because the argument applied to runners is a name and you need to get an ID from it as one of the values to be inserted. The second check is in the form of an EXISTS predicate.

All three initial arguments (runner_name, waypoint_id, and time) are thus parameters of this single statement.