Re: SqlTransactions and Sql timeout errors
From: Mary Chipman (mchip_at_online.microsoft.com)
Date: 11/03/04
- Next message: Dermot O'Loughlin: "Re: System.Data.OleDb.OleDbException '\\landfill\e\payload\db' is not a valid path"
- Previous message: yonggangwang: "Re: Data access and vb class file from .net novise"
- In reply to: Adam: "Re: SqlTransactions and Sql timeout errors"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 03 Nov 2004 08:57:04 -0500
Basically the way transactions work in SQL Server is that the first
step is that the server acquires the necessary locks so that it can
commit the transaction. In committing the transaction, the server
guarantees that it will pass the ACID test (atomicity, consistency,
isolation and durability). If it fails any one of these, then the
transaction will be rolled back in its entirety, preserving data
integrity. If a transaction times out, it usually means that the
server has failed to acquire the necessary locks. Since the server
cannot guarantee any of ACID properties in a timeout, the entire
transaction gets rolled back. Anything that causes an DML operation to
fail will cause a rollback of a transaction. One thing to bear in mind
is that this alone is not considered to be a runtime error by the
server. For example, if an Update statement fails in a batch, then SQL
Server moves on to the next statement in the batch. You need to catch
this by checking @@Rowcount as well as @@error. In an explicit
transaction, you then issue either a commit or a rollback based on
your findings. I personally prefer coding explicit transactions in a
stored procedure, rather than in client code, where all of the
procedural and DML code is executing directly on the server. You then
just pass back the success/failure messages in output parameters.
--Mary
On 1 Nov 2004 04:34:02 -0800, adam.pridmore@threex.co.uk (Adam) wrote:
>Thanks.
>
>Just another quick questions, but is there a reason why sql timeout
>exceptions cause it to rollback automatically? Is it to do with the
>way MS SQL server handles timeout errors as apposed to other runtime
>SQL errors?
>
>Adam
>
>
>Mary Chipman <mchip@online.microsoft.com> wrote in message news:<9ruao0l9foa203qovu0nkfu5h30k7vknk7@4ax.com>...
>> You'll need to wrap your Rollback in its own try/catch block since
>> you'll just be triggering a runtime error if you try to roll it back
>> and it's not active. One way to keep track of what's going on is to
>> create a variable that gets set to different values in your various
>> Catch blocks. You can see an example in the Rollback help topic in the
>> Help file -- instead of console.writeline shown here, just write to
>> your variable:
>> ms-help://MS.VSCC.2003/MS.MSDNQTR.2004APR.1033/cpref/html/frlrfSystemDataSqlClientSqlTransactionClassRollbackTopic.htm
>>
>> --Mary
>>
>>
- Next message: Dermot O'Loughlin: "Re: System.Data.OleDb.OleDbException '\\landfill\e\payload\db' is not a valid path"
- Previous message: yonggangwang: "Re: Data access and vb class file from .net novise"
- In reply to: Adam: "Re: SqlTransactions and Sql timeout errors"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
