Concurrency violation adding records Why?

R

Rob Dob

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,
 
M

Mike C#

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

Rob Dob

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

Jim Rand

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

Mike C#

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?

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 said:
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.
 
R

Rob Dob

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# said:
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?

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 said:
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.
 
M

Mike C#

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 said:
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# said:
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?

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 said:
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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top