Concurrent Access and Databases.

Without going into details I'm doing a project which involves a client program which makes requests to a server program that is listening for TCP requests. After the server parses the request, it spawns a service thread which obtains a lock on a data structure held in the server program. When the data is altered in that structure, then it adds or removes the corresponding data in the server via SQL queries.

From what I understand it is a safe way to modify the data on the server when multiple clients request a change, but what I wnat to know is do databases inherently have a method to handle concurrent queries to change data? Say if I got rid of the server/service thread portion which models the data in the database, and just had the clients performing queries concurrently, would the database be able to handle it?

I'm not going to actually use it this way because the data modeled in the server program is necessary for the actual implementation of the system, but I was just curious to see what the limits of a database are.

Quote:

---

From what I understand it is a safe way to modify the data on the server when multiple clients request a change, but what I wnat to know is do databases inherently have a method to handle concurrent queries to change data? Say if I got rid of the server/service thread portion which models the data in the database, and just had the clients performing queries concurrently, would the database be able to handle it?

---

It depends on the software, but generally I'd say the answer is yes.

OK I see. I only ask because each client essentially would like to read/write to one of several tables within the database, and the server program would basically spawn off a thread corresponding to the table information they request, and then perform a lock on that table class's in the server, so that any other access by a client waits for that to be free. So far I know that you can make a direct SQL request without indication of concurrency.

Your method sounds ok as long as your lock implementation is correct (atomic locking, not just a shared variable).

Any respectible database will have some level of concurrency control, these are usually decribed by isolation levels

Most database engines worth their name will support concurrent access.

The lock alone is a good method that ensures data integrity. But the real issue is to when apply it... Imagine this scenario, a client connects to read data, right after that instant another client updates that data... the first client is now, for all effects, looking at old data. What will happen if a decision is made on that data, or worst, the client updates the data based on what was displayed?

If there is a chance that scenario can occur and it is critical that information is accurate, you will want to also lock read operations (assuming you are working with live recordsets. A one time read of the data is never concurrent). But the problem here is that while the lock isn't released, no write operations can occur on records belonging to that recordset, which has the potential of slowing down the application.

An alternative is Optimistic Locking (the above is called Pessimistic Locking), which unfortunately, forces for a bit more of code on your part. There are several ways of doing it, but my common one is to not lock read operations on live recordsets and prior to any update on that recordset, I lock and check data consistency (that is, if any update was made between the time I read from the database and the time I'm ready to do an update).

Meanwhile, no locking mechanism is good without Transactions. You should apply them, particularly if you follow the Optimistic Locking approach.

I'm aware of locking strategies and for all purposes will have to use the server side locking methods because there can be no possibility of stale data read by a client, it would destroy the purpose of the system.

When the system starts up it populates itself with the information given by the database, so it's a live model of the information in the data tables. The service thread performs a search on the model to see if the client can modify it (in this instance, add itself to the dataset) and then finish. The model doesn't really need to search the database since all writes to the database will update the live model, which is what the service thread will be searching through, and then when they can add themselves to the set, the service will update the table via a SQL query.

I don't yet know what Database I will be using, but I believe they will be hosted by the university I go to.