Search code examples
sqlpostgresqllockingrowlockingmvcc

PostgreSql Row-Level Locks

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.

Solution

    • 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.