Skip to Content
0

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

Feb 14, 2017 at 12:43 PM

94

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lars Breddemann
Feb 14, 2017 at 10:31 PM
1

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.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

OK, that's good to know. Thanks for clarifying!

0

No worries. Remember to close the question by accepting the answer.

0