Sunday, May 23, 2010

IF u have a client server application and 2 clients are updating the same record from a table named Employee(ID, City, Salary)


The record they are attempting to update is (1,'Cairo',100)


1st client makes the following update (1,'Alex',100) to the previous data (1,'Cairo',100) according to PK


2nd client makes the following update (1,'cairo',200) to the previous data (1,'Cairo',100) according to PK


The 1st client as its update reaches the SQLServer first the row will be updated to (1,"Alex",100)


The 2nd client come as the blind donkey and make the record be (1,"cairo",200)


Then the first update of city will be overwritten.


Then what is the SOLUTION


the solution is the smart data type "TimeStamp" one of the sql server datatypes : it is a stream of binary didgits(0's & 1's)


When u add a column of type timestamp, as row first created It will have a binary value, but, each time the row updated the column updates its value at this row by a random binary value differs from any binary value in any row


The table will be (id, city, salary, StampeClo) the "StampeClo" of type time stamp


The update statement will be :


UPDATE Student SET City=@ City,Salary=@Salary WHERE StampeCol=@StampCol AND [ID]=@ID


Returning to our problem:


1st Client Update Statement: UPDATE Student SET City='Alex',Salary=100 WHERE StampeCol='110' AND [ID]=1


//the statement as reaches first and the


//StampCol value become say '111' (the number will be auto generated I put these values only for //explanation purpose )


2nd Client Update Statement: UPDATE Student SET City='cairo',Salary=200 WHERE StampeCol='110' AND [ID]=1


//the statement as later


//and StampCol value become say '111' not '110', update statement will not be updated, here u can inform //user "data of this record has //been changed by another client load the page again if it was a webpage for //example


Note:in .NET, U will give the mapped property of that column the datatype of "byte[]"


C# fast-food by:


Eng.Waleed abou-zaid


Senior Software Developer


Harf Information Technology


MCSD.Net,MCAD.Net,MCP,A+,N+ certified

walid.abouzaid@gmail.com






0 comments: