Concurrency violation adding records Why?

Discussion in 'ASP .Net Building Controls' started by Rob Dob, Nov 16, 2007.

  1. Rob Dob

    Rob Dob Guest

    Hi,
    I'm getting periodic concurrency violations while adding new records, can
    someone please tell me how to resolve this. I have an application that is
    running multi-user, approx 60+ users and for some reason my application is
    throwing a concurrency violation when adding a new record, I have a
    bindingsource that is bound to several fields, I then issue a EndEdit()
    followed by an update. 95% of the time it works and the other 5% I get a
    concurrency error.. This is very frustrating. the dataset being updated
    contains an identity field which is an int, I set this identity field using
    the datasets autoincrement feature and autostep -1. My insert statement
    does NOT contain the identity field.



    Thanks,
    Rob Dob, Nov 16, 2007
    #1
    1. Advertising

  2. Rob Dob

    Mike C# Guest

    If your clustered index column is going down by 1 (increment set to negative
    one) then you do realize that SQL Server has to reorganize the entire table
    after every single insert? If this is the case it might be at least
    contributing to/exacerbating your issue.

    "Rob Dob" <> wrote in message
    news:%...
    > Hi,
    > I'm getting periodic concurrency violations while adding new records, can
    > someone please tell me how to resolve this. I have an application that
    > is running multi-user, approx 60+ users and for some reason my application
    > is throwing a concurrency violation when adding a new record, I have a
    > bindingsource that is bound to several fields, I then issue a EndEdit()
    > followed by an update. 95% of the time it works and the other 5% I get a
    > concurrency error.. This is very frustrating. the dataset being updated
    > contains an identity field which is an int, I set this identity field
    > using the datasets autoincrement feature and autostep -1. My insert
    > statement does NOT contain the identity field.
    >
    >
    >
    > Thanks,
    >
    >
    Mike C#, Nov 16, 2007
    #2
    1. Advertising

  3. Rob Dob

    Rob Dob Guest

    the identity column never makes it to the sql, its only their to satisfy my
    datatset. when the update method of my tableadapter is called it has no
    mention of the identity column.

    Do you have any other ideas? thank you for your help.


    "Mike C#" <> wrote in messa ge
    news:%...
    > If your clustered index column is going down by 1 (increment set to
    > negative one) then you do realize that SQL Server has to reorganize the
    > entire table after every single insert? If this is the case it might be
    > at least contributing to/exacerbating your issue.
    >
    > "Rob Dob" <> wrote in message
    > news:%...
    >> Hi,
    >> I'm getting periodic concurrency violations while adding new records, can
    >> someone please tell me how to resolve this. I have an application that
    >> is running multi-user, approx 60+ users and for some reason my
    >> application is throwing a concurrency violation when adding a new record,
    >> I have a bindingsource that is bound to several fields, I then issue a
    >> EndEdit() followed by an update. 95% of the time it works and the other
    >> 5% I get a concurrency error.. This is very frustrating. the dataset
    >> being updated contains an identity field which is an int, I set this
    >> identity field using the datasets autoincrement feature and autostep -1.
    >> My insert statement does NOT contain the identity field.
    >>
    >>
    >>
    >> Thanks,
    >>
    >>

    >
    >
    Rob Dob, Nov 16, 2007
    #3
  4. Rob Dob

    Jim Rand Guest

    I'm going to make several assumptions.

    1) You use identity keys in the database.
    2) You have figured out how to get the keys back to replace negative keys in
    your dataset that existed prior to update.
    3) You are using timestamps for concurrency (that's a big assumption).
    4) You process the dataset updates in chunks using the
    dataset.GetChanges(Deleted | Modified | Added)
    5) You process the deletes first up the hierarchy. Then, you process the
    adds down the parent chain. Finally, you process the modifies.

    Let's say the user modified the parent row and added new child rows.

    Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets the
    added rows as you would expect. It also includes any modified parents. If
    you do a DataAdapter.Update(dsAdded.table), it will insert the new child
    rows AND will also update the parent row if it had been modified. Fine so
    far. Here is what happens next. You do the
    DataAdapter.Update(dsModified.table). Bingo - concurrency violation because
    the timestamp in the parent row has already changed.

    Sample SQL:

    INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
    [LastUpdatedBy]) VALUES (@AccessFlag, @Description, @LastUpdatedBy);SELECT
    AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
    WHERE (AccessFlagLookupID = SCOPE_IDENTITY())

    UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
    [Description] = @Description, [LastUpdated] = getutcdate(), [LastUpdatedBy]
    = @LastUpdatedBy WHERE (([AccessFlagLookupID] =
    @Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @Original_TS));SELECT
    CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
    (AccessFlagLookupID = @AccessFlagLookupID)

    That "(CAST(TS AS INT) = @Original_TS));" works with the first update with
    the added rows (modified parent) but fails when it gets updated again when
    the modified rows get processed.

    The way I found this was through getting unexpected concurrency errors.
    Stepping through the code and monitoring changes in the database timestamp
    values spotted the problem.

    Nasty.
    Jim Rand, Nov 17, 2007
    #4
  5. Rob Dob

    Mike C# Guest

    Do you have more information concerning usage patterns? I.e., is there more
    than one person attempting to update/add rows simultaneously? Or is this
    all happening with only a single user? Also, what is your PK on the
    Customers table? What happens if you wrap the INSERT INTO...SELECT in a
    BEGIN TRANSACTION...COMMIT TRANSACTION?

    "Rob Dob" <> wrote in message
    news:...
    Hi,

    -1- I have winform that contains some textboxes, comboboxes etc these are
    databound to a BindingSource
    -2- if the user decides to add a new record I call BindingSource.AddNew()
    -2- The BindingSource is bound to my dataset.
    -4- when the user clicks on the Save Record button my function looks like
    the following:

    try
    {
    this.CustomerBindingSource.EndEdit();
    this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy =
    "XXX";
    this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
    = DateTime.Now;
    int ncount =
    this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
    }
    catch (DBConcurrencyException oEx )
    {
    MessageBox.Show("Concurrency Erro: " + oEx.Message);
    }
    catch (System.Exception ex)
    {
    MessageBox.Show("Other Error: " + ex.Message);
    }

    -5- The Dataset that is bound to the BindingSource has an identity field
    which is an int and is also the primary key on the table, The insert
    command that is generated by the vs2005 designer does NOT contain the identy
    field but does a select after the insert inorder to repopulate the dataset
    with the new id as send below:
    INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@Date,
    @ContactLastName);
    SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID =
    SCOPE_IDENTITY())

    -6- I am getting some concurrency violations with updates but I am also
    getting the odd concurrency violation with Adding new records is this
    possible? also it appeard that for some reason today the update command was
    also not updating some records down to the database, however then the app
    was restarted and for some reason it started to work..
    Any help would be greatly appreciated.
    Thanks,


    "Jim Rand" <> wrote in message
    news:...
    > I'm going to make several assumptions.
    >
    > 1) You use identity keys in the database.
    > 2) You have figured out how to get the keys back to replace negative keys
    > in
    > your dataset that existed prior to update.
    > 3) You are using timestamps for concurrency (that's a big assumption).
    > 4) You process the dataset updates in chunks using the
    > dataset.GetChanges(Deleted | Modified | Added)
    > 5) You process the deletes first up the hierarchy. Then, you process the
    > adds down the parent chain. Finally, you process the modifies.
    >
    > Let's say the user modified the parent row and added new child rows.
    >
    > Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
    > the
    > added rows as you would expect. It also includes any modified parents. If
    > you do a DataAdapter.Update(dsAdded.table), it will insert the new child
    > rows AND will also update the parent row if it had been modified. Fine so
    > far. Here is what happens next. You do the
    > DataAdapter.Update(dsModified.table). Bingo - concurrency violation
    > because
    > the timestamp in the parent row has already changed.
    >
    > Sample SQL:
    >
    > INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
    > [LastUpdatedBy]) VALUES (@AccessFlag, @Description, @LastUpdatedBy);SELECT
    > AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
    > WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
    >
    > UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
    > [Description] = @Description, [LastUpdated] = getutcdate(),
    > [LastUpdatedBy]
    > = @LastUpdatedBy WHERE (([AccessFlagLookupID] =
    > @Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @Original_TS));SELECT
    > CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
    > (AccessFlagLookupID = @AccessFlagLookupID)
    >
    > That "(CAST(TS AS INT) = @Original_TS));" works with the first update with
    > the added rows (modified parent) but fails when it gets updated again when
    > the modified rows get processed.
    >
    > The way I found this was through getting unexpected concurrency errors.
    > Stepping through the code and monitoring changes in the database timestamp
    > values spotted the problem.
    >
    > Nasty.
    >
    >
    Mike C#, Nov 17, 2007
    #5
  6. Rob Dob

    Rob Dob Guest

    Thank you, I will try using the BeginTransaction .. Commit Transaction,

    yes this is a multi-user application, about 50+ users,

    Thank you, I will report back.

    "Mike C#" <> wrote in message
    news:%...
    > Do you have more information concerning usage patterns? I.e., is there
    > more than one person attempting to update/add rows simultaneously? Or is
    > this all happening with only a single user? Also, what is your PK on the
    > Customers table? What happens if you wrap the INSERT INTO...SELECT in a
    > BEGIN TRANSACTION...COMMIT TRANSACTION?
    >
    > "Rob Dob" <> wrote in message
    > news:...
    > Hi,
    >
    > -1- I have winform that contains some textboxes, comboboxes etc these are
    > databound to a BindingSource
    > -2- if the user decides to add a new record I call BindingSource.AddNew()
    > -2- The BindingSource is bound to my dataset.
    > -4- when the user clicks on the Save Record button my function looks like
    > the following:
    >
    > try
    > {
    > this.CustomerBindingSource.EndEdit();
    > this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy =
    > "XXX";
    > this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
    > = DateTime.Now;
    > int ncount =
    > this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
    > }
    > catch (DBConcurrencyException oEx )
    > {
    > MessageBox.Show("Concurrency Erro: " + oEx.Message);
    > }
    > catch (System.Exception ex)
    > {
    > MessageBox.Show("Other Error: " + ex.Message);
    > }
    >
    > -5- The Dataset that is bound to the BindingSource has an identity field
    > which is an int and is also the primary key on the table, The insert
    > command that is generated by the vs2005 designer does NOT contain the
    > identy field but does a select after the insert inorder to repopulate the
    > dataset with the new id as send below:
    > INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@Date,
    > @ContactLastName);
    > SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
    > = SCOPE_IDENTITY())
    >
    > -6- I am getting some concurrency violations with updates but I am also
    > getting the odd concurrency violation with Adding new records is this
    > possible? also it appeard that for some reason today the update command
    > was also not updating some records down to the database, however then the
    > app was restarted and for some reason it started to work..
    > Any help would be greatly appreciated.
    > Thanks,
    >
    >
    > "Jim Rand" <> wrote in message
    > news:...
    >> I'm going to make several assumptions.
    >>
    >> 1) You use identity keys in the database.
    >> 2) You have figured out how to get the keys back to replace negative keys
    >> in
    >> your dataset that existed prior to update.
    >> 3) You are using timestamps for concurrency (that's a big assumption).
    >> 4) You process the dataset updates in chunks using the
    >> dataset.GetChanges(Deleted | Modified | Added)
    >> 5) You process the deletes first up the hierarchy. Then, you process the
    >> adds down the parent chain. Finally, you process the modifies.
    >>
    >> Let's say the user modified the parent row and added new child rows.
    >>
    >> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
    >> the
    >> added rows as you would expect. It also includes any modified parents.
    >> If
    >> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
    >> rows AND will also update the parent row if it had been modified. Fine so
    >> far. Here is what happens next. You do the
    >> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
    >> because
    >> the timestamp in the parent row has already changed.
    >>
    >> Sample SQL:
    >>
    >> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
    >> [LastUpdatedBy]) VALUES (@AccessFlag, @Description,
    >> @LastUpdatedBy);SELECT
    >> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
    >> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
    >>
    >> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
    >> [Description] = @Description, [LastUpdated] = getutcdate(),
    >> [LastUpdatedBy]
    >> = @LastUpdatedBy WHERE (([AccessFlagLookupID] =
    >> @Original_AccessFlagLookupID) AND (CAST(TS AS INT) =
    >> @Original_TS));SELECT
    >> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
    >> (AccessFlagLookupID = @AccessFlagLookupID)
    >>
    >> That "(CAST(TS AS INT) = @Original_TS));" works with the first update
    >> with
    >> the added rows (modified parent) but fails when it gets updated again
    >> when
    >> the modified rows get processed.
    >>
    >> The way I found this was through getting unexpected concurrency errors.
    >> Stepping through the code and monitoring changes in the database
    >> timestamp
    >> values spotted the problem.
    >>
    >> Nasty.
    >>
    >>

    >
    >
    Rob Dob, Nov 17, 2007
    #6
  7. Rob Dob

    Mike C# Guest

    This problem is occurring when 50+ users are attempting to add/update rows
    simultaneously then; not just one user? That's what I'm trying to discover.
    And yes, please try wrapping your inserts and updates in single transactions
    so we can determine if that helps alleviate the problem for you.

    "Rob Dob" <> wrote in message
    news:...
    > Thank you, I will try using the BeginTransaction .. Commit Transaction,
    >
    > yes this is a multi-user application, about 50+ users,
    >
    > Thank you, I will report back.
    >
    > "Mike C#" <> wrote in message
    > news:%...
    >> Do you have more information concerning usage patterns? I.e., is there
    >> more than one person attempting to update/add rows simultaneously? Or is
    >> this all happening with only a single user? Also, what is your PK on the
    >> Customers table? What happens if you wrap the INSERT INTO...SELECT in a
    >> BEGIN TRANSACTION...COMMIT TRANSACTION?
    >>
    >> "Rob Dob" <> wrote in message
    >> news:...
    >> Hi,
    >>
    >> -1- I have winform that contains some textboxes, comboboxes etc these are
    >> databound to a BindingSource
    >> -2- if the user decides to add a new record I call BindingSource.AddNew()
    >> -2- The BindingSource is bound to my dataset.
    >> -4- when the user clicks on the Save Record button my function looks like
    >> the following:
    >>
    >> try
    >> {
    >> this.CustomerBindingSource.EndEdit();
    >> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].AddedBy
    >> = "XXX";
    >> this.dataSetCustomers1.Customers[CustomerBindingSource.Position].DateLastEdited
    >> = DateTime.Now;
    >> int ncount =
    >> this.customersTableAdapter.Update(this.dataSetCustomers1.Customers);
    >> }
    >> catch (DBConcurrencyException oEx )
    >> {
    >> MessageBox.Show("Concurrency Erro: " + oEx.Message);
    >> }
    >> catch (System.Exception ex)
    >> {
    >> MessageBox.Show("Other Error: " + ex.Message);
    >> }
    >>
    >> -5- The Dataset that is bound to the BindingSource has an identity field
    >> which is an int and is also the primary key on the table, The insert
    >> command that is generated by the vs2005 designer does NOT contain the
    >> identy field but does a select after the insert inorder to repopulate the
    >> dataset with the new id as send below:
    >> INSERT INTO [Customers] ([Date], [ContactLastName], ) VALUES (@Date,
    >> @ContactLastName);
    >> SELECT CustomerID, Date, ContactLastName FROM Customers WHERE (CustomerID
    >> = SCOPE_IDENTITY())
    >>
    >> -6- I am getting some concurrency violations with updates but I am also
    >> getting the odd concurrency violation with Adding new records is this
    >> possible? also it appeard that for some reason today the update command
    >> was also not updating some records down to the database, however then the
    >> app was restarted and for some reason it started to work..
    >> Any help would be greatly appreciated.
    >> Thanks,
    >>
    >>
    >> "Jim Rand" <> wrote in message
    >> news:...
    >>> I'm going to make several assumptions.
    >>>
    >>> 1) You use identity keys in the database.
    >>> 2) You have figured out how to get the keys back to replace negative
    >>> keys in
    >>> your dataset that existed prior to update.
    >>> 3) You are using timestamps for concurrency (that's a big assumption).
    >>> 4) You process the dataset updates in chunks using the
    >>> dataset.GetChanges(Deleted | Modified | Added)
    >>> 5) You process the deletes first up the hierarchy. Then, you process the
    >>> adds down the parent chain. Finally, you process the modifies.
    >>>
    >>> Let's say the user modified the parent row and added new child rows.
    >>>
    >>> Here is what I discovered. Dataset.GetChanges(DataRowState.Added) gets
    >>> the
    >>> added rows as you would expect. It also includes any modified parents.
    >>> If
    >>> you do a DataAdapter.Update(dsAdded.table), it will insert the new child
    >>> rows AND will also update the parent row if it had been modified. Fine
    >>> so
    >>> far. Here is what happens next. You do the
    >>> DataAdapter.Update(dsModified.table). Bingo - concurrency violation
    >>> because
    >>> the timestamp in the parent row has already changed.
    >>>
    >>> Sample SQL:
    >>>
    >>> INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
    >>> [LastUpdatedBy]) VALUES (@AccessFlag, @Description,
    >>> @LastUpdatedBy);SELECT
    >>> AccessFlagLookupID, CAST(TS AS INT) AS TS FROM
    >>> AgencyNET.AccessFlagLookup
    >>> WHERE (AccessFlagLookupID = SCOPE_IDENTITY())
    >>>
    >>> UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
    >>> [Description] = @Description, [LastUpdated] = getutcdate(),
    >>> [LastUpdatedBy]
    >>> = @LastUpdatedBy WHERE (([AccessFlagLookupID] =
    >>> @Original_AccessFlagLookupID) AND (CAST(TS AS INT) =
    >>> @Original_TS));SELECT
    >>> CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
    >>> (AccessFlagLookupID = @AccessFlagLookupID)
    >>>
    >>> That "(CAST(TS AS INT) = @Original_TS));" works with the first update
    >>> with
    >>> the added rows (modified parent) but fails when it gets updated again
    >>> when
    >>> the modified rows get processed.
    >>>
    >>> The way I found this was through getting unexpected concurrency errors.
    >>> Stepping through the code and monitoring changes in the database
    >>> timestamp
    >>> values spotted the problem.
    >>>
    >>> Nasty.
    >>>
    >>>

    >>
    >>

    >
    >
    Mike C#, Nov 18, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Vik

    Concurrency violation

    Vik, Dec 7, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    454
  2. Luke Airig
    Replies:
    0
    Views:
    788
    Luke Airig
    Dec 31, 2003
  3. Mr. SweatyFinger

    why why why why why

    Mr. SweatyFinger, Nov 28, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    880
    Mark Rae
    Dec 21, 2006
  4. Mr. SweatyFinger
    Replies:
    2
    Views:
    1,843
    Smokey Grindel
    Dec 2, 2006
  5. PaulNaude

    How 2 hide RED concurrency violation indicator

    PaulNaude, Dec 5, 2005, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    140
    PaulNaude
    Dec 5, 2005
Loading...

Share This Page