CODEWITHSUNDEEP
postgresqltransactionslocking
masize
masize

Reputation: 109

Manually Release Postgres LOCK

I would like to know if there´s any command to unlock or release a lock made by the same transaction.

Pseudo-code

FUNCTION
    TRANSACTION
        LOOP
            TABLE LOCK
            table operations...
            "TABLE UNLOCK WANTED"
        END
    END OF TRANSACTION
END OF FUNCTION

The function query can take a while as the LOOP might be large, so I would like to be able to unlock before the transaction is fully finished.

Upvotes: 3

Views: 6083

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 325141

No, it isn't possible. Locks are held until end of transaction, no exceptions.

Thus, you need to either:

  • Use a nonstandard lock like an advisory lock, which requires everyone to check for it and respect it; or

  • Do your work in transactions that commit autonomously, before the outer transaction is done. In PostgreSQL, lack of autonomous subtransaction support means that you must use dblink for this.

Upvotes: 6

Related Questions