Updating tables by simultaneous users

[John Astralidis]

Hello.

I encounter the same situation. I have a GUI client-program that performs CRUD operations on a MySQL database using JDBC driver.

What I want to "fix" is this senario...

The client-program presents the DB data in a scrollable JTable.
Two different clients running on different PCs can edit simultanuesly the same record. The 1st client deletes the record while the 2nd client updates it.
No error or SQL exception happens here, and the record is deleted.

What I have to do for this? What a real bussiness software does in these cases as to avoid it and inform the user?

[Dave Tolls]

The general technique is a LastModified column, or a version stamp.
When an update is sent for a row, the current value in the db is compared with the value from the client and rejected if they're not the same.
So any UPDATE or DELETE has a clause checking that.

Of course, in the case of a DELETE occurring before an UPDATE then there's nothing you can do apart from inform the client that the record no longer exists.

[Paul Clapham]

In many cases the approach taken is to ignore the possibility. After all, in most businesses you aren't likely to have two different users responsible for the same business entity (whatever a row in your table means) and so the problem is unlikely to occur.

[John Astralidis]

Dave Tolls wrote:When an update is sent for a row, the current value in the db is compared with the value from the client and rejected if they're not the same.
So any UPDATE or DELETE has a clause checking that.

I can't really understand what do you mean with that. Could you please be more descriptive or state an example?

Paul Clapham wrote:In many cases the approach taken is to ignore the possibility. After all, in most businesses you aren't likely to have two different users responsible for the same business entity (whatever a row in your table means) and so the problem is unlikely to occur.

Hmmm, I've seen some CRM and warehouse-management software that different users have the same scope and priviledges on the same database tables and records. So, this is exactly my case and I'm trying to find a programmatic solution. What your suggestions would be?

[Ron McLeod]

John Astralidis wrote:
What I want to "fix" is this senario...

The client-program presents the DB data in a scrollable JTable.
Two different clients running on different PCs can edit simultanuesly the same record. The 1st client deletes the record while the 2nd client updates it.
No error or SQL exception happens here, and the record is deleted.

Is this any different than buying a book through Amazon and before you conplete your transaction another shopper has purchased the last one in-stock? Or booking a hotel room on-line and the last room gets reserved before you are done. The backend is able to detect these types of use-cases/scenarios, and inform the shopper that the action that they were trying to perform could not be completed - book no longer in stock, requested room type not longer available.

[John Astralidis]

Ron McLeod wrote:Is this any different than buying a book through Amazon and before you conplete your transaction another shopper has purchased the last one in-stock? Or booking a hotel room on-line and the last room gets reserved before you are done. The backend is able to detect these types of use-cases/scenarios, and inform the shopper that the action that they were trying to perform could not be completed - book no longer in stock, requested room type not longer available.

Yes, you are right. That's exactly the same. I thought that the DBMS (in my case MySQL) should handle it using security locks, etc. But, so far I haven't seen such a thing even though I have implemented all the necessary steps in my code.

So, "backend" is the software code (algorithms) that the developer should write to handle it?

[Paul Clapham]

There may be facilities in the database which help you to do whatever it you end up deciding to do. For example MySQL can select rows and put a lock on them such that other users can only read those rows. This sort of thing sounds promising but you have to be careful using it. For example what happens when the first user locks a set of records and then goes for lunch (or on holiday) before deciding what do do with those records? Other users are left fuming.

[John Astralidis]

Paul Clapham wrote:There may be facilities in the database which help you to do whatever it you end up deciding to do. For example MySQL can select rows and put a lock on them such that other users can only read those rows. This sort of thing sounds promising but you have to be careful using it. For example what happens when the first user locks a set of records and then goes for lunch (or on holiday) before deciding what do do with those records? Other users are left fuming.

I understand what you mean, but I think that the solution relies on the "backend" software code, as Ron McLeod suggested.

I just want to inform the user that the record he/she tried to update does no longer exist.

Also (and the last scope), I want (somehow) to synchronize the record updates between the connected clients. Namely, let's say that a user updates the price of a product to 100$. I want the updated price to show up immediately to all the other connected clients. Something like Viber instant-messaging app (www.viber.com) does, when a new message comes it shows up immediately on all your connected devices (pc, mobile, etc).

The last one solves also the previous scenario when a client tries to update a deleted record, as when a client deletes a record I would like to synchronize it with all the others. So, the deleted record disappears (displaying first a notification pop-up) from all the connected clients.

How could that be possible to implement it?

[Dave Tolls]

John Astralidis wrote:
I just want to inform the user that the record he/she tried to update does no longer exist.

But you know the record no longer exists, as the update count will be zero.

My suggestion above was for multiple updates occurring from different clients. As has been suggested, though, it's not all that common to be concerned about it.

If you want both sets of updates to occur, and ensure the second does not overwrite the changes of the first, then you need to stamp your rows (see below).
You can lock the rows, but that means you need to manage these locks, and ensure you release them. After all you would need to lock the row during the whole editing process, not just during the db transaction.

Make sure this is actually going to be a problem, though.

=======
To do what I suggest as an option, you need to add a column (say called version) which is a simple integer.
When you do an update, that column is incremented by 1.
In addition you need to confirm that the current version your client thinks it is updating is the current version in the database:

UPDATE <some table>
SET version = version + 1
etc etc
WHERE version = <version I am working on>
and so on.

That's the basic concept.
If someone has got in with an earlier UPDATE then the version numbers won't match, so no UPDATE.

[John Astralidis]

I came down to this as my possible solution to all these scenarios.

I want to "synchronize" (not an actual synchronization, but smth like an ack) the DB changes between the connected clients.

So, what I think to implement is this...

When a connected client performs a CRUD operation to the DB then it sents an ack packet to a server? or web-service? with the details of that operation (e.g. production description, id). Then, the server? or web-service? sents another packet to all the other connected clients informing them to refresh there GUI (by executing a SELECT * from myDB_table) as to get the new results and popping up a dialog with the details of that change to the DB (e.g. Product1 with id 1 price changed to 100$, Product2 with id2 added/deleted to/from the DB).

I think this is the "simplest" (perhaps) solution I could figure out.

Are my thoughts correct? What do you think? Could you give me some instructions on how to implement it?
Do I need a socket tcp-server or a web-service?
Please notice that my application will also be transfered to android devices too and will be scaled to a global network not only in an intranet...

[John Astralidis]

--- update ---

Where I should look into?
TCP-sockets, WebSockets or web-services?

[John Astralidis]

I'm sorry for being persistent with that matter, but I'm kind of confused with such different technologies and APIs.

Could you please give me your opinion on my thoughts about "synchronizing" the clients (told in the previous post) or any advice using better programming skills?

Lastly, from what I've read it's better to use webservices, since websockets are mostly used in web-apps between browsers and webservers communication over TCP protocol stack.