Concurrent database access..(!!)

This sounds like a problem in your own implementation; database caches are designed to flush if the tables involved in the query are changed.

In any case, why are you passing realtime requests via a database? That's really not the right tool. You should be using an IPC method like sockets.

If you have multiple connections to the database, and the database doesn't have auto-commit enabled, it may be possible that you're adding the new database records as part of a transaction and then not calling wxDb::CommitTrans to make the changes visible to other database connections.

From the wxWidgets documentation:

Quote:

Different than non-SQL/ODBC datasources, when a program performs an insertion, deletion, or update (or other SQL functions like altering tables, etc) through ODBC, the program must issue a "commit" to the datasource to tell the datasource that the action(s) it has been told to perform are to be recorded as permanent. Until a commit is performed, any other programs that query the datasource will not see the changes that have been made (although there are databases that can be configured to auto-commit). NOTE: With most datasources, until the commit is performed, any cursor that is open on that same datasource connection will be able to see the changes that are uncommitted. Check your database's documentation/configuration to verify this before relying on it though.

Thanks for the replies.

ApochPiQ:

Quote:

In any case, why are you passing realtime requests via a database? That's really not the right tool. You should be using an IPC method like sockets.

The requests are not realtime. There is a continously running service that adds requests to a database table 24/7. These requests need to be handled manually at some point using the application in question (a kind of client), perhaps during normal working hours. Therefore I'm using a database.

Excors:

Quote:

If you have multiple connections to the database, and the database doesn't have auto-commit enabled, it may be possible that you're adding the new database records as part of a transaction and then not calling wxDb::CommitTrans to make the changes visible to other database connections.

I am calling wxDb::CommitTrans, to finish the transaction at an update or insert. I have two ways of inserting new requests to the requests table. Either I use a small tool I've written, which indeed does the wxDb::CommitTrans call. Or I just manually insert the row using the MySQL Query Browser. In either case, I can view the database table contents by using the Query Browser to verify that there indeed are requests pending. I'm also able to view and verify modification made by the application in the tables, so the commiting does work. The problem still remains though, if I add a request "externally" after connecting to the database, subsequent queries in the application returns the old number of rows.

It would be strange to suspect wxWidgets, because its database classes are surely well tested and used already. But considering that I'm committing my transactions, there seems to be something rotten somewhere..

Any more insight anyone?

Another guess: If you're using InnoDB tables, it sounds possible that the querying connection will use a snapshot of the database from the time of the first query - "If you are running with the default REPEATABLE READ isolation level, all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries." (source). If I'm interpreting it correctly, the application should see any externally committed updates only after it commits itself. But if that's not the issue, I'm afraid I have no other ideas.

Excors:
I think you are right! I was just about to post the very same observation. I do indeed use InnoDB tables and I just noticed I can get the externally added requests to show up by doing a "dummy" wxDb::CommitTrans before querying for new requests (although I have no pending transactions).

This sounds like a dangerous thing. Because I'll be running multiple instances of the program cross-accessing the database, it sounds like this "snapshot" thing could really mess things up. I'll have a look at the source you've given me and if I there's some alternative of disabling these snapshots.

Thanks alot. Rate++

That did it. Great!

For completeness, if anyone else has problems. I edited the options config file:

C:\Program Files\MySQL\MySQL Server 5.0\my.ini

Inserting on the last row, in the [mysqld] group:

transaction-isolation=READ-COMMITTED

This makes the snapshot inconsistency go away and the InnoDB tables work like a proper uptodate lock-the-row database.

Thanks again Excors.

I really, really strongly recommend not polling the database every few seconds. Set up something in the client code that adds the records to send some kind of wake-up signal over to the half that does the processing, or something similar. Polling on a database is horridly inefficient and very, very fragile.

This becomes an exponentially more important issue if you are running multiple instances of your code, as you indicated earlier.

ApochPiQ:

Quote:

I really, really strongly recommend not polling the database every few seconds. Set up something in the client code that adds the records to send some kind of wake-up signal over to the half that does the processing, or something similar. Polling on a database is horridly inefficient and very, very fragile. This becomes an exponentially more important issue if you are running multiple instances of your code, as you indicated earlier.

Thanks, I appreciate your advice. This is for a commercial application, so I'll definitely have a think about it. The applications have a "ping" setting, which might be set to check the database for a new request every 5 seconds or so. Processing of a request takes some time, so there won't be so much pinging if there are unserved requests. Once a request is served though, an immediate ping is issued after which (if there is no request) its pinged every few seconds again. In the commercial deployment, I can definitely decrease this ping time to say 30 seconds or so, but for testing it's nice to have decently quick response. The application instances are to be run on a local network, say 1-20 instances I'm imagining. Thus, as it's not for a large scale internet deployment or anything, you think the solution, although not the prettiest then, will be okay?

How are you going to handle the case where two (or maybe 10) clients all "ping" at the same time, and pull the same request set from the database?

Well, I've got a checkout system. Each application instance is logged on with a unique id. Requests are checked out in a way that shouldn't allow two instances to process the same request (now that the InnoDB settings are correct). The result sets are small, generally only one row, containing the oldest pending request_id, that is not yet checked out by anoyone. A client doesn't really access any other rows after it's got a request checked out, until it's checked in again. I can add, that the processing time for a request is perhaps ~10 seconds.

