4

In my Java webapp, each instance is checking on startup if the database is up-to-date via a JDBC connection. If the DB is not up-to-date, it performs an update routine by executing SQL scripts.

I can't control when instances get startet. Therefore, I need to ensure that only a single instance is performing a database update at the same time. Ideally, I would need to lock the complete database, but according to

http://www.postgresql.org/docs/8.4/static/explicit-locking.html

and

http://wiki.postgresql.org/wiki/Lock_database

PostgreSQL doesn't support it (I'm still using version 8.4).

What other options do I have?

Improve this question
3
  • do you intend to perform these operations via pure JDBC or do you have framework helping with it (Hibernate, myBatis, etc.)? Commented Sep 6, 2012 at 13:34
  • 1
    It should be obvious that any user ID that can do what you suggest can launch a denial of service attack very easily, so I assume this is done through a special user ID that is not used for normal application execution? Do you need to kick off any users that are already connected when you do this? Do you have enough control over the other connections to use a solution where they would need to cooperate in grabbing an advisory lock?: postgresql.org/docs/8.4/interactive/… Commented Sep 6, 2012 at 13:43
  • My DB upgrade code is running SQL scripts via JDBC. Later on, queries are run via Hibernate. I could take care that the upgrade code respects and advisory lock, but I'm not sure if something like that can be done in Hibernate. Commented Sep 6, 2012 at 18:27

2 Answers 2

Reset to default
4

If you control the code for all the instances, then you can create a table in the database where each instance that starts, looks in this table for a record with a timestamp. Lets call it your "lock" record.

If a process finds that the lock record does not exist, then it inserts the record and processes the data you require.

If a process finds that the lock record does exist then you can assume that another process has created it and do nothing, busy wait, or what ever.

With this design you are effectively creating a "lock" in the database to synchronize your processes with. You code it, so all processes know they have to adhere to the logic of the lock record.

Once the first process that has the lock, has completed processing, it should clear the lock record so the next restart behaves correctly. You also need to think about the situation where the lock has not been cleared due to a server error, or execution erorr. Typically, if the lock is older than n minutes you can consider it to be "stale", therefore delete it, and create it again (or just update it).

When dealing with the "lock" record be sure to utilise the Serializable isolation level on your DB connection in order to guarantee atomicity.

The Service layer of your Java code can enforce with your locking strategy prior to calling your Data Access layer. It won't matter whether you use Hibernate or not, as it's just application logic.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

So this is basically the same solution as the advisory lock in the comment on my question. It is easy to handle in the upgrade code itself and it seems to be one way to move forward with this issue.
Conceptually similar, but they differ in their implementation. My solution can be used with any database server as it's just using a standard table. The advisory lock mechanism is PostGres specific. In both cases all your application code has to be aware of the lock and behave itself in order for the locking strategy to be effective.
As portability is not an issue at the moment, I used an advisory lock and it works fine. But as you said before, it is probably not too different to having a custom log table.
0

Ideally, I would need to lock the complete database.

Does it really matter what your lock applies to, as long as you're effectively serializing access? Just acquire an exclusive lock on any table, or row for that matter.

Improve this answer

1 Comment

Well, if I could lock everything, all other connection would need to wait. The upgrade only takes about 1-2 seconds.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.