MySQL Locking Reads: SELECT ... FOR UPDATE and Next-Key Locks

locking read · next-key interval · data_locks

MySQL 8.4.10captured evidence

Learning guide

Question for this Lab

For the same tenant_id range, how do plain SELECT, FOR UPDATE, and isolation level change the waits for Session B's INSERT and Session C's UPDATE?

Execution engine: MySQL 8.4.10

Who runs it?

Session A reads and holds the range, Session B attempts a priority 25 INSERT, Session C updates job_id 103, and Session O observes the post-COMMIT result.

What stays controlled?

The jobs table, idx_tenant_priority_job, tenant_id = 7 range, and result rows job_id 102 and 103 stay fixed.

Executed SQL

SELECT job_id, priority, revision, note
FROM lock_jobs FORCE INDEX (idx_tenant_priority_job)
WHERE tenant_id = 7
  AND priority >= 20
  AND priority < 40
ORDER BY priority, job_id
FOR UPDATE

What changes?

Record, gap, and next-key locks held by Session A and the B → A and C → A wait edges change by variant.

What stays fixed?

The range predicate, holder result rows, B and C attempts, and completion after Session A COMMIT stay the same.

Predict before tracing

Who waits before COMMIT under REPEATABLE READ + SELECT ... FOR UPDATE?

Nobody waits because it behaves like a plain SELECT.Only Session C's UPDATE waits for Session A.Session C's row UPDATE and Session B's gap INSERT both wait for Session A.

Evidence mission

Open the wait-graph step of the RR · FOR UPDATE variant and confirm who waits for what before Session A commits.

Where do you look?: Read each wait edge in the trace corridor together with its indexName, lockMode, and lockData.
Screen to open: RR · FOR UPDATE · wait graph

What should you see?

Expected observation

This variant has two wait edges. Session C's job_id 103 UPDATE waits on the PRIMARY row lock, and Session B's priority 25 INSERT waits on the gap preceding LOCK_DATA (7, 30, 103) in idx_tenant_priority_job. Both are recorded before Session A COMMIT.

Evidence boundary

Captured: C → A and B → A wait edges from data_lock_waits with their lock attributes

Checkpoint · explain from evidenceHow do the captured wait edges differ across the three variants?Reveal answer and basis

rr-plain has no wait edge. rr-for-update has both C → A and B → A. rc-for-update retains only the C → A row wait. Waiting statements complete after Session A COMMIT.

Evidence boundary

Captured: per-variant wait edges from data_lock_waits

Core takeaway

A locking read can protect index records and preceding gaps, while the isolation level changes range protection that blocks an INSERT.

Next connection

This Lab closes the eight-Lab path from access paths through row versions to lock waits. Finish by comparing it directly with the preceding consistent-read Lab.

Review terms

record lockIntroduced in Lab 08: A lock on a specific index record that makes conflicting updates or locking reads wait.
gap lockIntroduced in Lab 08: A lock that protects the interval between index records and can conflict with an INSERT into that interval.
next-key lockIntroduced in Lab 08: An InnoDB lock form that combines an index-record lock with the gap preceding that record to protect a range.
lock waitIntroduced in Lab 08: A state where a statement pauses because its requested lock conflicts with an incompatible lock held by another transaction.
READ COMMITTEDIntroduced in Lab 07: An isolation level where each consistent read uses a fresh snapshot as of the start of that statement.

What does this query do?

It reads task rows where tenant_id = 7 and priority is at least 20 and below 40.

Foundjob_id 102, 103

Session A locks the matched rows and the index gap between them, so both the job_id 103 UPDATE and job_id 106 INSERT wait for COMMIT.

Run the same SELECT in three conditions and compare whether Session B's INSERT and Session C's UPDATE run immediately or wait for COMMIT.

Current condition: REPEATABLE READ + SELECT ... FOR UPDATE
Session A: Session A · runs SELECT
Session B: Session B · tries INSERT
Session C: Session C · tries UPDATE

