begin transaction
update mytable
set a = x.a
from mytable m inner join xtable x on m.id = x.id
where m.a <> x.a
update mytable
set b = x.b
from mytable m inner join xtable x on m.id = x.id
where m.b <> x.b
...
commit transaction
It runs without error, but afterwards there were a few rows where a
should have been updated and was not, and a few were b should have
been updated and was not.
I am embarrased to say I did not check to see if any of these were
obviously the same rows.
But I *thought* that the code above should work cleanly, and if it did
not it would hang or complain or something, not just misfire quietly,
if indeed it did misfire.
SQL2000 sp2, fwiw.
No, I don't have to run it quite this way, or in an overall tranaction
at all, I suppose, I just though it nice and harmless. Should it have
been?
Thanks.
Joshjxstern wrote:
> I have some code like:
> begin transaction
> update mytable
> set a = x.a
> from mytable m inner join xtable x on m.id = x.id
> where m.a <> x.a
> update mytable
> set b = x.b
> from mytable m inner join xtable x on m.id = x.id
> where m.b <> x.b
> ...
> commit transaction
>
> It runs without error, but afterwards there were a few rows where a
> should have been updated and was not, and a few were b should have
> been updated and was not.
> I am embarrased to say I did not check to see if any of these were
> obviously the same rows.
> But I *thought* that the code above should work cleanly, and if it did
> not it would hang or complain or something, not just misfire quietly,
> if indeed it did misfire.
> SQL2000 sp2, fwiw.
> No, I don't have to run it quite this way, or in an overall tranaction
> at all, I suppose, I just though it nice and harmless. Should it have
> been?
> Thanks.
> Josh
Doing these updates within a single transaction would not have allowed
some records to update but not others. A conflict in a transaction
would manifest as some sort of blocking, resulting in an error.
A "trick" that you can use to test updates like this is to not COMMIT,
but ROLLBACK. Immediately before the ROLLBACK, do a select against the
affected records to make sure the changes you expect were actually made.
Don't commit until you are sure the update is correct.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0110_01C6A1F0.A88836C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If the code below is your actual code, then you are not verifying =successful completion of each of the UPDATE queries. It is necessary to =verify each step and execute a ROLLBACK if there is a problem. And this =code will not have an @.@.Error if there are no rows that meet the =criterai to update. If it is important to abort if no rows are found, =then this has to be modified. (In SQL 2005, there is a more robust =structured error handling capability that doesn't require the constant =checking.)
Transaction code should be more like:
BEGIN TRANSACTION
UPDATE MyTable
SET a =3D x.a
FROM MyTable m
JOIN xTable x
ON m.ID =3D x.ID
WHERE m.a <> x.a
IF @.@.Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
UPDATE MyTable
SET b =3D x.b
FROM MyTable m
JOIN xTable x
ON x.ID =3D m.ID
WHERE m.b <> x.b
IF @.@.Error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
END TRANSACTION
You may be able to revise the query to UPDATE in a single query.
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"jxstern" <jxstern@.wherever.com> wrote in message =news:530ua21cafjbodm71j0f7pht79luofu1p8@.4ax.com...
>I have some code like:
> > begin transaction
> > update mytable
> set a =3D x.a
> from mytable m inner join xtable x on m.id =3D x.id
> where m.a <> x.a > > update mytable
> set b =3D x.b
> from mytable m inner join xtable x on m.id =3D x.id
> where m.b <> x.b > > ...
> > commit transaction
> > > It runs without error, but afterwards there were a few rows where a
> should have been updated and was not, and a few were b should have
> been updated and was not.
> > I am embarrased to say I did not check to see if any of these were
> obviously the same rows.
> > But I *thought* that the code above should work cleanly, and if it did
> not it would hang or complain or something, not just misfire quietly,
> if indeed it did misfire.
> > SQL2000 sp2, fwiw.
> > No, I don't have to run it quite this way, or in an overall tranaction
> at all, I suppose, I just though it nice and harmless. Should it have
> been?
> > Thanks.
> > Josh
--=_NextPart_000_0110_01C6A1F0.A88836C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If the code below is your actual code, =then you are not verifying successful completion of each of the UPDATE queries. It is =necessary to verify each step and execute a ROLLBACK if there is a problem. And this code will not have an @.@.Error if there are no =rows that meet the criterai to update. If it is important to abort if no rows are =found, then this has to be modified. (In SQL 2005, there is a more robust =structured error handling capability that doesn't require the constant checking.)
Transaction code should be more =like:
BEGIN TRANSACTION
UPDATE =MyTable
=SET a =3D x.a
=FROM MyTable m
JOIN xTable x
&nbs=p; ON m.ID =3D x.ID
=WHERE m.a x.a
IF @.@.Error = 0
BEGIN
ROLLBACK TRANSACTION
=RETURN
END
UPDATE =MyTable
=SET b =3D x.b
=FROM MyTable m
JOIN xTable x
&nbs=p; ON x.ID =3D m.ID
=WHERE m.b x.b
IF @.@.Error = 0
BEGIN
=ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
END TRANSACTION
You may be able to revise the query to UPDATE in =a single query.
-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."
"jxstern"
--=_NextPart_000_0110_01C6A1F0.A88836C0--|||On Fri, 7 Jul 2006 18:10:41 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>If the code below is your actual code, then you are not
>verifying successful completion of each of the UPDATE queries.
Thanks you for your concern!
Actual code uses
select @.myerr = @.@.error, @.mycnt = @.@.rowcount
after each update, does test @.myerr and rollback if anything is wrong,
why else use a transaction? But that part works fine, the mystery is
why some rows seem to be missed in the completed and apparently
error-free transaction.
Josh|||On Fri, 07 Jul 2006 19:59:27 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>Doing these updates within a single transaction would not have allowed
>some records to update but not others. A conflict in a transaction
>would manifest as some sort of blocking, resulting in an error.
That's what I'd expect.
>A "trick" that you can use to test updates like this is to not COMMIT,
>but ROLLBACK. Immediately before the ROLLBACK, do a select against the
>affected records to make sure the changes you expect were actually made.
> Don't commit until you are sure the update is correct.
Oh, it's much worse than that!
:)
I actually run a pre-check report on what needs to be fixed, so I
already have a count of records that the same logic picks out as a
pure select, so if I'm running it manually, I can see right away that
the count is wrong. And when I run it as a script, I have print
statements (nicer than nocount off!) that reports the number of rows
affected by each statement, that I can compare to the pre-check. And
afterwards I run a post-check that should report zero - and doesn't.
But freakily enough, simply rerunning the same script as second time,
seemed to pick up the missed rows.
(worse yet, I actually ran the same script against three related
databases, and it worked 100% on the first two, it was only the third
database where it acted weird)
It's as if someone wrote some new rows in immediately after the update
script ran, but nobody was, and indeed the rows have create date and
modified date fields, the later kept by trigger, and they haven't been
touched in a month.
--
But I gather from both responses so far that at least I haven't
overlooked some basic kind of known interference (feature or bug) that
might allow some rows to be skipped in this kind of code structure.
Thanks for the reality checks.
Josh|||JXStern wrote:
> On Fri, 07 Jul 2006 19:59:27 -0500, Tracy McKibben
> <tracy@.realsqlguy.com> wrote:
>> Doing these updates within a single transaction would not have allowed
>> some records to update but not others. A conflict in a transaction
>> would manifest as some sort of blocking, resulting in an error.
> That's what I'd expect.
>> A "trick" that you can use to test updates like this is to not COMMIT,
>> but ROLLBACK. Immediately before the ROLLBACK, do a select against the
>> affected records to make sure the changes you expect were actually made.
>> Don't commit until you are sure the update is correct.
> Oh, it's much worse than that!
> :)
> I actually run a pre-check report on what needs to be fixed, so I
> already have a count of records that the same logic picks out as a
> pure select, so if I'm running it manually, I can see right away that
> the count is wrong. And when I run it as a script, I have print
> statements (nicer than nocount off!) that reports the number of rows
> affected by each statement, that I can compare to the pre-check. And
> afterwards I run a post-check that should report zero - and doesn't.
> But freakily enough, simply rerunning the same script as second time,
> seemed to pick up the missed rows.
> (worse yet, I actually ran the same script against three related
> databases, and it worked 100% on the first two, it was only the third
> database where it acted weird)
> It's as if someone wrote some new rows in immediately after the update
> script ran, but nobody was, and indeed the rows have create date and
> modified date fields, the later kept by trigger, and they haven't been
> touched in a month.
> --
> But I gather from both responses so far that at least I haven't
> overlooked some basic kind of known interference (feature or bug) that
> might allow some rows to be skipped in this kind of code structure.
> Thanks for the reality checks.
> Josh
>
Could you possibly have dupes that are confusing things? Just guessing...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||jxstern wrote:
> I have some code like:
> begin transaction
> update mytable
> set a = x.a
> from mytable m inner join xtable x on m.id = x.id
> where m.a <> x.a
> update mytable
> set b = x.b
> from mytable m inner join xtable x on m.id = x.id
> where m.b <> x.b
>
Josh,
I would first of all combine these 2 updates into one:
update mytable
set a = case when m.a <> x.a then x.a else m.a end,
b = case when m.b <> x.b then x.b else m.b end
from mytable m inner join xtable x on m.id = x.id
where m.a <> x.a
This under most circumstances performs better.
Next, because you did not post DDL, I can only make a wild guess.
Suppose m.a is null and x.a = 12. Note that m.a <> x.a is FALSE, and
m.a will not get updated. If m.a and x.a are nullable, instead of m.a
<> x.a you should use:
(m.a <> x.a ) or (m.a is null and x.a is not null) or (m.a is not null
and x.a is null)|||On 8 Jul 2006 13:16:25 -0700, "Alexander Kuznetsov"
<AK_TIREDOFSPAM@.hotmail.COM> wrote:
>Josh,
>I would first of all combine these 2 updates into one:
>update mytable
> set a = case when m.a <> x.a then x.a else m.a end,
> b = case when m.b <> x.b then x.b else m.b end
> from mytable m inner join xtable x on m.id = x.id
> where m.a <> x.a
>This under most circumstances performs better.
>Next, because you did not post DDL, I can only make a wild guess.
>Suppose m.a is null and x.a = 12. Note that m.a <> x.a is FALSE, and
>m.a will not get updated. If m.a and x.a are nullable, instead of m.a
><> x.a you should use:
>(m.a <> x.a ) or (m.a is null and x.a is not null) or (m.a is not null
>and x.a is null)
The actual code is rather more complicated (!), sometimes it does
involve nulls but I take care of those properly, and there would be a
lot of tables involved in different joins for the different cases, so
I'm guessing it might not be all that much faster due to caching and
such. It's a batch process that runs only on special occassions, so
efficiency isn't the major concern, anyway. FWIW, takes about twenty
minutes on the dev box for six to ten update statements, probably run
2x faster on the production box with more processors and more RAM.
(actually, I though I should probably break up the transaction anyway
to make the script more production-friendly, now that you mention it!)
But I remain mystified by the behavior seen.
Josh|||On Sat, 08 Jul 2006 13:06:30 -0500, Tracy McKibben
<tracy@.realsqlguy.com> wrote:
>Could you possibly have dupes that are confusing things? Just guessing...
It's a thought, I know eliminate-dups scripts act like that, don't
they? The only way that would happen here is if I failed to fully
define my joins ... hey, that's something to look at on Monday,
thanks!
Josh
No comments:
Post a Comment