Re: C# transactions v SQL transactions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nicholas Paldino [.NET/C# MVP] (mvp_at_spam.guard.caspershouse.com)
Date: 09/23/04 

Date: Thu, 23 Sep 2004 12:16:20 -0400

Eric,

    What I'm trying to say is that for any resource (DB included), if you
have the resource itself determine its transaction state, instead of a
transaction coordinator, it then becomes difficult to integrate that
resource into larger-scale transactions.

    Because of that, I would recommend against handling transactions in SP
code, and have an external transaction coordinator (Enterprise
Services/COM+) handle it for you. Because SQL Server is registered as
having a resource manager, it can allow ES/COM+ to handle the transaction
management for it.

    Ultimately, yes, ES/COM+ would call the resource manager for SQL server,
and issue the appropriate transaction commands, based on configuration. The
idea here isn't to lock those procedures into a pattern which would be
difficult to integrate other operations into.

    So, for SQL server, or any resource that supports transactions, I would
never write a stored procedure that has transaction management code in it. 

-- 
               - Nicholas Paldino [.NET/C# MVP]
               - mvp@spam.guard.caspershouse.com
"Eric Sabine" <mopar41@mail_after_hot_not_before.com> wrote in message 
news:Op1J8dYoEHA.1816@TK2MSFTNGP09.phx.gbl...
>I do not believe we are discussing the same thing.  I am strictly speaking 
>about SQL Server transactions and as you have indicated you are talking 
>about business layer "transactions."  If the requirement were passed to the 
>DBA who was writing the stored procedure that File IO needs to take place, 
>he should kick it back and say that is outside the scope of the sql server 
>transaction.
>
> SQL Server transactions are strictly for maintaining data integrity and 
> consistency, not to mention they give you a named place to restore to 
> instead of a datetime stamp. I believe you would benefit from not one, but 
> both, i.e., the data integrity transaction and the process integrity 
> transaction.
>
> Eric
>
>
>
> Nicholas Paldino [.NET/C# MVP] wrote:
>> Eric,
>>
>>    I would agree with you, transactions should not exist in the front
>> end (nor should dynamic SQL statements).
>>
>>    I am referring to the business layer.  It is at this level that the
>> transactions should be controlled, and not by the programmer, but
>> rather, through the administrative tool (which gets the initial
>> values from attributes declared on the class).
>>
>>    The reason for this is that it allows for easy extenisibility in
>> the future.  For example, say you have your transaction code in your
>> stored procedure now.  Say that you have some piece of code that
>> calls the stored procedure.  Later on, you have a requirement to
>> write to a file when the procedure completes, and to not write to the
>> file when it fails.  You would want some sort of transaction manager
>> to coordinate this.  Of course, this assumes that the file system is
>> transactional (which it is in Longhorn).
>>    The point is that when you start performing transaction management
>> in the resource that is being handled by the transacton, you severely
>> limit the other business processes that the resource can be included
>> in.  The file is a simple case, but what about message queues?  What
>> about both queues and emails?  As you add more resources to the
>> transaction state (outside of the database), you need a way to abort
>> them all.
>>    It is because of the fact that I am going to make my business
>> process transactional, and not just my database operations
>> transactional that I use something like Enterprise Services, or the
>> new Transaction model being introduced in .NET 2.0.  I believe that
>> the mindset of the db being the only transactional resource is on the
>> way of being well behind us, and that external entities are needed to
>> control all of these resources, should something fail while trying to
>> perform operations on them.
>>
>> "Eric Sabine" <mopar41@mail_after_hot_not_before.com> wrote in message
>> news:%23rG96$XoEHA.800@TK2MSFTNGP14.phx.gbl...
>>> Are you talking about database transactions or something else? Database 
>>> transactions, i.e, BEGIN TRAN, COMMIT TRAN, ROLLBACK TRAN,
>>> really should not be used in the front-end application.  You greatly
>>> increase your risk to locking, blocking, inconsistent data, corrupt
>>> data, long running transactions (which leads back to locking and
>>> blocking), uncommitted transactions, and more.  Transactions should
>>> be kept as short as possible to keep database access clean.  I take
>>> the totally opposite stance and say transactions and typed sql
>>> statements (even though you didn't mention these but they would go
>>> hand-in-hand with someone who was creating transactions in the FE)
>>> should never exist in the front-end.  I believe that the only access
>>> from a front-end should be limited to executing stored procedures.
>>>
>>> Eric
>>>
>>>
>>>
>>>
>>> Nicholas Paldino [.NET/C# MVP] wrote:
>>>> Mike,
>>>>
>>>>    Personally, I think that managing transaction state in your
>>>> stored procedures is a very, very bad idea.  It limits how they can
>>>> be used elsewhere, and for the most part, are harder to maintain
>>>> (as you have more and more sp's calling each other, trying to
>>>> maintain transaction state is a pain).
>>>>
>>>>    I would recommend using EnterpriseServices for handling
>>>> transaction state.  There are a number of reasons for this, the
>>>> best one being easy maintainability (you can turn transactions on
>>>> and off with the flick of a switch, or change their behavior). Another 
>>>> reason to handle this would be because it offers the
>>>> easiest translation path to Indigo.
>>>>    Hope this helps.
>>>>
>>>>
>>>> "Mike P" <mrp@telcoelectronics.co.uk> wrote in message
>>>> news:e4xx6NXoEHA.1776@TK2MSFTNGP14.phx.gbl...
>>>>> I've been using C# transactions for a while and had no problems
>>>>> with them.  Using try catch blocks I can trap basically all
>>>>> possible errors and rollback all necessary data.
>>>>>
>>>>> Over the last few days I've been trying to convert some of this
>>>>> code to SQL Server stored procedures, but it seems to lack many of
>>>>> the benefits of C# transactions - a lot of the errors don't seem
>>>>> to be trapped by the SQL error trapping (e.g. if I do an update on
>>>>> a row that doesn't exist, no rollback occurs and execution
>>>>> continues, if a table I am trying to access doesn't exist then the
>>>>> sproc crashes). Can anybody tell me what reasons there are for writing 
>>>>> your
>>>>> transactions in stored procedures rather than your .NET code, as
>>>>> there don't seem to be any to me.
>>>>>
>>>>>
>>>>> Any assistance would be really appreciated.
>>>>>
>>>>>
>>>>> Cheers,
>>>>>
>>>>> Mike
>>>>>
>>>>>
>>>>> *** Sent via Developersdex http://www.developersdex.com ***
>>>>> Don't just participate in USENET...get rewarded for it!
>
>

Relevant Pages

  • Re: Right use for JTA transactions?
    ... Having done an XAResource for a DBMS, I might be able to offer ... The resource does the proper processing when called from the container software. ... The resource initiates its transaction ... For JDBC, it would do executeXXX, etc. on statements using the Connection ...
    (comp.lang.java.programmer)
  • Re: A quota based lock
    ... resource for hours then no amount of lock implementation smartness will ... long access times and responsiveness at the same time. ... Doing preemption manually will be difficult. ... The long-running task may run a bit shower due to transaction commit ...
    (comp.lang.java.programmer)
  • Re: C# transactions v SQL transactions
    ... your transaction, process, assembly, etc, etc boundaries). ... This is a single processor machine, hosting SQL server, COM+ ... > when you take the database-transaction away from the database and you make ... >> you have the resource itself determine its transaction state, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: computational model of transactions
    ... Since each of them wants to update the very same resource (the same ... within each transaction can occur simultaneously, so serializing each entire ... about the race condition, whatever the logical model. ...
    (comp.databases.theory)
  • Re: transaction with unknown nbr of commands
    ... then call your wrapper stored procedure, passing it all of the user ... explicit transaction with a BEGIN TRAN statement, ... >When I run a sqlcommand in a transaction, ...
    (microsoft.public.dotnet.framework.adonet)