As for clients actually pinging at the same time, there shouldn't need to be so much overlap. If I have 10 clients running, all checking for an incoming request every 30 seconds. Best case, there's only one database query every 3 seconds. The ping time could be adjusted depending on the number of concurrent running instances.

Well, I guess if you're really OK with such a fragile and cumbersome situation, that's fine. I just hope it doesn't bite you viciously down the road. Just keep in mind that you need to plan for failure cases. It is not enough to jury-rig things so they "should be OK" because eventually I can guarantee they'll quit being OK [wink]


My question is, if this is not under serious load and time to fulfill requests is not an issue, then why are you running multiple instances at all? Why introduce all the extra complication, overhead, and potential points of failure?

And if you do need multiple fulfillment instances to handle load, why are you not designing your system safely?

Quote:

Well, I guess if you're really OK with such a fragile and cumbersome situation, that's fine. I just hope it doesn't bite you viciously down the road. Just keep in mind that you need to plan for failure cases. It is not enough to jury-rig things so they "should be OK" because eventually I can guarantee they'll quit being OK

Well, I don't really see why it should bite me down the road. I also don't see why it's fragile. This is not much different than say a CVS. Fair enough, what I'm doing is hardly as involved, but I don't see why there would be any problems doing it like this. The database backend should make sure that the tables are syncronized and up to date. That being said, I do not know alot about database scaling etc, but I can hardly imagine my situation being very extreme on database load.

Quote:

My question is, if this is not under serious load and time to fulfill requests is not an issue, then why are you running multiple instances at all? Why introduce all the extra complication, overhead, and potential points of failure?

Well, the load will vary with time of day. I'll give you a bit more information about the service. Customers send in data together with some customer information. Human operators at their work stations are running the application client. The application checks out a pending request, the operator performs some manual processing and checks back the resulting data. The application retrieves the next pending request, if any. The customer will also be billed at some point. Requests can arrive 24/7, but operators are likely to not work around the clock. The load, as in the number of requests, will therefore most likely be the highest when the operators get back into work in the morning. If the operators manage to process all requests, then their clients will start pinging the database at some set time interval.

Quote:

And if you do need multiple fulfillment instances to handle load, why are you not designing your system safely?

I don't see quite what's unsafe about the system. I do guess there are multiple ways of creating the service processing, but using a database as a processing queue and history record, seems like a decent way to do it IMO.

So can you specify what you would do, to perform this kind of functionality safely, while managing history and customer information? I'm not sure what you mean by IPC in your previous post, but I guess I can google it.

Quote:

Original post by Puppet [...] Quote: And if you do need multiple fulfillment instances to handle load, why are you not designing your system safely? I don't see quite what's unsafe about the system. I do guess there are multiple ways of creating the service processing, but using a database as a processing queue and history record, seems like a decent way to do it IMO. So can you specify what you would do, to perform this kind of functionality safely, while managing history and customer information? I'm not sure what you mean by IPC in your previous post, but I guess I can google it.

It sounds like you have the following:
ReSync(); //the dummy commit
GetPendingRequests();
MarkRecords(NotPending);
ProcessPendingRequests();

If two instances are running at the same time, what prevents them from getting the same pending record requests and then processing the same requests? Where is your 'mutex' around {GetPendingRequests, MarkRecords} so that two instances can never see the same records as pending and then process requests twice?

There's a few problems with your approach. Now, granted, these will not prevent you from getting it to work. They just make it easier to break. There's a reason phones ring when you get an incoming call instead of people picking them up every 10 seconds to see if anyone's there [wink]

• Instantaneous collisions are a problem - i.e. if two clients try to "check out" the same request at the same time. The way to prevent this is to run your check-out and request-retrieval process as an atomic action in the database; this way the database layer will ensure nobody ever goofs up a checkout due to timing issues. I'm not clear on whether or not your checkout method is atomic, but it should be.
  


• Your request-fetch timing is suspect. How are you going to ensure that the pings come in evenly from the various clients? What if a machine silently fails or is deliberately taken offline while the rest keep running? How will you prevent the timing from getting screwed up?
  


• How will you ensure even distribution of requests? Your method is extremely easy to trip up, and cause a situation where one client gets the vast majority of the requests, or is starved and never gets any requests at all. Suppose you have 3 clients, each checking in at 30 second intervals, staggered in 10 second increments. If requests occur on average around once every 30 seconds, one client will see virtually every request, and the other two will only see load if they're really lucky. Similar situations can lead to starvation where one of the three never sees any traffic. Plot this out on graph paper or a timeline if you're curious.
  
  Do not underestimate the seriousness of this issue. You are dealing with human operators, and I will guarantee you from very harsh experience that inequitable work loads will cause problems.
  


• Since processing is manual, you can't guarantee how long it will take on average. There are two options: keep pinging even if the operator is serving a request, or only resume pinging once requests are finished. In either case, all it takes is the operator taking longer than the average delay between pings for one request, and they'll throw off the distribution of the entire system.

