|
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.
|
|
|
|
|
It looks like you used a "spinner" to generate that "question".
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
I'm an old systems programmer. Now forced to do some fancy DB projects. So trying to figure out, how things work.
|
|
|
|
|
You hit a multitude of topics; related and unrelated.
Without more focus, you made it near impossible to give you a "straight" answer.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
lol I see it now, when I read my question again. 
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.
|
|
|
|
|
You could always use rowversion[^] in the table.
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.
|
|
|
|
|
Thanks you very much. Will try this out.
But does the transaction in Linq automatically takes care of this issue? dbc.SubmitChanges() will do this automatically?
|
|
|
|
|
Not necessarily. Transactions have nothing to do with concurrency.
EF has a Timestamp attribute you can stick on a byte[8] field in your database class for this.
modified 12-Oct-18 8:13am.
|
|
|
|
|
|
What you're talking about is not handled by Transactions. Transactions make sure a group of operations on the database are handled as a single unit. They makes no claims to concurrency.
|
|
|
|
|
Correct. It's just related, but it needs us to do what you recommended in your first reply. Thats the right answer 
It's explained here[^]
modified 12-Oct-18 9:12am.
|
|
|
|
|
Simple; you don't use Access on the server. There's a button in your IDE that upgrades your Access-database to SQL Server.
Wrap your statements in a transaction, and the operation should become atomic.
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.
|
|
|
|
|
Eddy Vluggen wrote: Simple; you don't use Access on the server. There's a button in your IDE that upgrades your Access-database to SQL Server.
:=) made me smile.
Eddy Vluggen wrote: Wrap your statements in a transaction, and the operation should become atomic.
This way: ?
.net - How to create a LINQ to SQL Transaction? - Stack Overflow[^]
|
|
|
|
|
|
R1911 wrote: Though 'Transaction' takes care of things, it still needs us to know how to handle the exceptions.
You show the error to the user.
Something like "this app lets two people change the same text, and then can only save one of them".
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.
|
|
|
|
|
Eddy Vluggen wrote: Something like "this app lets two people change the same text, and then can only save one of them".
Mate, please don't mistake. But I'm not sure why your replies are consistently sarcastic. Do you think I can apply this solution?
|
|
|
|
|
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.
That's what Eddy was getting at.
This space for rent
|
|
|
|
|
Thanks, sorry may be I read him wrong. thanks for the neat explanation.
|
|
|
|
|
R1911 wrote: 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.
|
|
|
|
|
Got it. thanks! And excuse the misread.
|
|
|
|
|
You're welcome, and no problem; I know I'm not good with humans 
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
RAH
|
|
|
|
|
Sure. thanks. I'll try to share a pic of what I originally thought.
|
|
|
|
|
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?
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[ ^]
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
