SEO

After reading Erland Sommarskog's most enlightening articles on SQL Server
2000's error handling capabilities
(http://www.sommarskog.se/error-handling-I.html and
http://www.sommarskog.se/error-handling-II.html) I have a question regarding
batch cancellation caused by a client.

Because a batch cancel request from a client does not cause a rollback, is
there any danger of the commands that were executed in the batch before the
client cancelled to be persistent in the database since no commit is ever
called? Or is the real problem indefinite locking that only disappears when
the client connection is terminated?

Any insights are appreciated!


Regards,


Tyler

Tyler Hudson wrote:

> After reading Erland Sommarskog's most enlightening articles on SQL Server
> 2000's error handling capabilities
> (http://www.sommarskog.se/error-handling-I.html and
> http://www.sommarskog.se/error-handling-II.html) I have a question regarding
> batch cancellation caused by a client.
>
> Because a batch cancel request from a client does not cause a rollback, is
> there any danger of the commands that were executed in the batch before the
> client cancelled to be persistent in the database since no commit is ever
> called? Or is the real problem indefinite locking that only disappears when
> the client connection is terminated?
>
> Any insights are appreciated!

If a batch is being executed and you do a cancel, everything done before the
cancel is acknowledged and executed is still done. If a tx was underway, it still
is, and the updates and locks are still in effect until you commit or rollback,
at which time everything that was done during the tx and all locks obtained,
are released.
If a batch is executed in a non-transactional mode, I believe that everything
done by the batch is permanently done. No locks remain after the cancel.
Joe Weinstein at BEA
>
>
> Regards,
>
>
> Tyler
>
>

So let's say the client disconnects after issuing the cancel command. Would
SQL server roll back the commands executed before the cancel command?


"Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
news:40F6AC1A.3040303@bea.com...
>
>
> Tyler Hudson wrote:
>
> > After reading Erland Sommarskog's most enlightening articles on SQL
Server
> > 2000's error handling capabilities
> > (http://www.sommarskog.se/error-handling-I.html and
> > http://www.sommarskog.se/error-handling-II.html) I have a question
regarding
> > batch cancellation caused by a client.
> >
> > Because a batch cancel request from a client does not cause a rollback,
is
> > there any danger of the commands that were executed in the batch before
the
> > client cancelled to be persistent in the database since no commit is
ever
> > called? Or is the real problem indefinite locking that only disappears
when
> > the client connection is terminated?
> >
> > Any insights are appreciated!
>
> If a batch is being executed and you do a cancel, everything done before
the
> cancel is acknowledged and executed is still done. If a tx was underway,
it still
> is, and the updates and locks are still in effect until you commit or
rollback,
> at which time everything that was done during the tx and all locks
obtained,
> are released.
> If a batch is executed in a non-transactional mode, I believe that
everything
> done by the batch is permanently done. No locks remain after the cancel.
> Joe Weinstein at BEA
> >
> >
> > Regards,
> >
> >
> > Tyler
> >
> >
>

Tyler Hudson wrote:

> So let's say the client disconnects after issuing the cancel command. Would
> SQL server roll back the commands executed before the cancel command?

If the connection was running in a transactional state, then the DBMS would find the
client had gone away, and would roll back the tx and anything it had done,
whether the cancel had been sent or not, though depending on how big the batch
was, the cancel might save some time.
If the connection was not in a transactional state, whatever was done before the
cancel or disconnect stays done.
Joe

>
>
> "Joe Weinstein" <joeNOSPAM@bea.com> wrote in message
> news:40F6AC1A.3040303@bea.com...
>
>>
>>Tyler Hudson wrote:
>>
>>
>>>After reading Erland Sommarskog's most enlightening articles on SQL
>
> Server
>
>>>2000's error handling capabilities
>>>(http://www.sommarskog.se/error-handling-I.html and
>>>http://www.sommarskog.se/error-handling-II.html) I have a question
>
> regarding
>
>>>batch cancellation caused by a client.
>>>
>>>Because a batch cancel request from a client does not cause a rollback,
>
> is
>
>>>there any danger of the commands that were executed in the batch before
>
> the
>
>>>client cancelled to be persistent in the database since no commit is
>
> ever
>
>>>called? Or is the real problem indefinite locking that only disappears
>
> when
>
>>>the client connection is terminated?
>>>
>>>Any insights are appreciated!
>>
>>If a batch is being executed and you do a cancel, everything done before
>
> the
>
>>cancel is acknowledged and executed is still done. If a tx was underway,
>
> it still
>
>>is, and the updates and locks are still in effect until you commit or
>
> rollback,
>
>>at which time everything that was done during the tx and all locks
>
> obtained,
>
>>are released.
>> If a batch is executed in a non-transactional mode, I believe that
>
> everything
>
>>done by the batch is permanently done. No locks remain after the cancel.
>>Joe Weinstein at BEA
>>
>>>
>>>Regards,
>>>
>>>
>>>Tyler
>>>
>>>
>>
>
>

Tyler Hudson (TylerH@Spam.MeNOTallpax.com) writes:
> Because a batch cancel request from a client does not cause a rollback,
> is there any danger of the commands that were executed in the batch
> before the client cancelled to be persistent in the database since no
> commit is ever called? Or is the real problem indefinite locking that
> only disappears when the client connection is terminated?

The most likely problem is the indefinite locking. But of course you
could run into data being persisted that you did not intend to persist.
Assume that further afield you have another error where you incorrectly
commit one time too much.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Tyler Hudson (TylerH@Spam.MeNOTallpax.com) writes:
> So let's say the client disconnects after issuing the cancel command.
> Would SQL server roll back the commands executed before the cancel
> command?

Uncommitted transactions would be rolled back. But anything that already
has been committed will of course not be rolled back. Say that the user
submits a call to a stored procedures that performs this:

BEGIN TRANSACTION A
-- do some stuff
COMMIT TRANSACTION A

BEGIN TRANSACTION B
-- do some stuff
COMMIT TRANSACTION B

UPDATE tbl SET ...

BEGIN TRANSACTION C
-- do some stuff
BEGIN TRANSACTION D
-- do some other stuff
COMMIT TRANSACTION D
-- do even more stuff
COMMIT TRANSACTION C

Say now that the timeout sets in while transaction C is in progress, and
the client then disconnects. Transactions A, B and the UPDATE statement
will remain, but there will be nothing of transaction C. Note that this
applies even if COMMIT TRANSACTION D has been committed, because COMMIT
for a nested transaction only decrements the transaction level. Nothing
is committed.

If the timeout sets in while the UPDATE command is running, transaction
A and B will remain, but there will be no trace of the UPDATE statement.
(And of course not of transactions C and D.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp