CODEWITHSUNDEEP
javapostgresqljdbclockingpostgresql-8.4
Sebi
Sebi

Reputation: 8993

How to lock PostgreSQL database via JDBC?

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?

Upvotes: 4

Views: 4499

Answers (2)

Brad
Brad

Reputation: 15879

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.

Upvotes: 4

eevar
eevar

Reputation: 3629

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.

Upvotes: 0

Related Questions