Andres Freund wrote: > No, I was thinking about an update without triggers present. > > T0: CREATE TABLE tbl(id serial pk, name text unique, data text); > T1: BEGIN; -- read committed > T1: UPDATE tbl SET name = 'foo' WHERE name = 'blarg'; /* key update of row id > = 1 */ > T2: BEGIN; -- read committed > T2: UPDATE tbl SET name = 'blarg', data = 'blarg' WHERE id = 1; /* no key > update, waiting */ > T1: COMMIT; > T2: /* UPDATE follows to updated row, due to the changed name its a key > update now */ > > Does that make sense?
So I guess your question is "is T2 now holding a TupleLockExclusive lock?" To answer it, I turned your example into a isolationtester spec: setup { CREATE TABLE tbl(id serial primary key, name text unique, data text); INSERT INTO tbl VALUES (1, 'blarg', 'no data'); } teardown { DROP TABLE tbl; } session "s1" step "s1b" { BEGIN; } step "s1u" { UPDATE tbl SET name = 'foo' WHERE name = 'blarg'; } step "s1c" { COMMIT; } session "s2" step "s2b" { BEGIN; } step "s2u" { UPDATE tbl SET name = 'blarg', data = 'blarg' WHERE id = 1; } step "s2c" { COMMIT; } session "s3" step "s3l" { SELECT * FROM tbl FOR KEY SHARE; } permutation "s1b" "s1u" "s2b" "s2u" "s1c" "s3l" "s2c" And the results: Parsed test spec with 3 sessions starting permutation: s1b s1u s2b s2u s1c s3l s2c step s1b: BEGIN; step s1u: UPDATE tbl SET name = 'foo' WHERE name = 'blarg'; step s2b: BEGIN; step s2u: UPDATE tbl SET name = 'blarg', data = 'blarg' WHERE id = 1; <waiting ...> step s1c: COMMIT; step s2u: <... completed> step s3l: SELECT * FROM tbl FOR KEY SHARE; <waiting ...> step s2c: COMMIT; step s3l: <... completed> id name data 1 blarg blarg So session 3 is correctly waiting for session 2 to finish before being ablt to grab its FOR KEY SHARE lock, indicating that session 2 is holding a FOR UPDATE lock. Good. If I change session 1 to update the data column instead of name, session 3 no longer needs to wait for session 2, meaning session 2 now only grabs a FOR NO KEY UPDATE lock. Also good. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers