Solved

Simple Update (timestamp usage for concurrency)

Posted on 2004-10-03
13
288 Views
Last Modified: 2012-06-21
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..

Table :  UserInfo
COLUMNS
==============
UserID        
UserName      varchar(40)
City                varchar(40)
TimeStamp     TimeStamp


create procedure sp_update_user
    @UserID int,
    @UserName varchar(40),
    @city varchar(40),
   

as

begin

update set UserName=@UserName,city=@city  where UserID =@UserID

     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
       
        Dim mCommand As SqlClient.SqlCommand
        Dim drObject As SqlClient.SqlDataReader

        Try
            mCommand = New SqlClient.SqlCommand("sp_update_user", iOpenDb.openconnection())
            mCommand.CommandType = CommandType.StoredProcedure
            'Add Params to the Command collection

            With mCommand.Parameters
                .Add("@Return_value", SqlDbType.Int, 4)
                .add("@UserID",sqldbtype.int)
                .Add("@UserName", SqlDbType.VarChar, 40)
                .Add("@city", sqldbtype.varchar,40)
            End With

            'Sepcify the parameters values to be passed to SP
            With mCommand
                .Parameters("@UserID").Value = USerID
                .parameters("@Username").value = UserName
                .parameters("city").value = @city
            End With

            With mCommand
                .Parameters("@Return_value").Direction = ParameterDirection.ReturnValue
                .Parameters("@UserName").Direction = ParameterDirection.Input
                .Parameters("@City").Direction = ParameterDirection.intput
                .parameters("@UserID").direction = parameterdirection.input
            End With

            drObject = mCommand.ExecuteReader
            valueReturned as integer  = mCommand.Parameters("@Return_value").Value

            If valueReturned= -101 Then
                Throw New Exception("Error Occoured")
            End If

           
        Catch ex As Exception
            Err.Raise(vbObjectError + 6103, "", ex.Message)
     
        End Try


End function
0
Comment
Question by:sainavya1215
  • 8
  • 4
13 Comments
 
LVL 7

Expert Comment

by:gsiric
ID: 12215063
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.


create procedure sp_update_user
    @UserID int,
    @UserName varchar(40),
    @city varchar(40),
    @RetrievedTimeStamp DateTime
as
begin
update set UserName=@UserName,city=@city where UserID =@UserID AND TimeStamp = @RetrievedTimeStamp

if 0 rows are updated, then you must return an error from stored procedure.
0
 
LVL 3

Expert Comment

by:skpatra
ID: 12216306
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.

update set UserName=@UserName,city=@city, TimeStamp = getdate( ) where UserID =@UserID AND TimeStamp = @RetrievedTimeStamp
0
 

Author Comment

by:sainavya1215
ID: 12216839
I have couple of doubts regarding the same

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

0
 

Author Comment

by:sainavya1215
ID: 12216988
an example of point 3 mentioned earlier

for ex :   transactional  stored proc

inserts into one table gets the ID and updates another table.
so we arent retrieving any record to the client to send timestamp.

lets say stored proc has

       begin tran
            insert into table (name,place) values (@name,@place)

                   if error!=0
                        select @id = @@identity

     
           update  table2 set orderid =1,personID=@id where id=2
             
          update table 3
         etc
how will time stamp work in this scenario
0
 
LVL 7

Accepted Solution

by:
gsiric earned 500 total points
ID: 12217345
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.
0
 

Author Comment

by:sainavya1215
ID: 12219764
ok fine so timestamp need not be updated .... fine..

How about the scenario which i had posted earlier ie  transactional stored proc which inserts and updates 2 tables
how can it be handled. u can check an example given above .thx in advance
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:sainavya1215
ID: 12220681
HERE How can we check the timestamp as  we are not passing it to the stored proceudre during transaction updates
============================================================================

 

for ex :   transactional  stored proc

inserts into one table gets the ID and updates another table.
so we arent retrieving any record to the client to send timestamp  back to update statements.

lets say stored proc has

       begin tran
            insert into table (name,place) values (@name,@place)

                   if error!=0
                        select @id = @@identity

     
           update  table2 set orderid =1,personID=@id where id=2
             
          update table 3
         etc
how will time stamp work in this scenario
0
 
LVL 7

Expert Comment

by:gsiric
ID: 12221361
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.
0
 

Author Comment

by:sainavya1215
ID: 12223909
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.
0
 
LVL 7

Expert Comment

by:gsiric
ID: 12224387
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.
0
 

Author Comment

by:sainavya1215
ID: 12224644
Gsiric,
Thanks for all the info provided. it was v helpful though
0
 

Author Comment

by:sainavya1215
ID: 12225995
Gsiric,

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.
0
 

Author Comment

by:sainavya1215
ID: 12226213
hi Gsiric,

I posted a new question about the conversion prob i have .. Pls check out the following link

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/Q_21156340.html
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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)…
This video discusses moving either the default database or any database to a new volume.
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.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now