none
DataSet with Locking for updating. RRS feed

  • Question

  • Hi all,

    I think I have a problem when working with DB and I need your help.

    First I Fill Data from SQL Server 2005 into DataSet (DataTable). And user can modify, delete ... the records in DataSet.
    If there is another user using this application on another computer modify the same record at the same time
    => When Update method (SqlCommandBuilder) is invoked, may be an exception occurs.

    Ex : User A delete record A
    User B modify record A.

    User A update data into Db first.
    User B update data in Db later => The deleted record is not found.

    Please tell me the way to avoid this.

    Thanks a lot.


    Tuesday, January 29, 2008 8:18 AM

Answers

  • If you take a look at the stored procedures that the DataSet Designer generates when it creates a TableAdapter, you'll see how it does concurrency checking:  you'll see SQL that looks something like:

     

    UPDATE table SET foo = @foo, bar = @bar WHERE id = @id and foo = @original_foo and bar = @original_bar

     

    If this statement updates 0 rows, then one of the columns in the row has changed since it was read - that is, some other user or process has changed it.

     

    This is an unbelievable pain to maintain if you're writing your stored procedures manually.  But it always works.  (Using a timestamp to track the record version always works too - if and only if you update the timestamp in an update trigger on the table.  If this isn't done in a trigger, sooner or later someone will write a query or stored proc that updates a row without advancing its timestamp, and you'll start mysteriously losing data to overwrites.)

     

    If you want to prevent record-contention from happening at all, you have a different problem:  to keep user A from accessing a row while user B is accessing it, you have to record which user has the row in the database.  And you have to deal with all of the exception conditions that can occur:  what happens if user B decides that he doesn't want to use the row anymore?  What happens if user B just disconnects without giving the row up?  And so on.

    Tuesday, January 29, 2008 8:01 PM

All replies

  • One thing I do in a multi-user environment is append a table with the column DateLastUpdated.  Then, when I read a row from this table I know when the record was last updated.  When it then comes to update the table (row), I check to see if this has changed. 

     

    If not then I know no-one has updated it and so the user can commit the update.  Otherwise the user gets a message stating the record they tried to update has since been modified by another user at ...time....  Same applies if a record no longer exists.

     

    I write my own update Stored Procedures for this which includes the above check.

     

    Gerry

    Tuesday, January 29, 2008 6:34 PM
  • If you take a look at the stored procedures that the DataSet Designer generates when it creates a TableAdapter, you'll see how it does concurrency checking:  you'll see SQL that looks something like:

     

    UPDATE table SET foo = @foo, bar = @bar WHERE id = @id and foo = @original_foo and bar = @original_bar

     

    If this statement updates 0 rows, then one of the columns in the row has changed since it was read - that is, some other user or process has changed it.

     

    This is an unbelievable pain to maintain if you're writing your stored procedures manually.  But it always works.  (Using a timestamp to track the record version always works too - if and only if you update the timestamp in an update trigger on the table.  If this isn't done in a trigger, sooner or later someone will write a query or stored proc that updates a row without advancing its timestamp, and you'll start mysteriously losing data to overwrites.)

     

    If you want to prevent record-contention from happening at all, you have a different problem:  to keep user A from accessing a row while user B is accessing it, you have to record which user has the row in the database.  And you have to deal with all of the exception conditions that can occur:  what happens if user B decides that he doesn't want to use the row anymore?  What happens if user B just disconnects without giving the row up?  And so on.

    Tuesday, January 29, 2008 8:01 PM

  • What make me worry is I am using DataSet for processing data.
    May be some Tables, many records are modified, deleted with dis-connection mode on different computers.

    So when Update method is invoked => Exception error.

    Please give me the solution for this case.


    Thanks for your help.
    Wednesday, January 30, 2008 1:29 AM
  • There is no solution for the general problem.  The appropriate solution is going to depend on the requirements of your specific case.

     

    Wednesday, January 30, 2008 4:18 AM