How can I handle concurrent changes in a web application?

Question

Here are two potential workflows that I would like to perform in a web application.

Option 1

• user sends request
• server reads data
• server changes data
• the server saves the changed data

Option 2:

• user sends request
• server reads data
• the server sends data to the user
• user sends request with changes
• the server saves the changed data

In each of these cases, I wonder: what are the standard approaches to ensuring that simultaneous access to this service yields reasonable results? (i.e. no one is edited, reset, the values ​​correspond to some ordering of changes, etc.)

The situation is hypothetical, but here are some details of where I will probably have to deal with this in practice:

• web application but no language specified
• potentially using a web framework
• Data Warehouse is a relational SQL database.
• the logic involved is too complex to express the request well, for example. value = value + 1

I think I would rather not try to reinvent the wheel here. Of course, these are well-known problems with well-known solutions. Please inform.

Thank.

Answer 1

As far as I know, a general solution to the problem does not exist.

The root of the problem is that the user can retrieve data and look at it on the screen for a long time before doing the update and saving.

I know three main approaches:

• When the user reads the database, lock the record and do not release it until the user has saved any updates. In practice, this is wildly impractical. What if the user opens the screen and then dines without saving? Or is he going home for a day? Or is he so upset, trying to update this stupid record, that he leaves and never comes back?

• Express your updates as delta, not recipients. To take a classic example, suppose you have a system that registers inventory in inventory. Each time you sell, you must subtract 1 (or more) from the inventory account.

So say that the quantity is currently 10. User A creates a sale. Current quantity = 10. User B creates a sale. He also gets the current quantity = 10. User A enters two blocks sold. New quantity = 10 - 2 = 8. Save. User B is included in one sold unit. The new number = 10 (the value that it downloaded) - 1 = 9. Save. Obviously, something went wrong.

Solution: instead of writing "update inventory quantity = 9, where itemid = 12345", write "update inventory quantity = quantity-1, where itemid = 12345". Then give the database a queue of updates. This is very different from strategy No. 1, since the database only needs to record a record long enough to read it, make an update and write it. He does not have to wait while someone is looking at the screen.

Of course, this can only be used for changes that can be expressed as delta. If you, say, update a customer’s phone number, it will not work. (For example, the old number is 555-1234. User A says to change it to 555-1235. This change is +1. User B says to change it to 555-1243. This change is + 9. So the total change is +10. the new client number is 555-1244. :-)) But in such cases, "the last user who clicked on the win key" is probably the best thing to do.

• When upgrading, make sure that the appropriate fields in the database match your "from" value. For example, say you work for a law firm that contracts for your clients. You have a screen where the user can enter notes on the negotiations. User A creates a contract entry. User B creates the same contract entry. User A says that he was just talking to the other party over the phone, and they agree with the proposed conditions. User B, who also tried to call the other party, states that they do not answer the phone and suspects that they are obstructed. User A clicks the Save button. We want user comments B to rewrite user A? Probably no. Instead, we display a message stating that the notes were changed after he read the record and let him see the new value before deciding whether to continue saving, interrupt, or enter something else.

[Note: the forum will automatically renumber my numbered lists. I'm not sure how to override this.]

Answer 2

At the application level, things are simple - each request is served by a different thread (or process), so if you do not have states in your processing classes (services), everything is safe.

Everything becomes more complicated when you reach the database, that is, where the state is. There you need a transaction so that everything is in order.

Transactions have a set of properties - ACID , that "database transaction transactions are processed reliably."

Answer 3

If you do not have transactions in mysql, you can use the update command to make sure that the data is not corrupted.

UPDATE tableA SET status=2 WHERE status = 1 

If the state is equal to one, then only one process receives the result, as a result of which the record was updated. In the code below, returns -1 if the update is NOT completed (if there were no rows to update).

 PreparedStatement query; query = connection.prepareStatement(s); int rows = -1; try { rows = query.executeUpdate(); query.close(); } catch (Exception e) { e.printStackTrace(); } return rows;