RE: Transactions commit during command timeout

From: Mike Vedder (mikevedder_at_online.nospam)
Date: 12/28/04 

Date: Tue, 28 Dec 2004 06:41:01 -0800

Thanks for the response Kevin.

You are correct - I want to understand why the transaction is committed when
the command times out.

I'm not sure I understand all of your response. You say the record will be
inserted, but the COMMIT TRAN will not be executed. Under what circumstance
will the commit not be executed? When there is a timeout? I didn't think
the records could get committed to the database unless the COMMIT TRAN is
executed. The BEGIN TRAN and COMMIT TRAN are executed within the stored
procedure - how can I detect there was a timeout from within the stored
procedure to know that I need to call Rollback? I don't believe @@error will
be set on a timeout, correct?

According the documentation, "If a severe error prevents the successful
completion of a transaction, SQL Server automatically rolls back the
transaction and frees all resources held by the transaction." What is the
definition of a severe error? I always assumed that if network problems were
preventing the SQL Server from talking to the application server, the
transaction in the stored procedure would be rolled back, even without a
rollback command.

I have not use SQL Profiler yet to troubleshoot this problem (I don't have
access to the production machine to do this), so I don't know if the COMMIT
was executed or not. I will see if I can get this in place.

Relevant Pages

  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... delayed allocation, starting a new transaction could to happen a lot to ...
    (Linux-Kernel)
  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... Find the client and either commit or roll it back. ... If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... How would you like to assert for inode being freed? ...
    (Linux-Kernel)