submitting hierarchical changes to database

  • Thread starter Thread starter nuno braga via .NET 247
  • Start date Start date
N

nuno braga via .NET 247

Hi,

Having read Microsoft ADO .Net from David Sceppa, i come across an way to partially solve the order problem when updating a dataset that has hierarchical data.

I've followed David Sceppa's suggestion in page 486:

1) submit new parent records
2) submit new child records
3) submit modified parent records
4) submit modified child records
5) submit deleted child records
6) submit deleted parent records

nevertheless i've found (as yet) a problem.. what if we:

1) Delete a record
2) Add a new record but with the same key of the previouslly deleted record.

When commiting to the database we get a constraint error of course.. But modifying the order in wich the submitting is made doesn't seem to be a good ideia..

Is there any workarround someone could suggest?

Any help would be appreciated, thanks
 
nuno braga via .NET 247 said:
Hi,

Having read Microsoft ADO .Net from David Sceppa, i come across an way to
partially solve the order problem when updating a dataset that has
hierarchical data.

I've followed David Sceppa's suggestion in page 486:

1) submit new parent records
2) submit new child records
3) submit modified parent records
4) submit modified child records
5) submit deleted child records
6) submit deleted parent records

nevertheless i've found (as yet) a problem.. what if we:

1) Delete a record
2) Add a new record but with the same key of the previouslly deleted
record.

When commiting to the database we get a constraint error of course.. But
modifying the order in wich the submitting is made doesn't seem to be a
good ideia..

Is there any workarround someone could suggest?

Any help would be appreciated, thanks
Click to expand...
I'd suggest that if you want to delete a record, then add back a new record
using the just-deleted primary key, you should be updating the existing
record. The update would work correctly within the suggested sequence.
 
Nuno,

I know what you are talking about. :-)

I actually disagree with Sceppa's approach (I hope I understood his approach
correctly) on how he recommends to do hierarchical updates - and if i
understood his approach right let me type repeat it here -

Two tables - A parent of B

He says, go recursively, filter out changed rows, which also means I have to
go by row states.

When you do row states, so for inserted records u gotta do -

1. Filter out inserted rows from A, insert them, filter out child rows from
B, insert those.

--- Then u gotta work with updated rows after inserted rows in the same
order.---

2. Filter out updated rows from A, then ditto with B

--- Then u gotta work with Deleted rows IN THE REVERSE Order to respect FK
constraints.---

3. Delete rows from B - Then delete rows from A

--- And for more tables than A & B, you do the above operations
recursively.---

--- --- Here is the problem - the above approach sounds great on paper. You
put it in production - you get unresolvable deadlocks all over. (Why because
one guy starts inserting, the other starts deleting and in that one
situation their rows are on the same page in a clustered index - BIG
PROBLEM).--- ---

My recommended approach is outlined here -
http://www.codebetter.com/blogs/sahil.malik/archive/2005/05/06/62893.aspx ..
and it is also explained in greater depth in Chapter #10 of my upcoming
book.
http://www.codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

Let me know if you have any further questions after reading the posts above.
:-)

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/







nuno braga via .NET 247 said:
Hi,

Having read Microsoft ADO .Net from David Sceppa, i come across an way to
Click to expand...
partially solve the order problem when updating a dataset that has
hierarchical data.
I've followed David Sceppa's suggestion in page 486:

1) submit new parent records
2) submit new child records
3) submit modified parent records
4) submit modified child records
5) submit deleted child records
6) submit deleted parent records

nevertheless i've found (as yet) a problem.. what if we:

1) Delete a record
2) Add a new record but with the same key of the previouslly deleted record.

When commiting to the database we get a constraint error of course.. But
Click to expand...
modifying the order in wich the submitting is made doesn't seem to be a good
ideia..
 
I'd suggest that if you want to delete a record, then add back a new
record
using the just-deleted primary key, you should be updating the existing
record. The update would work correctly within the suggested sequence.
Click to expand...


This will cause transaction deadlocks in a multi -user scenario. See my
reply to nuno for an alternative approach.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/





Miha Markic said:
Hi,

Why don't you delete records first?
Click to expand...
 
Hi Sahil,

1. I don't think this concurrency happens often, or better, it shouldn't.
2. In worst case you get a deadlock which isn't the end of the world.
3. having in mind point 1 and that the data won't be compromised I wouldn't
bother. Getting a concurrency error or a deadlock error - it is not a big
deal imo.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Sahil Malik said:
I'd suggest that if you want to delete a record, then add back a new record
using the just-deleted primary key, you should be updating the existing
record. The update would work correctly within the suggested sequence.
Click to expand...


This will cause transaction deadlocks in a multi -user scenario. See my
reply to nuno for an alternative approach.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/





Miha Markic said:
Hi,

Why don't you delete records first?
Click to expand...
Click to expand...
 
Miha,

The deadlocks can happen a lot often than you'd think. :-). Especially when
you have table level locks.

