ADO.net or TSQL Transactions

[Simon Harvey]

Hi all

Should implement a transaction in both the stored procedure AND in ADO.net
code or is doing it in one or the other good enough to protect against
concurrency and atomicity problems?

Thanks

Simon

 

[David Gugick]

Hi all

Should implement a transaction in both the stored procedure AND in
ADO.net code or is doing it in one or the other good enough to
protect against concurrency and atomicity problems?

Thanks

Simon

If you're talking about SQL running from a client as opposed to running
a single stored procedure, I think you have to go the SP route (in most
cases). Security aside, imagine running 20 SQL commands from a client on
the server in succession: each one requires a full round-trip to the
server which can slow things down. OTOH, a single SP is a single call.
While the slower client-side transaction runs, it holds locks on the
server, which in turn causes other transactions to wait on locked
resources which slows everyone down.

Plus, if you have to make an implementation change, you don't have to
deal with recompiling the app and distributing it to everyone.

 

[Andrew J. Kelly]

A transaction has to be ATOMIC regardless of who starts it otherwise what
good is it. So two transactions are not better than one in this case. If
the outer one is Rolled back then ALL the nested ones are too. Sometimes it
makes sense to have trans in sp's so that if you call the sp by itself
everything inside it is all or nothing. But if you begin a tran from
outside a sp, everything from there on will be wrapped in that same tran.

 

[Shawn B.]

Except that sometimes you need to do something and then do more things in
source code that depend on that something and so on. Unless you move your
entire application into the SP, you'll have no choice but to wrap the
transaction into an ADO.NET call. Apart from the fact that (in our case) it
may not be feasible to rewrite 2.5 million lines of code to accomodate SP
only transactions, not to mention that some of the older parts of the code
(that are being rewritten into .NET) use embedded SQL...

You'll just have to evaluate your application architecture if it already
exists, or decide how you will be accessing the data at all times in your
application of you are currently designing it. There's a time and place for
everything. But know this, if you begin a transaction in the SP and for
some reason need to execute source code that may depending on something from
a currently running transaction, and it attempts to start a transaction and
call an SP that starts its own transaction, you'll get an exception. So you
either do it one way or another but not both, unless you want to excercise
your patients and stamina.


Thanks,
Shawn

 

[David Gugick]

Except that sometimes you need to do something and then do more
things in source code that depend on that something and so on.
Unless you move your entire application into the SP, you'll have no
choice but to wrap the transaction into an ADO.NET call. Apart from
the fact that (in our case) it may not be feasible to rewrite 2.5
million lines of code to accomodate SP only transactions, not to
mention that some of the older parts of the code (that are being
rewritten into .NET) use embedded SQL...

You'll just have to evaluate your application architecture if it
already exists, or decide how you will be accessing the data at all
times in your application of you are currently designing it. There's
a time and place for everything. But know this, if you begin a
transaction in the SP and for some reason need to execute source code
that may depending on something from a currently running transaction,
and it attempts to start a transaction and call an SP that starts its
own transaction, you'll get an exception. So you either do it one
way or another but not both, unless you want to excercise your
patients and stamina.


Thanks,
Shawn

I thought the OP asked a simple question: Which is better to use in an
application SPs or embedeed SQL. The answer to _that_ simple question is
stored procedures. But that's not the question he asked. He asked
whether the transaction should be started in the app or on in the SP. I
think Andrew answered that question. But I stand by answer to a question
that was never asked

 

[Shawn B.]

I think the relevance, while not as direct as previous answers, is that "it
depends on how your code and workflow is organized". I'm not dissagreeing
with the "simple" answer, that you use transactions in the SP. However,
I've rarely encountered a business application that was designed in such a
way that all transactions were at the SP level. I was only offering a
difference perspective, a difference way of looking at things, another thing
to take into consideration. Nothing more.


Thanks,
Shawn

 

[David Gugick]

I think the relevance, while not as direct as previous answers, is
that "it depends on how your code and workflow is organized". I'm
not dissagreeing with the "simple" answer, that you use transactions
in the SP. However, I've rarely encountered a business application
that was designed in such a way that all transactions were at the SP
level. I was only offering a difference perspective, a difference
way of looking at things, another thing to take into consideration.
Nothing more.


Thanks,
Shawn

I agree with you Shawn. Unless you have strict standards (which are not
bad to have), most applications will have some embedded SQL, even if
using SPs is the standard. Although, I'm not sure if you have an SP
standard for an app that ending up with a mixed code base is good from a
maintenance and security standpoint. It's nice to know no one can access
your database, except through stored procs.

 

[Simon]

Hi all,

Thanks for your answers so far.

I'm still not sure about something. Are ADO.net transactions and TSQL
transactions essentially equivelent?

If I do a big stored procedure, locked through ADO.net, will all the the
rows be locked until the SP returns?

I'm worried that if I use ADO, the stored procedure still might cause a
concurrency problem. This is essentially my problem.

Thanks again

Simon

 

[Andrew J. Kelly]

Simon,

An ADO transaction (.net or otherwise) is nothing more than passing a BEGIN
TRAN to SQL Server. So it's always a SQL Server transaction and any rows
locked after the first Begin Tran is issued (regardless of where or by who)
on that connection will remain locked until the final commit (if nested) or
the first Rollback.

 

[David Gugick]

Simon,

An ADO transaction (.net or otherwise) is nothing more than passing a
BEGIN TRAN to SQL Server. So it's always a SQL Server transaction
and any rows locked after the first Begin Tran is issued (regardless
of where or by who) on that connection will remain locked until the
final commit (if nested) or the first Rollback.

I would add that if concurrency is a concern, letting SQL Server start
and end the transaction from within the SP will be faster than doing so
from .net because it will eliminate 2 additional round-trips to the
server.

 

[Andrew J. Kelly]

I agree, you always want to keep the transactions as short as possable. I
was just trying to get the point across that there really is no such thing
as an ADO tran, it is really SQL Server that is managing it.