CODEWITHSUNDEEP
databasepostgresqlsql-updatelocking
gudge
gudge

Reputation: 1083

Update taking a row lock

I have the following query in postgres.

CREATE SCHEMA s;
CREATE TABLE s.t1 (
    "id1" Bigint,
    "id2" Bigint,
    "id3" Boolean DEFAULT false NOT NULL,
    CONSTRAINT "pk1" PRIMARY KEY (id1)
)    
WITH(OIDS=FALSE);

INSERT INTO s.t1 (id1, id2, id3) VALUES (1, 22, true);
INSERT INTO s.t1 (id1, id2) VALUES (2, 22);
INSERT INTO s.t1 (id1, id2) VALUES (3, 33);

SELECT EXISTS (SELECT 1 FROM s.t1 WHERE id2 = 22  and id3 = true FOR UPDATE); /* Does it take a lock on the row. */

SELECT id3 FROM s.t1 WHERE id2 = 22  and id3 = true FOR UPDATE; /* Takes a lock on the row. */

DROP SCHEMA s CASCADE;

I understand that the second query takes a lock (when run under a transaction) on the particular row. No other query will be able to access the row till the transaction commits.

Does the first query also takes a lock on the row when run under a transaction?

Thanks

Upvotes: 1

Views: 84

Answers (1)

klin
klin

Reputation: 121834

SELECT EXISTS (SELECT 1 FROM s.t1 WHERE id2 = 22  and id3 = true FOR UPDATE);
/* Does it take a lock on the row. */

Yes. All rows retrieved by the query are locked. It does not matter in what form and whether at all they are returned by the query.

If a subquery locks some rows the outer query may reduce the number of locked rows (if it reduces number of retrieved rows). In this case the outer query reduces the number of locked rows to one row.

Upvotes: 1

Related Questions