Skip to Content

Question about table locks during SELECT...FOR UPDATE query.

Hell,

I have observed that doing a SELECT...FOR UPDATE query on an empty table will actually acquire an "intention exclusive" lock on the table being queried. This lock will be held for the duration of the transaction, which will prevent the table from being dropped.

Is this expected behaviour? I would not expect any locks to be acquired unless (a) there are rows in the table, and (b) at least one of those rows is returned by my query.

Thank you!

scott

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Feb 14, 2017 at 10:31 PM

    Sure this is expected behavior. During *any* kind of query there must be a lock against the table structure to avoid changes to it while the query is running.

    The Intentional Exclusive is required, because with SELECT FOR UPDATE you indicate that you want to change data. For that no other exclusive lock on the table structure can be allowed.

    Add comment
    10|10000 characters needed characters exceeded