Before Session A runs SELECT

The WHERE clause reads lock_jobs rows for tenant_id = 7, then keeps only rows whose priority is at least 20 and below 40.

Query range

tenant_id	priority	job_id
7	10	101
7	20	102
7	30	103
7	40	104
7	50	105

tenant_id = 7 · priority >= 20 · priority < 40

After Session A runs SELECT

Session A reads job_id 102 and 103 and keeps FOR UPDATE locks. Session C waits on the job_id 103 row lock, and Session B waits on the index gap for priority 25.

SELECT ... FOR UPDATE

job_id 102tenant_id 7 · priority 20
job_id 103tenant_id 7 · priority 30

Session C · UPDATE job_id 103

Waiting until A commits

Session B · INSERT job_id 106, priority 25

Waiting until A commits

After Session A commits

After Session A commits, the locks are released and the waiting UPDATE and INSERT complete.

UPDATED · job_id 103
INSERTED · job_id 106

tenant_id	priority	job_id	state
7	20	102	UNCHANGED
7	25	106	INSERTED
7	30	103	UPDATED

.locking-read-next-key-locks-page { overflow: auto !important; background: #f4f6f7; } .locking-read-next-key-locks-page main { height: auto !important; min-height: 100dvh; } .locklab-shell { display: none !important; } .locklab-noscript { width: min(100% - 32px, 96rem); margin: 0 auto; padding: 28px 0 72px; color: #171a1d; overflow-wrap: anywhere; } .locklab-noscript > header { padding-bottom: 24px; border-bottom: 1px solid #d8dde1; } .locklab-noscript > section, .locklab-noscript > nav { padding-block: 24px; border-bottom: 1px solid #d8dde1; } .locklab-noscript h1 { margin: 8px 0 12px; font-size: 32px; line-height: 1.25; letter-spacing: 0; } .locklab-noscript h2 { margin: 0 0 14px; font-size: 22px; letter-spacing: 0; } .locklab-noscript h3 { margin: 22px 0 10px; font-size: 18px; letter-spacing: 0; } .locklab-noscript h4 { margin: 0 0 8px; font-size: 16px; letter-spacing: 0; } .locklab-noscript p, .locklab-noscript li, .locklab-noscript dt, .locklab-noscript dd, .locklab-noscript td, .locklab-noscript th { font-size: 15px; line-height: 1.6; } .locklab-noscript a { color: #174ca7; } .locklab-noscript-eyebrow { margin: 0; color: #0d6d4d; font-weight: 800; } .locklab-noscript-nav { display: flex; flex-wrap: wrap; align-items: center; gap: 10px 20px; } .locklab-noscript-nav strong { margin-right: auto; } .locklab-noscript-code { max-width: 100%; margin: 10px 0; padding: 16px; overflow-x: auto; border: 1px solid #d8dde1; background: #fff; font: 15px/1.65 "SFMono-Regular", Consolas, monospace; white-space: pre-wrap; } .locklab-noscript-grid { display: grid; grid-template-columns: repeat(2, minmax(0, 1fr)); gap: 1px; margin: 12px 0; padding: 1px; background: #d8dde1; } .locklab-noscript-grid > div { min-width: 0; padding: 14px; background: #fff; } .locklab-noscript-scroll { max-width: 100%; overflow-x: auto; } .locklab-noscript table { width: 100%; min-width: 760px; border-collapse: collapse; background: #fff; } .locklab-noscript th, .locklab-noscript td { padding: 8px 10px; border: 1px solid #d8dde1; text-align: left; vertical-align: top; } .locklab-noscript th { background: #f2f5f6; } .locklab-noscript code { overflow-wrap: anywhere; } .locklab-noscript-variant { padding: 18px 0 30px; border-bottom: 1px solid #edf0f2; } .locklab-noscript-variant:last-child { border-bottom: 0; } .locklab-noscript-steps { display: grid; grid-template-columns: repeat(2, minmax(0, 1fr)); gap: 1px; margin: 0; padding: 1px; background: #d8dde1; list-style-position: inside; } .locklab-noscript-steps > li { min-width: 0; padding: 14px; background: #fff; } .locklab-noscript-steps p { color: #46525a; } .locklab-noscript-result { background: #edf4ff; } @media (max-width: 760px) { .locklab-noscript { width: min(100% - 24px, 96rem); } .locklab-noscript h1 { font-size: 26px; } .locklab-noscript-grid, .locklab-noscript-steps { grid-template-columns: minmax(0, 1fr); } }

Captured · MySQL 8.4.10 · arm64

MySQL Locking Reads: SELECT ... FOR UPDATE and Next-Key Locks

Trace how isolation level and FOR UPDATE change where an UPDATE and an INSERT wait, using captured MySQL 8.4.10 lock evidence.

한국어

Fixture and index definition

DROP DATABASE IF EXISTS engine_atlas_locking_read;
CREATE DATABASE engine_atlas_locking_read
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE engine_atlas_locking_read;

CREATE TABLE lock_jobs (
  job_id INT UNSIGNED NOT NULL,
  tenant_id INT UNSIGNED NOT NULL,
  priority INT UNSIGNED NOT NULL,
  revision TINYINT UNSIGNED NOT NULL,
  note VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (job_id),
  KEY idx_tenant_priority_job (tenant_id, priority, job_id)
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  STATS_PERSISTENT=1;

INSERT INTO lock_jobs VALUES
  (101, 7, 10, 1, 'seed-101'),
  (102, 7, 20, 1, 'seed-102'),
  (103, 7, 30, 1, 'seed-103'),
  (104, 7, 40, 1, 'seed-104'),
  (105, 7, 50, 1, 'seed-105'),
  (201, 8, 25, 1, 'other-201');

ANALYZE TABLE lock_jobs;

tenant_id	priority	job_id	state
7	10	101	seed
7	20	102	seed
7	25	106	attempted-insert
7	30	103	seed
7	40	104	seed
7	50	105	seed

Executed SQL

plain SELECT

SELECT job_id, priority, revision, note
FROM lock_jobs FORCE INDEX (idx_tenant_priority_job)
WHERE tenant_id = 7
  AND priority >= 20
  AND priority < 40
ORDER BY priority, job_id

SELECT ... FOR UPDATE

SELECT job_id, priority, revision, note
FROM lock_jobs FORCE INDEX (idx_tenant_priority_job)
WHERE tenant_id = 7
  AND priority >= 20
  AND priority < 40
ORDER BY priority, job_id
FOR UPDATE

Session C · UPDATE

UPDATE lock_jobs
SET revision = 2,
    note = 'updated-by-c'
WHERE job_id = 103

Session B · INSERT

INSERT INTO lock_jobs
VALUES (106, 7, 25, 1, 'inserted-by-b')

Native plans

REPEATABLE READ + SELECT

access type: range
selected key: idx_tenant_priority_job
estimated cost: 0.71
estimated rows: 1
actual time: 0.0734..0.0791 ms
actual rows: 2 · loops 1

-> Index range scan on lock_jobs using idx_tenant_priority_job over (tenant_id = 7 AND 20 <= priority < 40), with index condition: ((lock_jobs.tenant_id = 7) and (lock_jobs.priority >= 20) and (lock_jobs.priority < 40))  (cost=0.71 rows=1)

REPEATABLE READ + SELECT ... FOR UPDATE

access type: range
selected key: idx_tenant_priority_job
estimated cost: 0.71
estimated rows: 1
actual time: 0.0293..0.0333 ms
actual rows: 2 · loops 1

-> Index range scan on lock_jobs using idx_tenant_priority_job over (tenant_id = 7 AND 20 <= priority < 40), with index condition: ((lock_jobs.tenant_id = 7) and (lock_jobs.priority >= 20) and (lock_jobs.priority < 40))  (cost=0.71 rows=1)

READ COMMITTED + SELECT ... FOR UPDATE

access type: range
selected key: idx_tenant_priority_job
estimated cost: 0.71
estimated rows: 1
actual time: 0.0307..0.0366 ms
actual rows: 2 · loops 1

-> Index range scan on lock_jobs using idx_tenant_priority_job over (tenant_id = 7 AND 20 <= priority < 40), with index condition: ((lock_jobs.tenant_id = 7) and (lock_jobs.priority >= 20) and (lock_jobs.priority < 40))  (cost=0.71 rows=1)

Captured locks and waits

REPEATABLE READ + SELECT

REPEATABLE READ · 0 lock rows · 0 wait edges

session	object	index	lock mode	status	lock_data
no captured rows

requester	blocker	index	lock mode	lock_data
no captured rows

REPEATABLE READ + SELECT ... FOR UPDATE

REPEATABLE READ · 10 lock rows · 2 wait edges

session	object	index	lock mode	status	lock_data
A	lock_jobs	`idx_tenant_priority_job`	`X`	GRANTED	`7, 20, 102`
A	lock_jobs	`idx_tenant_priority_job`	`X`	GRANTED	`7, 30, 103`
A	lock_jobs	`idx_tenant_priority_job`	`X`	GRANTED	`7, 40, 104`
A	lock_jobs	`PRIMARY`	`X,REC_NOT_GAP`	GRANTED	`102`
A	lock_jobs	`PRIMARY`	`X,REC_NOT_GAP`	GRANTED	`103`
A	lock_jobs	`TABLE`	`IX`	GRANTED	`NULL`
C	lock_jobs	`PRIMARY`	`X,REC_NOT_GAP`	WAITING	`103`
C	lock_jobs	`TABLE`	`IX`	GRANTED	`NULL`
B	lock_jobs	`idx_tenant_priority_job`	`X,GAP,INSERT_INTENTION`	WAITING	`7, 30, 103`
B	lock_jobs	`TABLE`	`IX`	GRANTED	`NULL`

requester	blocker	index	lock mode	lock_data
Session C	Session A	`PRIMARY`	`X,REC_NOT_GAP`	`103`
Session B	Session A	`idx_tenant_priority_job`	`X,GAP,INSERT_INTENTION`	`7, 30, 103`

READ COMMITTED + SELECT ... FOR UPDATE

READ COMMITTED · 7 lock rows · 1 wait edges

session	object	index	lock mode	status	lock_data
A	lock_jobs	`idx_tenant_priority_job`	`X,REC_NOT_GAP`	GRANTED	`7, 20, 102`
A	lock_jobs	`idx_tenant_priority_job`	`X,REC_NOT_GAP`	GRANTED	`7, 30, 103`
A	lock_jobs	`PRIMARY`	`X,REC_NOT_GAP`	GRANTED	`102`
A	lock_jobs	`PRIMARY`	`X,REC_NOT_GAP`	GRANTED	`103`
A	lock_jobs	`TABLE`	`IX`	GRANTED	`NULL`
C	lock_jobs	`PRIMARY`	`X,REC_NOT_GAP`	WAITING	`103`
C	lock_jobs	`TABLE`	`IX`	GRANTED	`NULL`

requester	blocker	index	lock mode	lock_data
Session C	Session A	`PRIMARY`	`X,REC_NOT_GAP`	`103`

Outcomes around A COMMIT

REPEATABLE READ + SELECT

session	before A COMMIT	after A COMMIT	affected rows
Session C	completed-before-holder-commit	completed-before-holder-commit	1
Session B	completed-before-holder-commit	completed-before-holder-commit	1

REPEATABLE READ + SELECT ... FOR UPDATE

session	before A COMMIT	after A COMMIT	affected rows
Session C	waiting-on-holder	completed-after-holder-commit	1
Session B	waiting-on-holder	completed-after-holder-commit	1

READ COMMITTED + SELECT ... FOR UPDATE

session	before A COMMIT	after A COMMIT	affected rows
Session C	waiting-on-holder	completed-after-holder-commit	1
Session B	completed-before-holder-commit	completed-before-holder-commit	1

Fresh observer result

job_id	priority	revision	note
102	20	1	`seed-102`
106	25	1	`inserted-by-b`
103	30	2	`updated-by-c`

Complete explanation

REPEATABLE READ + SELECT

The consistent read blocks neither C’s UPDATE nor B’s INSERT.

01 · Schema · The exact tuple stored by the secondary index

idx_tenant_priority_job is ordered by (tenant_id, priority, job_id). Rows 101–105 belong to tenant_id 7, while job_id 201 proves that another tenant is outside this index range.
barrier.connections-ready
02 · Predicate · The [20, 40) range opened by WHERE

tenant_id = 7, priority >= 20, and priority < 40 return (7,20,102) and (7,30,103). Tuple (7,40,104) is excluded from the result and serves as the upper range boundary.
barrier.holder-read-complete
03 · EXPLAIN ANALYZE · range access with 2 actual rows

MySQL 8.4.10 selects range access on idx_tenant_priority_job. The native plan estimates rows=1 at cost=0.71, while EXPLAIN ANALYZE reports actual rows=2 and loops=1.
barrier.plans-captured
04 · Session A · Start the holder transaction

Session A begins a transaction at the selected isolation level. rr-plain runs a regular SELECT; the other variants append FOR UPDATE to the same SQL and perform a locking read.
barrier.connections-ready
05 · Session A · A reads rows 102 and 103

Session A returns job_id 102 at priority 20 and job_id 103 at priority 30. ORDER BY priority, job_id follows the secondary index tuple order.
barrier.holder-read-complete
06 · data_locks · Secondary RECORD locks and PRIMARY row locks

Under rr-for-update, Session A holds X locks on secondary tuples (7,20,102), (7,30,103), and boundary (7,40,104), plus X,REC_NOT_GAP locks on PRIMARY rows 102 and 103.
barrier.contention-captured
07 · Session C · UPDATE conflicts on PRIMARY 103

Session C executes UPDATE with WHERE job_id = 103. With a locking read, its X,REC_NOT_GAP request on PRIMARY 103 conflicts with Session A’s granted row lock and waits for COMMIT.
barrier.update-outcome-established
08 · Session B · INSERT requests the (7,25,106) position

Session B attempts to place (7,25,106) between (7,20,102) and (7,30,103). Under rr-for-update, its INSERT INTENTION conflicts with the protected gap ending at (7,30,103).
barrier.insert-outcome-established
09 · Wait evidence · Blockers confirmed by data_lock_waits

rr-for-update captures C → A on PRIMARY 103 and B → A on idx_tenant_priority_job tuple (7,30,103). rc-for-update retains only the C → A row wait.
barrier.contention-captured
10 · B-tree interval · The index interval protected by a next-key lock

The captured B wait reports LOCK_DATA=(7,30,103). In the next-key model, attempted (7,25,106) lies in the gap before that record and therefore waits for Session A’s COMMIT.
barrier.contention-captured
11 · Clustered index · Follow the secondary tuple to the PRIMARY row

The carried primary keys 102 and 103 lead from the secondary leaf to clustered PRIMARY rows. Session C updates job_id 103, so it directly conflicts with A’s PRIMARY 103 lock.
barrier.contention-captured
12 · Wait graph · See which locks make Sessions C and B wait for Session A

rr-plain has no wait edge. rr-for-update makes Sessions C and B wait for A; rc-for-update makes only Session C wait for A. Every edge retains its captured indexName, lockMode, and lockData.
barrier.contention-captured
13 · COMMIT · Waiting statements resume after A commits

Session A’s COMMIT releases its locks. The waiting UPDATE and INSERT then complete with one affected row each, and the captured run reports no deadlock.
barrier.holder-commit-closed
14 · Session O · A fresh transaction sees the final 3 rows

Session O reads job_id 102, 106, and 103 in priority order. The inserted priority 25 row sits between them, while job_id 103 now has revision 2 and note updated-by-c.
barrier.final-observer-result

REPEATABLE READ + SELECT ... FOR UPDATE

Both the row update and secondary-index gap insert wait for A’s COMMIT.

01 · Schema · The exact tuple stored by the secondary index

idx_tenant_priority_job is ordered by (tenant_id, priority, job_id). Rows 101–105 belong to tenant_id 7, while job_id 201 proves that another tenant is outside this index range.
barrier.connections-ready
02 · Predicate · The [20, 40) range opened by WHERE

tenant_id = 7, priority >= 20, and priority < 40 return (7,20,102) and (7,30,103). Tuple (7,40,104) is excluded from the result and serves as the upper range boundary.
barrier.holder-read-complete
03 · EXPLAIN ANALYZE · range access with 2 actual rows

MySQL 8.4.10 selects range access on idx_tenant_priority_job. The native plan estimates rows=1 at cost=0.71, while EXPLAIN ANALYZE reports actual rows=2 and loops=1.
barrier.plans-captured
04 · Session A · Start the holder transaction

Session A begins a transaction at the selected isolation level. rr-plain runs a regular SELECT; the other variants append FOR UPDATE to the same SQL and perform a locking read.
barrier.connections-ready
05 · Session A · A reads rows 102 and 103

Session A returns job_id 102 at priority 20 and job_id 103 at priority 30. ORDER BY priority, job_id follows the secondary index tuple order.
barrier.holder-read-complete
06 · data_locks · Secondary RECORD locks and PRIMARY row locks

Under rr-for-update, Session A holds X locks on secondary tuples (7,20,102), (7,30,103), and boundary (7,40,104), plus X,REC_NOT_GAP locks on PRIMARY rows 102 and 103.
barrier.contention-captured
07 · Session C · UPDATE conflicts on PRIMARY 103

Session C executes UPDATE with WHERE job_id = 103. With a locking read, its X,REC_NOT_GAP request on PRIMARY 103 conflicts with Session A’s granted row lock and waits for COMMIT.
barrier.update-outcome-established
08 · Session B · INSERT requests the (7,25,106) position

Session B attempts to place (7,25,106) between (7,20,102) and (7,30,103). Under rr-for-update, its INSERT INTENTION conflicts with the protected gap ending at (7,30,103).
barrier.insert-outcome-established
09 · Wait evidence · Blockers confirmed by data_lock_waits

rr-for-update captures C → A on PRIMARY 103 and B → A on idx_tenant_priority_job tuple (7,30,103). rc-for-update retains only the C → A row wait.
barrier.contention-captured
10 · B-tree interval · The index interval protected by a next-key lock

The captured B wait reports LOCK_DATA=(7,30,103). In the next-key model, attempted (7,25,106) lies in the gap before that record and therefore waits for Session A’s COMMIT.
barrier.contention-captured
11 · Clustered index · Follow the secondary tuple to the PRIMARY row

The carried primary keys 102 and 103 lead from the secondary leaf to clustered PRIMARY rows. Session C updates job_id 103, so it directly conflicts with A’s PRIMARY 103 lock.
barrier.contention-captured
12 · Wait graph · See which locks make Sessions C and B wait for Session A

rr-plain has no wait edge. rr-for-update makes Sessions C and B wait for A; rc-for-update makes only Session C wait for A. Every edge retains its captured indexName, lockMode, and lockData.
barrier.contention-captured
13 · COMMIT · Waiting statements resume after A commits

Session A’s COMMIT releases its locks. The waiting UPDATE and INSERT then complete with one affected row each, and the captured run reports no deadlock.
barrier.holder-commit-closed
14 · Session O · A fresh transaction sees the final 3 rows

Session O reads job_id 102, 106, and 103 in priority order. The inserted priority 25 row sits between them, while job_id 103 now has revision 2 and note updated-by-c.
barrier.final-observer-result

READ COMMITTED + SELECT ... FOR UPDATE

The PRIMARY row update waits, while the priority 25 insert completes first.

01 · Schema · The exact tuple stored by the secondary index

idx_tenant_priority_job is ordered by (tenant_id, priority, job_id). Rows 101–105 belong to tenant_id 7, while job_id 201 proves that another tenant is outside this index range.
barrier.connections-ready
02 · Predicate · The [20, 40) range opened by WHERE

tenant_id = 7, priority >= 20, and priority < 40 return (7,20,102) and (7,30,103). Tuple (7,40,104) is excluded from the result and serves as the upper range boundary.
barrier.holder-read-complete
03 · EXPLAIN ANALYZE · range access with 2 actual rows

MySQL 8.4.10 selects range access on idx_tenant_priority_job. The native plan estimates rows=1 at cost=0.71, while EXPLAIN ANALYZE reports actual rows=2 and loops=1.
barrier.plans-captured
04 · Session A · Start the holder transaction

Session A begins a transaction at the selected isolation level. rr-plain runs a regular SELECT; the other variants append FOR UPDATE to the same SQL and perform a locking read.
barrier.connections-ready
05 · Session A · A reads rows 102 and 103

Session A returns job_id 102 at priority 20 and job_id 103 at priority 30. ORDER BY priority, job_id follows the secondary index tuple order.
barrier.holder-read-complete
06 · data_locks · Secondary RECORD locks and PRIMARY row locks

Under rr-for-update, Session A holds X locks on secondary tuples (7,20,102), (7,30,103), and boundary (7,40,104), plus X,REC_NOT_GAP locks on PRIMARY rows 102 and 103.
barrier.contention-captured
07 · Session C · UPDATE conflicts on PRIMARY 103

Session C executes UPDATE with WHERE job_id = 103. With a locking read, its X,REC_NOT_GAP request on PRIMARY 103 conflicts with Session A’s granted row lock and waits for COMMIT.
barrier.update-outcome-established
08 · Session B · INSERT requests the (7,25,106) position

Session B attempts to place (7,25,106) between (7,20,102) and (7,30,103). Under rr-for-update, its INSERT INTENTION conflicts with the protected gap ending at (7,30,103).
barrier.insert-outcome-established
09 · Wait evidence · Blockers confirmed by data_lock_waits

rr-for-update captures C → A on PRIMARY 103 and B → A on idx_tenant_priority_job tuple (7,30,103). rc-for-update retains only the C → A row wait.
barrier.contention-captured
10 · B-tree interval · The index interval protected by a next-key lock

The captured B wait reports LOCK_DATA=(7,30,103). In the next-key model, attempted (7,25,106) lies in the gap before that record and therefore waits for Session A’s COMMIT.
barrier.contention-captured
11 · Clustered index · Follow the secondary tuple to the PRIMARY row

The carried primary keys 102 and 103 lead from the secondary leaf to clustered PRIMARY rows. Session C updates job_id 103, so it directly conflicts with A’s PRIMARY 103 lock.
barrier.contention-captured
12 · Wait graph · See which locks make Sessions C and B wait for Session A

rr-plain has no wait edge. rr-for-update makes Sessions C and B wait for A; rc-for-update makes only Session C wait for A. Every edge retains its captured indexName, lockMode, and lockData.
barrier.contention-captured
13 · COMMIT · Waiting statements resume after A commits

Session A’s COMMIT releases its locks. The waiting UPDATE and INSERT then complete with one affected row each, and the captured run reports no deadlock.
barrier.holder-commit-closed
14 · Session O · A fresh transaction sees the final 3 rows

Session O reads job_id 102, 106, and 103 in priority order. The inserted priority 25 row sits between them, while job_id 103 now has revision 2 and note updated-by-c.
barrier.final-observer-result