Now, it would be possible to engineer a massive house of cards and try to hack up some kind of regulation mechanism that keeps load evenly balanced, keeps the ping times sane, and generally copes with the unpredictable human element. However, that would actually probably make things even worse. Every layer of "security" you add to this mechanism is actually just piling more stuff on a narrow foundation. Eventually you're going to hit a point where you've crammed too much onto too shaky of a base and it's going to blow up. Never underestimate how long your systems will remain in use or how grossly abused they will become over time.


So, there's the problems. The solution is actually remarkably simple - it's extremely widespread in systems like phone queues where equal load distribution to human operators is essential.

What I would personally do here is set up a push-oriented design rather than a pull-oriented design; i.e. the clients get told they will fulfill a request, rather than asking if there are any they can fulfill. The distinction may seem subtle but it is vital.

This involves two basic components: the request marshall, and the clients themselves. Incoming requests should all be directed into a single point - the marshall. You haven't detailed how the customers are generating requests in the system, so I'm not sure whether or not you have control over the software they use. If you do, you're in good shape: just have the software connect up to the marshall application, log the request, and then bugger off and do whatever it does.

If not, you need to find a way to collect those requests efficiently. Polling a database isn't really efficient, but it'll be acceptable if it has to be. The important thing is that you want one piece of code polling and then acting on that, not many.


Now, once you have a piece of code collecting requests, the next step is to send them out to clients. The simplest way to do this is to have each client "log in" to the marshall when it is ready to service requests. When a request comes in, the marshall uses a distribution decision process to pick a client, then sends a message to that client informing it that it should begin servicing a request, along with the request data itself, or some way to get ahold of the request data (like an ID number). The marshall then flags that client as busy, and never sends requests to a busy client.

Once the client finishes, it will send the results (if applicable) back to the marshall, or at the very least some kind of "I'm done now" message. The marshall will then flag that client as ready to service additional requests.


The only missing piece here is the distribution logic: how do you pick which client to send the next request to? This depends a lot on your needs and there are several good decision mechanisms commonly used. I would recommend coding up several decision options, and letting the system administrator reconfigure which method is used on-the-fly if needed:

• Pick the least recently busy client


• Pick the client with the least total work time


• Pick the client with the smallest percentage of time spent serving requests


• Pick the client with the fewest number of finished requests

Many of these will give similar results but the subtle variance can be important, especially when balancing among human operators. For instance, least-recently-busy is good if you need to maintain steady workflow, and smallest-working-time is good if you need to keep people feeling like everyone is pulling their weight.


The mechanism for doing all this communication is up to you; this is a general category of stuff known as Inter-process communication or IPC. (Note that there are two general uses of the IPC term: when communicating between abstract "processes" which are likely on different machines, and when communicating between operating system processes on a single local machine. Some techniques apply to one more than the other.)

The most common, flexible, and accessible mechanism for this is sockets. There is a socket implementation on every major platform in operation, and a decent socket library for virtually every applications programming environment out there.

As I said, if you really want to stick to an "easier" but more fragile system, that's your call. The alternatives may seem less easy because they require unfamiliar technology, but that technology is a good skill to have, and once you have it, this way is actually quite a bit easier in that it takes a lot less work to get a robust system working.

Extrarius:

Quote:

If two instances are running at the same time, what prevents them from getting the same pending record requests and then processing the same requests? Where is your 'mutex' around {GetPendingRequests, MarkRecords} so that two instances can never see the same records as pending and then process requests twice?

Well, I do put alot of trust in the database backend to keep things in sync I guess. My SQL statement for performing the checkout looks like (pseudoish):

execute_sql("UPDATE requests SET operator_id = 'the_operator_id' WHERE operator_id IS NULL AND request_id = the_request_id");

wxDb::CommitTrans();

The operator_id is then read back from the database and compared with the_operator_id. If they match, the application's got the request checked out. If they don't match, it's presumed another operator was quicker and commited the checkout. It works in some basic test when putting a break point before the CommitTrans() and manually setting another operator_id through the MySQL Query Browser. Resuming execution, the operator_id read back doesn't match and the request is thus not checked out. I can agree it's not that pretty. This is my first attempts at database access and my 200 page SQL beginner's quick start didn't really cover this stuff ;)

ApochPiQ:
Thanks for the long and elaborate reply. Yes, I figured you were talking about some proxy/marshall thing. You are obviously right in what you're saying. I'm also pretty convinced about what you're saying about the work load distribution system. It could be really benificial to have that level of control. At the moment there is no idea if there will be 1 or 20 operators, it all depend on the success of the service and the automation (which has been my main task in this application). Using the one marshall with exclusive database access also removes the problems of the "snapshot" issue, the reliance on database behaviours.

I'll have to have a talk with my employer. Perhaps the current system will do for early testing of the service. I do concern myself with service stability, so I want to avoid purposly doing something half-arsed. I have only a conceptual understanding of sockets and no experience, so I'll have to investigate what it'd take to implement the marshall system you're suggesting. wxWidgets seems to be a magic box of goodies, I'm sure there's something useful already available.

Thanks for your input, I'll seriously consider revising the system. Rate++