3 # This looks at how we handle concurrent INSERTs, illustrating how the
4 # behavior differs from INSERT ... ON CONFLICT
8 CREATE TABLE target (key int primary key, val text);
19 BEGIN ISOLATION LEVEL READ COMMITTED;
21 step "merge1" { MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1'; }
22 step "delete1" { DELETE FROM target WHERE key = 1; }
23 step "insert1" { INSERT INTO target VALUES (1, 'insert1'); }
30 BEGIN ISOLATION LEVEL READ COMMITTED;
32 step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; }
34 step "merge2i" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; }
36 step "select2" { SELECT * FROM target; }
41 permutation "merge1" "c1" "select2" "c2"
42 permutation "merge1" "c1" "merge2" "select2" "c2"
44 # check concurrent inserts
45 permutation "insert1" "merge2" "c1" "select2" "c2"
46 permutation "merge1" "merge2" "c1" "select2" "c2"
47 permutation "merge1" "merge2" "a1" "select2" "c2"
49 # check how we handle when visible row has been concurrently deleted, then same key re-inserted
50 permutation "delete1" "insert1" "c1" "merge2" "select2" "c2"
51 permutation "delete1" "insert1" "merge2" "c1" "select2" "c2"
52 permutation "delete1" "insert1" "merge2i" "c1" "select2" "c2"