Can Kill Command undo changes outwith a transaction?

23 Mar 2006 8:49 PM

woodk

In SQL 2000 I have a problem whereby it appears that a process successfully
saved data (inserts followed by updates), hung, and when I killed the process
some of the data disapeared. I had understood that only declared transactions
would be rolled back, but in this case it appears that untransacted changes
were undone.

When I say that the process successfully saved data, the data was read from
the database in a separate program (Crystal Reports) and was printed out.

By "the data disappeared", I mean that the inserted rows are no longer in
the database, and the rows that were updated (according to the hard copy) are
in a previous state. The tables involved in this have insert/update/delete
triggers that record all changes; they do not record the changes that are
involved, and have been working without fault for several months.

The updates were grouped in transactions, whereas the inserts were not.
There was no over-all transaction.

Sure, at first glance you might think it's just a transaction that did not
complete and that I'm overlooking something, however I have the print out of
the missing data to show the data was in there.

Can someone confirm or refute that my interpretation of what happened is
possible, or suggest an alternative explanation?

24 Mar 2006 3:18 AM

Andrew J. Kelly

My guess is the report was run under the Read Uncommitted isolation level
(or used NOLOCK). As such it would see the changes if run while the
transaction was in progress and they would have been rolled back when you
killed it.

--
Andrew J. Kelly SQL MVP

Show quote

"woodk" <wo***@discussions.microsoft.com> wrote in message
news:F6E9C8E0-BD1C-49BF-BDB8-094BFC68C964@microsoft.com...
> In SQL 2000 I have a problem whereby it appears that a process
> successfully
> saved data (inserts followed by updates), hung, and when I killed the
> process
> some of the data disapeared. I had understood that only declared
> transactions
> would be rolled back, but in this case it appears that untransacted
> changes
> were undone.
>
> When I say that the process successfully saved data, the data was read
> from
> the database in a separate program (Crystal Reports) and was printed out.
>
> By "the data disappeared", I mean that the inserted rows are no longer in
> the database, and the rows that were updated (according to the hard copy)
> are
> in a previous state. The tables involved in this have insert/update/delete
> triggers that record all changes; they do not record the changes that are
> involved, and have been working without fault for several months.
>
> The updates were grouped in transactions, whereas the inserts were not.
> There was no over-all transaction.
>
> Sure, at first glance you might think it's just a transaction that did not
> complete and that I'm overlooking something, however I have the print out
> of
> the missing data to show the data was in there.
>
> Can someone confirm or refute that my interpretation of what happened is
> possible, or suggest an alternative explanation?

24 Mar 2006 10:38 AM

woodk

Thank you for your suggestion. The operations were not wrapped in a single
transaction, and some of them should have been atomic in their own right.
However as we've found an indication that Crystal does indeed read
uncommitted, we're investigating the possibility that an earlier transaction
by that user may not have been properly committed. If this is the case, it
may be that this was also what led to the situation where I had to issue the
Kill command in the first place.

Show quote

"Andrew J. Kelly" wrote:

> My guess is the report was run under the Read Uncommitted isolation level
> (or used NOLOCK). As such it would see the changes if run while the
> transaction was in progress and they would have been rolled back when you
> killed it.
>
> --
> Andrew J. Kelly SQL MVP