UPDATE takes a write lock on the row automatically, which prevents concurrent transactions from doing the same until the lock is released (your transaction has finished).
So this should do the trick:
WITH upd AS (
SET ct1 = true
WHERE t1.id = $t1_id -- your input here
AND ct1 = false
INSERT INTO t2(t1_id, col1)
SELECT t1_id, 'foo' -- or your input for t2 here
FROM upd; -- only if UPDATE found a row
This assumes a PK
t1.id to allow multiple rows in
t1. Your example makes it seem like there is a single row in
t1. The same solution would work for that simple case, just remove
t1.id from the query.
t1.ct1 must be defined
UPDATE finds no row (row in t1 with
t1.id = $t1_id is already
true or does not exist) then nothing happens.
If concurrent transactions wait to update the same row, they will wake up once this transaction has finished.
If your transaction commits,
true now, and the recheck for others will return no qualifying row, i.e. concurrent transactions are finished, too.
If your transaction rolls back, the next one in line gets to update
t1 (and hence also insert rows in
Note: Normally, queries in CTEs can execute in any order. But since the outer
INSERT references the
UPDATE, a sequence of operations is established.