One table - Multiple users - One mySQL query

[renzlo]

This is the scenario:

I have one table (main_tbl) with columns RECID(primary key),JOBNAME(text),STATUS(text). Now, I created an application that will query to the database and get one jobname and update the status. How can I make sure that multiple users wont get the same jobname when they executed the query at the same time?

Kindly assist and thank you in advance.

[Begginnerdev]

A preliminary question would be: How is your application handling the multiple users? Just a simple string containing the query executed in a command?

On the back end you can implement row locking, but this can lead to problems if the users do not commit their changes.

[renzlo]

Yes, just a simple string.

[Begginnerdev]

Some one please correct me if I am wrong, but I do beleive that row locking will be your only option to completely safeguard the data.

You will have to be sure to program in timeouts for when a user opens a record but gets side tracked with something else.

Something like a timer would take care of this. Pair a timer with an action listener. (Maybe a mousemove or keystroke?) Then reset the timer value to 0.

Unlock the row @ 5 minutes ect...

[samir_ibrahim]

You can use the dataset to hold your table, then dataadapter will handle your table changes and compare the data since the time you load the row from table and when you update the row to check if any other user changed during the time you load the row (that is "optimistic concurrency")

row lock is useful to prevent many user changing the same row at the same time, but you should know when to lock the row (not every select statement should lock the row) you can tell that in your program diagram.

That how it works with SQL Server, not tested with MySQL, but the idea should remain the same

[Begginnerdev]

The problem when loading the data into a dataset is that the data is cached. If one user has a copy in their dataset, and another has a copy in theirs - if the users decide to change the data one of the user's input will be erased.