I have a column in an SQL table, whose values might be updated by multiple clients at the same time.
All clients access DAL through Asp.net WebAPIs + Win Form application.
How do we ensure , only one client updates the value at a time consistently?
If it's a client side code, I would just use locks/Mutex . How do we do this in server?
I guess "static" methods are a poor option.
Should I go for any Queuing mechanism?
Please suggest what's the right method to handle this simple concurrency requirement.
My own experience is all about putting Critical sections & mutex to use to handle similar scenario's without involving DB.
I was all thinking to control just with plain client code. Just like ensuring the entry gate is atomically protected or ensuring the calls reaches sequentially there through a proper queue.
But Linq/SQL providing features out of the box, is awesome.
So you're going to edit a record. You first do a SELECT on the recordId you want, getting the data for the record and it's rowversion value. When you go to UPDATE the record, you include the recordId and the rowversion values in the WHERE clause for the update.
If the rowversion matches, the update will proceed and you'll get back a 1 for 1 record updated. If the rowversion doesn't match, you'll get back 0 for no records updated. That means the record has been updated between the time you did the SELECT and the UPDATE.
He's not being sarcastic. You are mixing two cases here:
The first case is handled through transactions which is where two records attempt to update the same record at the same time.
The second case requires more of an end user experience type of mindset and relates to the question, what happens when you have two people editing the same record but they save at different times? Consider a stock trading application. You ring trader A and tell him you want to sell a million dollars of stock. So, he gets your stock up and enters a sell order for a million dollars but then gets distracted. Now, you're watching the market and you see that selling that much stock is going to be a bad idea and you also see that you haven't sold it yet so you ring up the same trading company but get put through to trader B instead. Now, you tell trader B to just sell half a million dollars of this stock. That trader gets up the stock, sets it and processes the order and you have just sold half a million. Now trader A comes back from his very enjoyable lunch and notices that he hasn't placed the order so he presses Sell. What happens next is hugely important.
If your application treats each "transaction" atomically and just processes it, you have just sold 1.5 million dollars of stock when you only wanted to sell 1/2 a million. If your system is clever and checks the updates, it detects that another transaction has occurred after you started and prompts you. Hopefully this causes you to ring the customer and check whether they wanted to sell 1/2, 1 or 1 1/2 dollars of stock.
Mate, please don't mistake. But I'm not sure why your replies are consistently sarcastic.
They're not meant to be sarcastic, but I can see how they could come across like that.
Someone could have deleted the record you tried to update. How should the computer recover from that automagically, without resorting to assumptions about one of the two conflicting updates? How DO you update a record that no longer exists?
Depending on what you are doing, there should be *very little* chance ever for two users simultaneous working on the same recordset. A neat solution I've seen would open the forms in read-only mode when someone else was working on that record, including the time they opened it "for editing".
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
Please be very sure this is a real use case, it is often identified as a problem by management or an inexperienced developer as something that can/might happen but they have no real idea if the application requires this level of engineering.
Never underestimate the power of human stupidity
IMO, we start with an empty database and then keep on adding the tables/procedures as needed by that sprint. We do not design entire database upfront. Assuming this to be true, will code first approach (except for data loss) would be quicker for developers? Or database first approach will also be fine depending on comfort level of person working?
Both should be fine providing you establish a good model for publishing migrations in advance. Don't find yourself requiring migrations without a pre-set strategy; that's long nights and extra downtime just waiting to happen.
"Never attribute to malice that which can be explained by stupidity."
- Hanlon's Razor
Last Visit: 31-Dec-99 18:00 Last Update: 23-Jun-21 18:15