When a deadlock happens, you will need dba interaction to kill a transaction
if you hadn't exclusively requested a lock as I suggest in my approach. So
yeah a system that doesn't automatically recover, and needs a human to come
fix it in production - thats bad.

And why do it, when you can avoid it? :-)

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/




Miha Markic said:
Hi Sahil,

1. I don't think this concurrency happens often, or better, it shouldn't.
2. In worst case you get a deadlock which isn't the end of the world.
3. having in mind point 1 and that the data won't be compromised I
wouldn't bother. Getting a concurrency error or a deadlock error - it is
not a big deal imo.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Sahil Malik said:
I'd suggest that if you want to delete a record, then add back a new record
using the just-deleted primary key, you should be updating the existing
record. The update would work correctly within the suggested sequence.
Click to expand...


This will cause transaction deadlocks in a multi -user scenario. See my
reply to nuno for an alternative approach.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/





Miha Markic said:
Hi,

Why don't you delete records first?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Hi,

Having read Microsoft ADO .Net from David Sceppa, i come across an way to
partially solve the order problem when updating a dataset that has
hierarchical data.

I've followed David Sceppa's suggestion in page 486:

1) submit new parent records
2) submit new child records
3) submit modified parent records
4) submit modified child records
5) submit deleted child records
6) submit deleted parent records

nevertheless i've found (as yet) a problem.. what if we:

1) Delete a record
2) Add a new record but with the same key of the previouslly deleted
record.

When commiting to the database we get a constraint error of course..
But
modifying the order in wich the submitting is made doesn't seem to be
a
good ideia..

Is there any workarround someone could suggest?

Any help would be appreciated, thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi,

sorry for this late post, but i was ocuppied by another problem.

Thank you all for the replies.

To Miha Markic:

Can't delete first, i would end up trying to delete a row that is
parent of another row not yet updated.

To Peter:

I' must work in a disconnected environment, the transaction is made only
at the end of the session, when the user logs out or commits.

To Sahil Malik:

I've read your blog carefully, and it seemed to be very usefull, thanks
for pointing it to me.. :) anyway, i will use pessimistic lock, as it
seems to be the only answer in a multi-user scenario.
Nevertheless my primary concern is about the original question in my
post..

thinking only of a single-user scenario:

1) the user deletes a record with key "x"
2) the user adds a record with key "x"
3) the user commits and get a constraint violation

can't delete first.. (because of pending child rows)
can't delete next.. (constraint error)

am i loosing something here?


thanks again :)
 
Nuno,
1) the user deletes a record with key "x"
2) the user adds a record with key "x"
Click to expand...

Okay, with the solution I recommended this wouldn't be much of a problem.
Say User #1 starts to delete BEFORE user #2 starts adding keys - if you have
done a HoldLock - the database will detect the hold lock and reject the
change that started last. This behaviour can be overriden using Deadlock
priority.

BTW - I know you are gonna ask "What if they start together" - well they
can never. SQL Server executes interleaved commands on the same resource -
never parallel.

Hope I answered the right question? :-) .. BTW this is given good coverage
in my upcoming book Chap #10.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
 
Hi,

Maybe i didn't express myself well enough :)

The problem is not an user #2 trying to add keys user #1 has deleted.

the problem is user #1 deleting a key 'x' and the same user #1 adding a
row with the same key 'x' (all of this beeing done disconnected from the
database)

when updating he gets a constraint error because the row still exists in
the database.

the database isn't locked for user #1. User #1 is the only user
accessing the database.

sorry for insisting with this, maybe i didn't understand your
explanation very well, but i still didn't figured this out..

thanks for your patience :)
 
Oh no .. thank you for YOUR patience :)

So you have two values X & Y. Y depends on X.

What ur saying is .. What happens when User #2 is disconnectedly (bad
inglish sorri) adding Y, when User #1 has deleted X between #2's fill and
update.

Well then you get a constraint error - thats the end of it. Your data is
safe, which is the eventual goal.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
 
I will give an example:


UserTable
------------
userID (Key)
cityID (FKey)


user #1 deletes user from 'UserTable' with key 'u1', next the same user
#1 adds an user to 'UserTable' with key 'u1'.
there's no user #2.

Follows an update to the database.
the update will start by the inserted rows..
--- Error: user 'u1' already exists. ---

yes it exists in the database, but not in the dataset in memory. the
problem is when i submit the changes to the database:

1) dataadapter.Update(Inserted)
2) dataadapter.update(Modified)
3) dataadapter.Update(Deleted)


if you're starting to fill bored by this i understand :) thanks..
 
The answer to this is -

When a key is generated in the disconnected dataset, it is not "u1", instead
it is an invalid key (could be anything - monkey1, monkey2, monkey3 .. ).
The real keys are fetched as output parameters to the Insertcommand.

Also, for the very same reason, it is not recommended to have PKs which have
business meanings.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
 
Back
Top