0

Is it a bad idea to try to hold a transaction open indefinitely in order to hold an advisory lock? It appears to function well enough for our use case, however in performance testing the long transaction has already raised a lot of eyebrows.

Our application has several backends in a cluster connected to a database, and could use some sort of mutex to ensure at most one of those backends are responsible for some housekeeping chores. I elected to use transaction level advisory locks for this purpose, as we don't control the environments and can't rule out external connection poolers.

Each backend will periodically try to acquire the lock. When it successfully acquires the lock it will periodically re-execute the same statement in order to ensure the lock is still held (and prevent the idle transaction from being booted.) That backend will run the housekeeping logic until the backend is shut down or there is a problem reacquiring the lock, presumably just in case of a broken connection to db.

SELECT pg_try_advisory_xact_lock($1) as locked;

**edit: the transaction is used ONLY for holding this lock. It is my understanding that this keeps it MVCC friendly.

Improve this question
2
  • @jjanes No objective findings. I was not very clear. Stated differently and without the superfluous info: Folks have expressed concern that long transaction time is something they use as an indicator of trouble, and that especially in a monitoring context where database stats like transaction duration are aggregated this strategy would suppress that signal.
    – Christian Kruse
    Mar 10 at 22:03
  • 1
    Well, it certainly can cause problems, so it is right for them to be concerned. But there is really no reason to think it will in this particular case, especially because as you note it is not holding a snapshot, which the main source of problems. Maybe there is a way to get the monitoring software to be more nuanced. I don't use have much experience in that, my favorite way to improve database monitoring software is to throw it out the window.
    – jjanes
    Mar 11 at 0:23

2 Answers 2

Reset to default
1

If your database transaction does nothing except for holding an advisory lock, it won't cause any trouble in the database, even if it takes a very long time. You can safely use that for application threads to lock out each other, but perhaps it is easier to use synchronization techniques offered by your application programming language.

Improve this answer
0

I have used a dedicated table (call ot locks or backends) to insert a row that contains the server name and timestamp.

Each backend creates a row if missing or the timestamp is too old (say by 10 minutes).

If successful, it updates it regularly (say every 2 minutes), to keep control. If it goes down, another can take over.

Once finished, it erases the row.

Before every cycle of action, the backend checks if it is still incharge.

You have to decide on suitable time periods.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.