Code Side Tower of Babel Update Problem.

I have some code that implements some business logic that sits on top of an
SQL Server Express 2005 DB.
The database is fine, but I have found a potential issue with the updates
that affects my code, and also the functionality of the .Net Dataset, that I
am looking for a way around.
My code is required to work with a large dataset of multiple related tables,
that are only committed on calling the Save() function. This functionality
works fine, but I have found an issue that is perfectly legal in the business
logic, but will fail when attempting to store to the database due to unique
restrictions.
I have a table:
ID Name
1 AAA
2 BBB
The column Name is a unique column.
In the business logic, I can do the following:
* Rename Item[1] to "--"
* Rename Item[2] to "AAA"
* Rename Item[1] to "BBB"
* Call Save()
This is completely valid at the Dataset side, as no unique restriction is
breached. However, at this point, the program will begin iterating through
the updated value, and the following SQL is generated by the dataset:
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
UPDATE tbl SET Name = 'AAA' WHERE ID = 2
The first item executed will be
UPDATE tbl SET Name = 'BBB' WHERE ID = 1
On Table
ID Name
1 AAA
2 BBB
The attempt to update the table to state
ID Name
1 BBB
2 BBB
Will fail, regardless of the fact that the next update will restore the
unique value status.
Is there any way to enable a before and after test on a total commit? Say by
using transactions?
This is a critical issue for my system.
Regards
TrisTris
Do you have an unique constraint on (id,name) ?
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A5BD2ECD-557E-4EF1-A6E2-7208B03CD737@.microsoft.com...
>I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
> I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related
> tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the
> business
> logic, but will fail when attempting to store to the database due to
> unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>|||"Uri Dimant" wrote:
> Tris
> Do you have an unique constraint on (id,name) ?
Yes, in this instance the column Name is unique.
Although this is only an example, it could be applied to any unique set of
fields where this situation could arise.|||> This is completely valid at the Dataset side, as no unique restriction is
> breached.
That's because your Dataset constraints don't match the database. If you
had the same constraints on both the Dataset and database, you would get the
error when the change was made to the dataset rather than when data is later
saved to the database.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
I believe you want a 'deferred constraint checking' feature so that
constraint's aren't enforced until COMMIT. Unfortunately, this feature does
not yet exist in SQL Server. If this is important to you, consider
submitting this as a feature request via Connect:
https://connect.microsoft.com/SQLServer
One workaround is to remove the database unique constraint and implement the
unique restriction in your Save method by querying to ensure modified values
are unique before you commit. Another approach is to first delete all
modified rows and then insert with the new values. However, that can be
problematic if you also have foreign key constraints.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A5BD2ECD-557E-4EF1-A6E2-7208B03CD737@.microsoft.com...
>I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that
> I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related
> tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the
> business
> logic, but will fail when attempting to store to the database due to
> unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say
> by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>|||Tris
>Yes, in this instance the column Name is unique.
Pehaps you need something like that
IF NOT EXISTS (SELECT * FROM Table WHERE Name =@.Name)
UPDATE Table SET name =@.name
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:0EF2AC17-1F9E-4F7B-86DB-3A4B6A809DC1@.microsoft.com...
>
> "Uri Dimant" wrote:
>> Tris
>> Do you have an unique constraint on (id,name) ?
>
> Yes, in this instance the column Name is unique.
> Although this is only an example, it could be applied to any unique set of
> fields where this situation could arise.|||"Dan Guzman" wrote:
> > This is completely valid at the Dataset side, as no unique restriction is
> > breached.
> That's because your Dataset constraints don't match the database. If you
> had the same constraints on both the Dataset and database, you would get the
> error when the change was made to the dataset rather than when data is later
> saved to the database.
The unique restrictions are enforced in the dataset, but i use a tower of
babel switch,
X = 'X'
Y = 'Y'
Save
X = 'Q'
Y = 'X'
X = 'Y'
Save
The constraints on the dataset are never breached.
I can't delete the rows, it would cause far to many headaches. And
dissabling the restrictions would be a pain.
However, i suppose i could create a validate stored procedure...
What do you think of this:
SPValidateTable?()
#TempTable
(
VarChar Name,
Int Count
)
TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
Return
If the result != 0
Throw exception.
It's a hack i know... but it could work.
How could this be implemented before / After commiting a transaction, so
that it could fail a transaction if the method failed? And how can it's scope
be handled in relation to the transaction?
Tris|||Tris wrote:
> I have some code that implements some business logic that sits on top of an
> SQL Server Express 2005 DB.
> The database is fine, but I have found a potential issue with the updates
> that affects my code, and also the functionality of the .Net Dataset, that I
> am looking for a way around.
> My code is required to work with a large dataset of multiple related tables,
> that are only committed on calling the Save() function. This functionality
> works fine, but I have found an issue that is perfectly legal in the business
> logic, but will fail when attempting to store to the database due to unique
> restrictions.
> I have a table:
> ID Name
> 1 AAA
> 2 BBB
> The column Name is a unique column.
> In the business logic, I can do the following:
> * Rename Item[1] to "--"
> * Rename Item[2] to "AAA"
> * Rename Item[1] to "BBB"
> * Call Save()
> This is completely valid at the Dataset side, as no unique restriction is
> breached. However, at this point, the program will begin iterating through
> the updated value, and the following SQL is generated by the dataset:
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
> The first item executed will be
> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
> On Table
> ID Name
> 1 AAA
> 2 BBB
> The attempt to update the table to state
> ID Name
> 1 BBB
> 2 BBB
> Will fail, regardless of the fact that the next update will restore the
> unique value status.
> Is there any way to enable a before and after test on a total commit? Say by
> using transactions?
> This is a critical issue for my system.
> Regards
> Tris
>
Assuming you know the two (or more) ID's that you want to swap:
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
If your table is small enough that you don't mind checking the entire table,
I would suggest that you ditch the temp table:
CREATE PROC SPValidateTable
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
Alternatively, you can selectively check only those names (or dataset
DataRows) that were changed:
CREATE PROC SPValidateName
@.Name varchar(100)
AS
SELECT COUNT(*) AS DuplicateNames
FROM (
SELECT COUNT(*) AS Duplicates
FROM dbo.MyTable
WHERE Name = @.Name
GROUP BY Name
HAVING COUNT(*) > 1) AS Dups
RETURN
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:06E8D3D0-C416-46E4-9369-21405864428C@.microsoft.com...
>
> "Dan Guzman" wrote:
>> > This is completely valid at the Dataset side, as no unique restriction
>> > is
>> > breached.
>> That's because your Dataset constraints don't match the database. If you
>> had the same constraints on both the Dataset and database, you would get
>> the
>> error when the change was made to the dataset rather than when data is
>> later
>> saved to the database.
> The unique restrictions are enforced in the dataset, but i use a tower of
> babel switch,
> X = 'X'
> Y = 'Y'
> Save
> X = 'Q'
> Y = 'X'
> X = 'Y'
> Save
> The constraints on the dataset are never breached.
> I can't delete the rows, it would cause far to many headaches. And
> dissabling the restrictions would be a pain.
> However, i suppose i could create a validate stored procedure...
> What do you think of this:
> SPValidateTable?()
> #TempTable
> (
> VarChar Name,
> Int Count
> )
> TempTable = SELECT DISTINCT Name, COUNT(Name) FROM Table
> SELECT Count(Name) FROM #TempTable WHERE (Count > 1)
> Return
>
> If the result != 0
> Throw exception.
> It's a hack i know... but it could work.
> How could this be implemented before / After commiting a transaction, so
> that it could fail a transaction if the method failed? And how can it's
> scope
> be handled in relation to the transaction?
> Tris|||Tracy McKibben wrote:
> Tris wrote:
>> I have some code that implements some business logic that sits on top
>> of an SQL Server Express 2005 DB.
>> The database is fine, but I have found a potential issue with the
>> updates that affects my code, and also the functionality of the .Net
>> Dataset, that I am looking for a way around.
>> My code is required to work with a large dataset of multiple related
>> tables, that are only committed on calling the Save() function. This
>> functionality works fine, but I have found an issue that is perfectly
>> legal in the business logic, but will fail when attempting to store to
>> the database due to unique restrictions.
>> I have a table:
>> ID Name
>> 1 AAA
>> 2 BBB
>> The column Name is a unique column.
>> In the business logic, I can do the following:
>> * Rename Item[1] to "--"
>> * Rename Item[2] to "AAA"
>> * Rename Item[1] to "BBB"
>> * Call Save()
>> This is completely valid at the Dataset side, as no unique restriction
>> is breached. However, at this point, the program will begin iterating
>> through the updated value, and the following SQL is generated by the
>> dataset:
>> UPDATE tbl SET Name = 'BBB' WHERE ID = 1
>> UPDATE tbl SET Name = 'AAA' WHERE ID = 2
>> The first item executed will be UPDATE tbl SET Name = 'BBB' WHERE ID = 1
>> On Table
>> ID Name
>> 1 AAA
>> 2 BBB
>> The attempt to update the table to state
>> ID Name
>> 1 BBB
>> 2 BBB
>> Will fail, regardless of the fact that the next update will restore
>> the unique value status.
>> Is there any way to enable a before and after test on a total commit?
>> Say by using transactions?
>> This is a critical issue for my system.
>> Regards
>> Tris
> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
I forgot the WHERE clause, although on a small table, it should run fine
without one.
UPDATE tbl
SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
WHERE ID IN (1, 2)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||> Assuming you know the two (or more) ID's that you want to swap:
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Hi Tracy,
Thanks, that's interesting, but unfortunately, it's done using the MS
Dataset update mechanism, so if two elements are effectively swapped in code,
then they are not aware of each other.
Tris|||Hi Dan,
Thanks, yes that makes sense.
Can this be run inside a transaction to check before i call the Commit?
I'm not sure how the scope will affect things, i mean, a transaction can't
see what other transactions are doing, so if i ran two concurrent
transactions, and ran the test first, it passed and then i commitied, the
second one may fail when the test runs?
But as far as i can see it seems viable.
Tris|||> Can this be run inside a transaction to check before i call the Commit?
Yes.
> I'm not sure how the scope will affect things, i mean, a transaction can't
> see what other transactions are doing, so if i ran two concurrent
> transactions, and ran the test first, it passed and then i commitied, the
> second one may fail when the test runs?
You are wise to consider the potential concurrency issues. If multiple
users call Save at the same time, it is likely that a deadlock will occur.
Consider the following scenario with the default read committed transaction
isolation level (assuming the validation proc scans all rows).
- User 1 changes row A and saves
- User 2 changes row B and saves
- User 1 Save method updates row A
- User 2 Save method updates row B
- User 1 Save method executes validation proc, which is blocked when the
uncommitted row B is touched
- User 2 Save method executes validation proc, which is blocked when the
uncommitted row A is touched
- User 1 is blocked by User 2 and User 2 is blocked by User 1. SQL Server
chooses one of the users to be the deadlock victim
One way to address the problem is to specify a TABLOCK hint on the updates.
This will have the effect of serializing the updates and validation.
Whether or not the performance hit is acceptable depends on your situation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:C71FDF05-5E8B-42E8-955C-D17EC6BC6A42@.microsoft.com...
> Hi Dan,
> Thanks, yes that makes sense.
> Can this be run inside a transaction to check before i call the Commit?
> I'm not sure how the scope will affect things, i mean, a transaction can't
> see what other transactions are doing, so if i ran two concurrent
> transactions, and ran the test first, it passed and then i commitied, the
> second one may fail when the test runs?
> But as far as i can see it seems viable.
> Tris|||Hello, Tracy
Without the WHERE clause, your UPDATE statement would set the Name to
NULL for the other rows, so it's a very important clause (but I guess a
well designed table would not allow nulls in that column).
Razvan
Tracy McKibben wrote:
> Tracy McKibben wrote:
> >
> > Assuming you know the two (or more) ID's that you want to swap:
> >
> > UPDATE tbl
> > SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
> >
> >
> I forgot the WHERE clause, although on a small table, it should run fine
> without one.
> UPDATE tbl
> SET Name = CASE WHEN ID = 1 THEN 'BBB' WHEN ID = 2 THEN 'AAA' END
> WHERE ID IN (1, 2)
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com