Friday, February 24, 2012

ADO.net or TSQL Transactions

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
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
> If you have complex explicit transactions, your best bet is going to
> be to implement them in your stored procedure(s) in T-SQL both in
> terms of performance and simplicity. Implementing them in client code
> may cause more round trips and not be as performant. Call a single
> stored procedure that executes the transactions and returns
> success/failure information in output parameters. See the BEGIN
> TRANSACTION and related topics in SQL Books Online for more
> information.
>
> --Mary
>
> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> <sh856531@.microsofts_free_email_service.com> wrote:
>
>>Hi everyone,
>>
>>Can anyone tell me, if I use and ADO transaction object to execute say 10
>>stored procedures, and the stored procedures are themselve quite quite
>>long
>>and multistaged, do I need to use transaction statements inside the
>>individual procedures to avoid potential concurrency issues, or am I
>>protected from this by virtue of the ADO transaction object.
>>
>>The reason I ask is, it could be the case that the ADO.net transaction
>>simply ensures that the stored procedures operate in an all or nothing
>>manner. This may mean that within a complicated multi staged stored
>>procedure information could become corrupted because the relevent multi
>>staged code *inside* the procedure isn't transacted.
>>
>>I hope that make sense. My query pertains to SQL Server but I'm guessing
>>the
>>same would be true of any db that supports transactions and SProcs.
>>
>>Thanks
>>
>>Simon
>>
>Simon Harvey wrote:
> 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
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
>> If you have complex explicit transactions, your best bet is going to
>> be to implement them in your stored procedure(s) in T-SQL both in
>> terms of performance and simplicity. Implementing them in client code
>> may cause more round trips and not be as performant. Call a single
>> stored procedure that executes the transactions and returns
>> success/failure information in output parameters. See the BEGIN
>> TRANSACTION and related topics in SQL Books Online for more
>> information.
>> --Mary
>> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
>> <sh856531@.microsofts_free_email_service.com> wrote:
>> Hi everyone,
>> Can anyone tell me, if I use and ADO transaction object to execute
>> say 10 stored procedures, and the stored procedures are themselve
>> quite quite long
>> and multistaged, do I need to use transaction statements inside the
>> individual procedures to avoid potential concurrency issues, or am I
>> protected from this by virtue of the ADO transaction object.
>> The reason I ask is, it could be the case that the ADO.net
>> transaction simply ensures that the stored procedures operate in an
>> all or nothing manner. This may mean that within a complicated
>> multi staged stored procedure information could become corrupted
>> because the relevent multi staged code *inside* the procedure isn't
>> transacted. I hope that make sense. My query pertains to SQL Server
>> but I'm
>> guessing the
>> same would be true of any db that supports transactions and SProcs.
>> 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.
--
David Gugick
Imceda Software
www.imceda.com|||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.
Andrew J. Kelly SQL MVP
"Simon Harvey" <sh856531@.microsofts_free_email_service.com> wrote in message
news:eOl8GlkzEHA.1204@.TK2MSFTNGP10.phx.gbl...
> 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
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
>> If you have complex explicit transactions, your best bet is going to
>> be to implement them in your stored procedure(s) in T-SQL both in
>> terms of performance and simplicity. Implementing them in client code
>> may cause more round trips and not be as performant. Call a single
>> stored procedure that executes the transactions and returns
>> success/failure information in output parameters. See the BEGIN
>> TRANSACTION and related topics in SQL Books Online for more
>> information.
>> --Mary
>> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
>> <sh856531@.microsofts_free_email_service.com> wrote:
>>Hi everyone,
>>Can anyone tell me, if I use and ADO transaction object to execute say 10
>>stored procedures, and the stored procedures are themselve quite quite
>>long
>>and multistaged, do I need to use transaction statements inside the
>>individual procedures to avoid potential concurrency issues, or am I
>>protected from this by virtue of the ADO transaction object.
>>The reason I ask is, it could be the case that the ADO.net transaction
>>simply ensures that the stored procedures operate in an all or nothing
>>manner. This may mean that within a complicated multi staged stored
>>procedure information could become corrupted because the relevent multi
>>staged code *inside* the procedure isn't transacted.
>>I hope that make sense. My query pertains to SQL Server but I'm guessing
>>the
>>same would be true of any db that supports transactions and SProcs.
>>Thanks
>>Simon
>>
>|||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" <davidg-nospam@.imceda.com> wrote in message
news:ut0iiRlzEHA.824@.TK2MSFTNGP11.phx.gbl...
> Simon Harvey wrote:
> > 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
> >
> > "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> > news:8egpp052lm2jkkuv2e5dik0k1h4pvgncc4@.4ax.com...
> >> If you have complex explicit transactions, your best bet is going to
> >> be to implement them in your stored procedure(s) in T-SQL both in
> >> terms of performance and simplicity. Implementing them in client code
> >> may cause more round trips and not be as performant. Call a single
> >> stored procedure that executes the transactions and returns
> >> success/failure information in output parameters. See the BEGIN
> >> TRANSACTION and related topics in SQL Books Online for more
> >> information.
> >>
> >> --Mary
> >>
> >> On Thu, 18 Nov 2004 09:48:47 -0000, "Simon Harvey"
> >> <sh856531@.microsofts_free_email_service.com> wrote:
> >>
> >> Hi everyone,
> >>
> >> Can anyone tell me, if I use and ADO transaction object to execute
> >> say 10 stored procedures, and the stored procedures are themselve
> >> quite quite long
> >> and multistaged, do I need to use transaction statements inside the
> >> individual procedures to avoid potential concurrency issues, or am I
> >> protected from this by virtue of the ADO transaction object.
> >>
> >> The reason I ask is, it could be the case that the ADO.net
> >> transaction simply ensures that the stored procedures operate in an
> >> all or nothing manner. This may mean that within a complicated
> >> multi staged stored procedure information could become corrupted
> >> because the relevent multi staged code *inside* the procedure isn't
> >> transacted. I hope that make sense. My query pertains to SQL Server
> >> but I'm
> >> guessing the
> >> same would be true of any db that supports transactions and SProcs.
> >>
> >> 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.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> 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 :-)
David Gugick
Imceda Software
www.imceda.com|||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" <davidg-nospam@.imceda.com> wrote in message
news:u4zdwPmzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> Shawn B. wrote:
> > 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 :-)
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Shawn B. wrote:
> 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.
--
David Gugick
Imceda Software
www.imceda.com|||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|||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.
--
Andrew J. Kelly SQL MVP
"Simon" <sh856531@.microsofts_free_email_service.com> wrote in message
news:%23GAkzXyzEHA.3336@.TK2MSFTNGP11.phx.gbl...
> 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 wrote:
> 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.
--
David Gugick
Imceda Software
www.imceda.com|||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.
--
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23a4gWk0zEHA.2012@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
>> 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.
> --
> David Gugick
> Imceda Software
> www.imceda.com

No comments:

Post a Comment