Search code examples
postgresqllockingselect-for-update

PostgreSql Insert while select for update

I'm considering using 'SELECT ... FOR UPDATE' to run some changes on several rows. For example -

Select * from my_table where parent_id = 5
Update my_table set created = null where parent_id = 5;

As I understand, it will block any request to delete/update rows where parent_id=5. My concern is what will happen when at the same time, there will be a request to create a new my_table row with parent_id=5. Will it be blocked or not?

Solution

  • Row locks are acquired at the time SELECT ... FOR UPDATE is called, so inserting new rows will not be blocked, even if they match the selection criteria for the locked rows. This can be easily confirmed by starting a transaction and performing a SELECT ... FOR UPDATE then in a second session insert a new row that matches the selection critera and perform an update on the newly inserted row. Both operations will complete. Confirm that the originally selected rows are still locked by attempting to update one of them from the second session. The operation will hang until the transaction in the first session is terminated.