Managing concurrent access to an Apache Derby database

Question

Hi, I have an Apache Derby database running (in networked mode) inside a java swing application, I connect to it through direct JDBC calls via a java client application.

This is all very good and works great however I know have an additional requirement to implement concurrent licenses for this client/server application.

Ideally a user with a single user license should be able to have 1 client app running on a laptop and also a second client app running on a desktop and be able to connect to the server from both.

I don't have the luxury of a web server so I am wondering if my only option is to use the "maxthreads" runtime property in Derby and essentially force the user to have to log off the laptop say if they want to then use the app from their desktop.

If I leave the timeslice property to 0 then will a call to getConnection timeout so that I can display a message to the user explaining that they need to disconnect one of the client apps.

Are connection threads a reliable way of doing this?

Am I missing another solution?

Thanks for any input.

Phillip

Answer 1

If I understand the question correctly, you have user-locked licenses and want to prevent a single user with a single license from accessing the "server" from more than one instance of the client at a time. Your "server" is simply a database and you don't want to create an application layer on the server side.

I don't really like your initial suggestion of using the thread pool and connection timeout to enforce licensing constraints because both of those things are used for other purposes.

Instead, to solve this I might consider requiring the user to enter their license number into the GUI the first time they use it. This can be saved in the user's home directory for subsequent starts of the GUI. The same would happen if they start the GUI for the first time on another machine. Then, for each transaction the GUI makes with the database, it would first update a licenses table or similar to indicate the user's license is currently taking part in a transaction. Wherever the same license is already in the table, marked as active, or similar, the GUI would reject the transaction and display to the user their license is already in use by another client.

If you like this idea, think it through farther than I have. I can see some errors in logic locking users out permanently :-)

Answer 2

It seems like you want to make the action of "logging in" and "logging out" of the database explicit, so perhaps you may want to have a special table in the database where your application writes a record when you log in or out.

Then your application can query that table to find out if other copies of the application are currently logged in.

You might want to include additional fields in the table to track the date/time when you logged in/out, the hostname and IP address of the client which performed the login/logout, etc.