is it possible that data changes in this way?

Question

I'm creating a website that user can become resellers and sell things and that money get added to there credit.

I'm using ASP.net (C#), Linq-to-SQL and SQL Server 2008 R2 and I was wondering if a certain problem could ever happen:

My userinfo table is something like this:

ID
username
password
credit
isactive

when a user sell something, the Credit column must be added with 99% of that product price, and I do this in my code like this:

PayDBDataContext db = new PayDBDataContext(ConnectionStrings.ConnectionString);
UserInfo ui = db.UserInfos.SingleOrDefault(x => x.ID == userID);
if(ui.isactive==false)
    return;
int p= (int)(newSell.Price*0.99);
//---and maybe some other more time consuming calculations
ui.credit+=p;       
db.SubmitChanges();
db.Connection.Close();

My questions:

Is it possible that after I have extracted UI record from database and before db.submitchanges() value of credit column in database changes and my ui.credit won't be reliable? Like if that user just sells something else in that short time?

http://msdn.microsoft.com/en-us/library/vstudio/bb738618(v=vs.100).aspx — ta.speot.is, Nov 01 '13 at 07:10
@ta.speot.do you know how this behaves if you have multiple servers? Doesn't entity framework handle it at the database level or in code? — nunespascal, Nov 01 '13 at 07:13

score 2 · Accepted Answer · edited May 23 '17 at 12:13

It is totally possible, that you fetched a record and based on value of some field, you begun your calculation, and before you could save that, the record that you fetched, has changed in the database. It happens all the time or is very likely to happen.

What you need to look at is concurrency check. The most simple way of implementing a concurrency check is to keep a DateTime stamp in your database for each row i.e. modify your table to this:

ID
username
password
credit
isactive
dateModified

Now when you fetch the record, you also have dateModified field in it, so, before doing db.SubmitChanges(), you again make a call to database for the same record and see whether now returned dateModified is equal to the one you earlier retrieved, if not, there is concurrency violation, generate error message i.e.

PayDBDataContext db = new PayDBDataContext(ConnectionStrings.ConnectionString);
UserInfo ui = db.UserInfos.SingleOrDefault(x => x.ID == userID);
if(ui.isactive==false)
    return;
int p= (int)(newSell.Price*0.99);
//---and maybe some other more time consuming calculations
ui.credit+=p;       

UserInfo uiCon =db.UserInfos.SingleOrDefault(x=>x.ID==userID);
if(uiCon.dateModified != ui.dateModified)
{
    //generate validation error and ask the user to reperform calculations
     return;
}
db.SubmitChanges();
db.Connection.Close();

more on concurreny check, you can read here

just one more thing, for this to actully happens two transaction(selling of a product in this case) must be very close, i mean like two different sells happen less than a second from each other, right? — user2944052, Nov 01 '13 at 07:38
see,tht is a decision you have to make, based on the frequency of usage of your app. What I mean is, instead of saving a DateTime, you can have a DateTimeStamp, which comprises of Day, Year, Month, Hour,minute, second (and milliseconds if you will), so it will look like 011201311011354333. such a number is always going to be unique (again,based on the frequency of usage) — Manish Mishra, Nov 01 '13 at 07:42

is it possible that data changes in this way?

1 Answers1