LiveCode Forums.

I am working on a CATI application (Computer Assisted Telephone Interviewing). Currently, this is my setup:

1. There is an application that functions as the records server. It listens on a particular port for record requestss.
All of the records are currently held in an array in memory.

2. There is a client application which exists at the interviewers' stations.

Here is the process:
-client computer requests a number to the server
-server goes through an algorithm to find an appropriate record and sends it to the client (and prevents others from getting it until returned)
-once the call is complete the interviewer returns the record with the results
-server saves the updated information and requeues the record as appropriate

Problems:
-If there are periods of inactivity, sometimes we cannot connect to the server unless I close and restart it.
-The project sizes are growing, so I really need to start using a database to store records instead of just using the arrays.

Idea 1:
-Store records in a MySQL database.
-Keep just the most relevant information in arrays in the server app to make the appropriate decisions as to which record to use next.
-Server tells client the record ID# and then the client retrieves the record and later updates it.

I like idea 1, but it doesn't address my connectivity issues with the server app.

Idea 2:
-Store records in a MySQL database.
-Ditch the server app.
-Put the algorithm for selecting the next appropriate record into the client.
-Client decides which record to use, pulls it down and returns it to the DB.

My issue with idea 2, is how do I guarantee that 2 clients do not end up with the same record? Is there a way to immediately lock a record once it is queried?

My issue with idea 2, is how do I guarantee that 2 clients do not end up with the same record? Is there a way to immediately lock a record once it is queried?

Typically the server writes a simple text file that contains the ID of an open transaction. When another request comes in, the server checks the file to see if the ID is in use, if so it sends back a "wait until later" or similar notification, otherwise it sends the data. When the ID is no longer in use, the ID is removed from the text file.

If the client gets the "wait" notification, it should try again at regular intervals for a while to see if the ID has been freed.

Years and years ago in a galaxy far away, I worked with a database system that provided record level locking functionality in its api. So many of this type of issue would be solved in SQL provided the same functionality. I think you can get down to table level locking in mySQL (and maybe other implementations) but that's about it.
Pete

Check the settings of the network card, and make sure no power-saver options are letting it go to sleep to save energy.

Keep the server app. Have a database with the records table including an "in use" column. When a client requests a number, make a query from the server app to the database where inuse is false. Then having chosen a record, make an update query to set the inuse column value to true before giving out that number to the client. When the next client request comes in, the number in use won't be one of those available. Keeping the server app means that requests can be made in a linear way and each client transaction is completed before the next query. That will avoid the possibility of two clients making a query in the same few milliseconds and mark the same record in use. Slim chance of that, but concurrent database edits are a headache generally.
When the client submits the results, the update query can set the data values and at the same time, set the inuse column to false.