CODEWITHSUNDEEP
sqldatabasepostgresqllockingrelational-database
julx
julx

Reputation: 9091

SQL semantics for "FOR UPDATE" & "JOIN"

I'd like to understand the exact semantics of using "FOR UPDATE" in connection with "JOIN". Does it just lock all the rows that were effectively used to build the end result? Does it do something else?

From this topic:

http://postgresql.1045698.n5.nabble.com/Select-For-Update-and-Left-Outer-Join-td4363154.html

I understand there are some important differences between database implementations. However I'm not sure. I'm interested in behaviour of any popular RDBMS out there, however PostgreSQL in particular.

Upvotes: 42

Views: 11674

Answers (1)

Greg Aponte
Greg Aponte

Reputation: 676

You've got it correct. In a "SELECT FOR UPDATE" with a JOIN, any rows that contribute to the returned rows will be locked. You can change this behavior by adding an "OF table_a" to the "FOR UPDATE" so that only the rows from table_a will be locked. You can read more about this in the Postgres docs here:

http://www.postgresql.org/docs/9.0/static/sql-select.html#SQL-FOR-UPDATE-SHARE

Upvotes: 50

Related Questions