begin transactions

Hi I am a DBA and am having a dispute with a developer. He insists on
coding like this:
Begin train
1 insert statment
Check for error
Commit or rollback.
Since SQL Server 2000 an d 2005 has implicit transactions, my standard
is to NOT put them in unless they are needed and at least 2 statements
occur (insert, update, delete). I am assuming that these extra begin
trans affect performance and the log some how. Can anyone help me with
this. Again, this is the case when there is 1 update, delete, or
insert.
Thanks in advance.
Kristina
KristinaDBA@.gmail.com wrote:
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
> Thanks in advance.
> Kristina
>
I'd have to side with your developer on this one... You're "assuming"
that SQL will take care of the error handling for you. He's
GUARANTEEING that the errors will be handled in an expected fashion.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Thanks for the advice, but I am not sure you are understanding my
question. You can check for an error without a transaction like this:
Insert table (firld a, field b)
Select a, b ....
IF @.@.error <> 0
do somthing.
The point it this, the insert will AUTOMATICALLY roll back as it is
only one statement. The question is, does another begin tran put extra
overhead on performance. The regular insert will roll back if it fails
because by default, SQL server does implicit transactions unlike
oracle. etc..
make sense?
Tracy McKibben wrote:
> KristinaDBA@.gmail.com wrote:
> I'd have to side with your developer on this one... You're "assuming"
> that SQL will take care of the error handling for you. He's
> GUARANTEEING that the errors will be handled in an expected fashion.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||KristinaDBA@.gmail.com wrote:
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
>
I understood your question perfectly. Explicitly issuing a BEGIN TRAN
doesn't add any overhead to an implicit transaction. My point was that
your developer is guaranteeing the behavior of his code. His code will
also be easier to understand to someone new to SQL, who may not know or
fully understand implicit transactions. I'd compare this to arguing
over commenting your code - good comments make for good code. In this
case, good flow control makes for good code.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||No, if you have single statement autocommit transactions they are COMMITTED
when the single statement is finished, so they cannot be then rolled back.
They are automatically COMMITTED, not AUTOMATICALLY ROLLED BACK.
There are errors like constraint violations that will not cause rollbacks.
The only way to force a rollback for an error that doesn't automatically
roll back is to check the error before the commit occurs, and that means you
have to turn the transaction into an explicit transaction, using BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165335197.517839.101070@.16g2000cwy.googlegro ups.com...
> Thanks for the advice, but I am not sure you are understanding my
> question. You can check for an error without a transaction like this:
> Insert table (firld a, field b)
> Select a, b ....
> IF @.@.error <> 0
> do somthing.
> The point it this, the insert will AUTOMATICALLY roll back as it is
> only one statement. The question is, does another begin tran put extra
> overhead on performance. The regular insert will roll back if it fails
> because by default, SQL server does implicit transactions unlike
> oracle. etc..
> make sense?
> Tracy McKibben wrote:
>
|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegrou ps.com...
> Hi I am a DBA and am having a dispute with a developer. He insists on
> coding like this:
> Begin train
> 1 insert statment
> Check for error
> Commit or rollback.
> Since SQL Server 2000 an d 2005 has implicit transactions, my standard
> is to NOT put them in unless they are needed and at least 2 statements
> occur (insert, update, delete). I am assuming that these extra begin
> trans affect performance and the log some how. Can anyone help me with
> this. Again, this is the case when there is 1 update, delete, or
> insert.
>
For a single DML statement, there is no need, but no real cost, to wrapping
it in an explicit transaction. It might generate an extra log record or
two, but nothing to worry about.
But, what if you want to enlist this procedure in a larger transaction? I
am generally against explicit transaction handling in stored procedures.
While it's sometimes necessary and beneficial It's usually the wrong scope
to knit together transactions and decide their fate. You only really want
one level of transaction handling, either in the outermost stored procedure
or in the client code.
David
|||Kalen,
I misspoke. What I mean to say is if there was an error, they would be
automically rolled back. - no need for begin trans. In the case of no
errors with a single statment, they would be automatically committed.
On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
m...@.hotmail.com> wrote:
> <Kristina...@.gmail.com> wrote in messagenews:1165334426.713996.85810@.79g2000cws.goo glegroups.com...
>
>
>
>
>
> it in an explicit transaction. It might generate an extra log record or
> two, but nothing to worry about.
> But, what if you want to enlist this procedure in a larger transaction? I
> am generally against explicit transaction handling in stored procedures.
> While it's sometimes necessary and beneficial It's usually the wrong scope
> to knit together transactions and decide their fate. You only really want
> one level of transaction handling, either in the outermost stored procedure
> or in the client code.
> David- Hide quoted text -- Show quoted text -
|||I understand that. Did you even read my reply?
I said that there are some errors that do NOT automatically rollback, that
will commit even with the error. So the only way to roll them back is for
YOU or your code to catch them before the commit, and the only way to do
that is to have BEGIN TRAN.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165336494.705787.285740@.l12g2000cwl.googlegr oups.com...
> Kalen,
> I misspoke. What I mean to say is if there was an error, they would be
> automically rolled back. - no need for begin trans. In the case of no
> errors with a single statment, they would be automatically committed.
> On Dec 5, 11:25 am, "David Browne" <davidbaxterbrowne no potted
> m...@.hotmail.com> wrote:
>
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:#RM4bzIGHHA.2456@.TK2MSFTNGP06.phx.gbl...
>I understand that. Did you even read my reply?
> I said that there are some errors that do NOT automatically rollback, that
> will commit even with the error. So the only way to roll them back is for
> YOU or your code to catch them before the commit, and the only way to do
> that is to have BEGIN TRAN.
>
I think the confusion is around the way single DML statements are handled.
A constraint violation will not roll back a transaction, but it will "roll
back" any changes made by the the statement in which the violation occurs,
since single DML are always atomic.
David