I am trying to understand Postgresql Row-Level Locks could someone help me understand when to use each type of lock.
FOR UPDATE; FOR NO KEY UPDATE; FOR SHARE; FOR KEY SHARE;
I tried to go over the documentation but still, I couldn't understand the difference and didn't know when to use what and how does it make a difference.
I am expecting someone could help me understand the use case of each lock and give me clarity on when to choose each.
lock a row in FOR UPDATE
mode if you plan to delete it or to update a column that belongs to a primary key or unique constraint
lock a row in FOR NO KEY UPDATE
mode if you plan to update only columns that do not belong to a primary key or unique constraint
lock a row in FOR SHARE
mode if you don't want anybody else to modify it (a very rare requirement)
lock a row in FOR KEY SHARE
mode if you want to keep anybody else from deleting the row or updating a column that belongs to a primary key or unique constraint (also a very rare requirement)
It is difficult to come up with a use case that requires you to use FOR SHARE
and FOR KEY SHARE
explicitly; they are taken be the database internally to make sure that foreign key constraints are not violated.