Can a commit fail??

Can an error occur /during/ the process of committing a transaction? So:
BeginTraction();
try {
UpdateTable1();
UpdateTable2();
CommitTransaction(); <-- error here?
} catch(Exception e){
RollbackTransaction();
}
Also, what are the implications in such a situation where both the
Updates pass without any errors, but committing the transaction fails?
Is this scenario even possible at all?
If so, what are the suggested best-practices for recovering from such
types of errors?
TIA,
AbdullahYes. If the log disk fills up, the write of the commit sentinal can fail,
thus it's indeed possible for a commit to fail. I'm sure that there are
other scenarios that could cause a commit failure. Therefore, if the commit
fails, the transaction should be rolled back (if that hasn't already
happened as a result of the failure).
"Abdullah Kauchali" <none@.none.com> wrote in message
news:ukGjDWAzFHA.1264@.tk2msftngp13.phx.gbl...
> Can an error occur /during/ the process of committing a transaction? So:
> BeginTraction();
> try {
> UpdateTable1();
> UpdateTable2();
> CommitTransaction(); <-- error here?
> } catch(Exception e){
> RollbackTransaction();
> }
> Also, what are the implications in such a situation where both the
> Updates pass without any errors, but committing the transaction fails?
> Is this scenario even possible at all?
> If so, what are the suggested best-practices for recovering from such
> types of errors?
> TIA,
> Abdullah|||Brian Selzer wrote:
> Yes. If the log disk fills up, the write of the commit sentinal can fail,
> thus it's indeed possible for a commit to fail. I'm sure that there are
> other scenarios that could cause a commit failure. Therefore, if the comm
it
> fails, the transaction should be rolled back (if that hasn't already
> happened as a result of the failure).
Thanks Brian.
Let's use your example. Suppose UpdateTable1() succeeds during the
COMMIT (SQL Server frees the resources and releases the locks for
Table1) and then attempts to commit statements in UpdateTable2() but
then realises "oops, transaction log is full!" Will UpdateTable1()
rollback on a rollback instruction? Won't the transaction logs still
be considered full for the rollback log entries to go through?
:)
(I am actually trying to understand the process of commit in a 2-phase
scenario (distributed transactions), but I'd like to understand the
concept from a local-transaction point of view first. So, I apologise
for my lack of knowledge there!
My question for the 2-phase (distributed) transaction is this: if the
DTC commits all preceding resources and then encounters a problem with
the very last resource in the chain of updates, can/does the DTC
actually "uncommit" the preceding resources it just instructed to
commit? No during the prepare phase, but during the commit phase.)|||The commit of UpdateTable1() succeeded, so a rollback isn't possible. The
commit of UpdateTable2() fails, the log file is full, and the database shuts
down. During recovery (assuming disk space has been freed or otherwise made
available), UpdateTable2() will be rolled back. Changes are written to the
transaction log before they're written to the database, and only after all
of the database changes have been flushed to the disk is the commit sentinal
written to the transaction log, so the recovery process can undo any changes
made by any uncommitted transactions.
I'm not sure exactly how the process works with a distributed transaction.
Maybe there's a different type of sentinal written to the transaction log
after the prepare phase has completed. During the prepare phase, all cached
changes in each participant are flushed to the disk and then a
ready-to-commit signal is sent back to the coordinator. Once the commit
signal has been sent, I don't think a rollback is possible, even if an error
occurs on one of the other participants. If communication is lost before
the commit signal is received, then the participant is required to roll back
the transaction. If it happens afterward, the transaction is supposed to be
committed. Again, I'm not sure exactly how the process works under the
covers. Maybe someone with more knowledge than I can give you a more
difinitive answer.
"Abdullah Kauchali" <none@.none.com> wrote in message
news:uUupV7EzFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Brian Selzer wrote:
> Thanks Brian.
> Let's use your example. Suppose UpdateTable1() succeeds during the
> COMMIT (SQL Server frees the resources and releases the locks for
> Table1) and then attempts to commit statements in UpdateTable2() but
> then realises "oops, transaction log is full!" Will UpdateTable1()
> rollback on a rollback instruction? Won't the transaction logs still
> be considered full for the rollback log entries to go through?
> :)
> (I am actually trying to understand the process of commit in a 2-phase
> scenario (distributed transactions), but I'd like to understand the
> concept from a local-transaction point of view first. So, I apologise
> for my lack of knowledge there!
> My question for the 2-phase (distributed) transaction is this: if the
> DTC commits all preceding resources and then encounters a problem with
> the very last resource in the chain of updates, can/does the DTC
> actually "uncommit" the preceding resources it just instructed to
> commit? No during the prepare phase, but during the commit phase.)
>