33

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.

julx
  • 8,694
  • 6
  • 47
  • 86

1 Answers1

42

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

Greg Aponte
  • 596
  • 5
  • 3
  • 6
    Oh, this `OF table_xyz` is great news and will help me a lot. Many thanks – julx Sep 04 '11 at 23:04
  • 1
    adding a small comment. In case you have aliases for your table, we should use the alias rather than the table name when using the OF syntax i.e `FOR UPDATE OF some_table_alias` – kelgwiin Sep 24 '21 at 05:44