I'm a Computing Student and I'm working on some test code that will hopefully help in the construction of a new System for a friend of mine for some much needed cash over the summer. I'm fairly confident with most aspects of the requirements, but the one that has me stumped is safe concurrent access to a Database via JDBC.
The system shall consist of several hosts (10 at the most - at the moment) that access a server on the LAN, running version of MySQL (or some other DBMS) via JDBC/ODBC.
From my understanding, there are features in JDBC that make sure the DB is locked while it's being updated, eg: TRANSACTION_SERIALIZABLE etc. However, I'm not sure if there's a way in which I can actually update the stale data that resides in the text fields of other client applications that may have accessed the same record?
The only solution I can think of, is to create a server-side program that acts as an intermediary between the clients and the database, and which locks access to the Database just before the client program updates a record. Once locked, the the client will fetch the same record that it has stored in its local variables and checks to see if any changes have been made to the data since it was first loaded, then informs the user of any changes that have been made.
This, obviously, is going to double the network traffic of each update and is also going to require some serious comparison operations on the Data before it can ascertain wether the data is stale or not.
I was hoping that JDBC and ODBC would take care of this for me, but after some tests that involve two programs loading the same record, changing the data in both clients and updating the recordset and underlying Database one by one, have proved, so far, that JDBC does not inform me when Data has been changed.
If anyone knows where I'm going wrong, or can point me in the right direction, your help will be much appreciated.
Concurrent data access is an issue regardless of the technology you use to make the connection, there is no silver bullet! RDBMS's are good at allowing concurrent access to data, it is one of the reasons they exist, so don't rush to try to change that underlying fucntionality (i.e. why write a database application and synchronize access to it?). You need to judge the implications of concurent access - do you anticipate loads of concurrent attempts to update the same record? Normally, the amount of widely shared updatable data in a transactional database is minimal, users are normally changing only that which pertains to them. In which case you might consider optimistic locking to prevent the odd occasion when two concurrent updates happen on the same data. To mitigate against stale data visible to clients you could use some sort of background thread to poll the DB - you don't say what technology you client is written in but an AJAX request to check stake data would do this in a web application.
You can use pessimistic locking if you feel you really must prevent others updating data while someone is viewing it, but this is usually the exception, not the rule.
I am not anticipating lots of concurrent access to the same record, but I have no idea how the system is going to be used at the moment, so I cannot be absolutely sure of this just yet. I would guess, judging by the initial low number of clients, that there will be a small amount of concurrent access to a record, so optimistic locking might be the best option.
As far as the technology goes, I'll likely be using Java and the JDBC drivers. The clients will be Desktop Applications, not web Applications and the server will be located on the LAN and will likely be running MySQL.
Can you suggest ways in which 'Polling' the database can be achieved? Does Java provide the facilities to do this, or will this have to be developed.
Thanks once again.
Joined: May 07, 2010
Joined: May 07, 2010
Ok, I have a much better idea of what I need to do now. I have tried optimistic locking by implementing a 'Version' column in each table which gets checked and incremented on each update, then notifies the user that the record has been updated since they last loaded it, if the version number has been increased.
I knew the solution to this would be something simple!