Simple Update (timestamp usage for concurrency)

Simple update function
======================================
How do i use timestamp column in this scenario keeping in mind about concurreny in multiuser application
I use a function in the dll where I pass the values to be updated as params to the function.
there is no dataset over here..

if @@error!=0
begin
return -100
end
else
begin
return -101
end
end
======================================================================
public function updateUser(byval UserID as integer,UserName as string,City as string )
Dim iOpenDb As New COpenDb

When you retrieve row from a table, you must also retrieve original TimeStamp from row.
When you are updating record, you must pass this original time stamp to stored procedure, and check that it's not changed by other user.
If TimeStamp in row isn't equal to this supplied to stored procedure, then row is changed by other user, and 0 rows will be updated by stored procedure.

Slight modification to gsiric's solution:
While updating we should update the timestamp as well: (Note the addition of TimeStamp = getdate( ) below). Otherwise the query will update successfully for all concurrent users.

1) Can An example be shown how a timestamp is retrieved when first fetching a record in a stored procedure
2) update set UserName=@UserName,city=@city, TimeStamp = getdate( ) where UserID =@UserID AND TimeStamp = @RetrievedTimeStamp
(if this condition ie the Update doesnot work does it mean that a error message has to be throw to client that Someone else has updated as what i can see is
timestamp=@retrievedTimestamp (this wouldnt work if someone else has updated with a new value in timestamp right)
3) I have a stored proc where i really dont bring any data to the client ....it runs on transaction based.
i have 2-3 select statements and check condition and if condition is met I update a history table . in this case how can i updateTimestamp

4) Do we need to take care of timestamp only during updates OR deletes and select statements as well

Hi,
1.
Timestamp need to be retrieved like any other column from table.
You somewhere need to have select statement with which you retrieve user data:

SELECT UserID, UserName , ... FROM Users

Modify this statement to get TimeStamp too:

SELECT UserID, UserName , ..., TimeStamp FROM Users

2. ... this wouldnt work if someone else has updated with a new value in timestamp right
This is actually just what you want. If someone else update row before you, row is changed and you will not update this record. So if 0 rows are updated there is concureny problem and you need to throw exception to user.

3. As I know, timestamp column is automatically updated when you perform insert or update on table row, you dont need
to update it manually.

4. You need to check for timestamp in UPDATE and DELETE. When you retrieve row, you don't need to worry abut timestamp.

Hi,
When concurency problem usually occurs ?
User retrieve data from database and after spending some time to explore this data he make changes. He now want to put changes to the database. There is posibility that someone else changed or deleted this data before them, and with concurency control, we want to show to user that this happend.

When we begin transaction, we count on 4 things that transaction must support
Atomicity
Consistency
Isolation
Durability

Isolation : Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state.

So when you start this transaction there is no fear that someone else will change row in table2 after you start transaction.
Transaction worry about concurency control.

Someone else can retrieve row from table2 with ID=2 before we commit transaction (make changes), but no one else can make modification to this row before transaction finish.

After we commit transaction, row in table2 (id=2) is changed. (timestamp is changed too)

If someone else retrieved data before we are commit transaction, he is one who must worry about concurrency, not transaction. So we are on the begining. Data is retrieved by client and client must worry about concurency.

I got u. Lets say the transaction is running and another person tries to use the same stored proc transaction would it throw an error automatically or should we need to cdoe transaction to throw an error.

Second transaction will begin after first finsih, so second transaction will have consistent state in database before it begin.
You can setup setup transaction to fail after some time if first transaction not finish, or can wait forever to first transaction finish.

In transaction you can code some extra bussines logic, and throw exception, but there is no concurency problem.
For example you can fail transaction if there exist row in table1.

We cannot compare a oldtimestamp column with the current timestamp. I tested it . this might be due to the binary digits it store. I think we have to convert the timestamp to datetime or some other datatype and convert the current time stamp to the same datatype and compare. I dont think it would work if we dont convert timestamp and compare.

IP addresses can be stored in a database in any of several ways. These ways may vary based on the volume of the data. I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.
…

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…

When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.