BEFORE Triggers: transactions

Showing posts with label transactions. Show all posts

Tuesday, March 20, 2012

Best Practices - SQL Transactions

I have a web app (ASP) that does all updates, inserts by calling
transaction-supported COM+ components (with the transaction started in the
ASP page, i.e. transaction=required) that use ADO to call stored procedures
(that usually involve single tables). If there is any error (missing SP,
parameter value of wrong type, etc.) with the database insert/update, MTS
automatically rolls back everything that was done in the database (and maybe
SQL Server does that with any error in an SP anyway'). As such, when I
write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
So, I have many SPs (that do not return any indication of success, or not)
like
INSERT INTO Table
(ColumnA, ColumnB)
VALUES
(ValueA, ValueB)
WHERE
Some condition
As a matter of best practice, should SQL programmers always enclose INSERTs,
UPDATEs within transactional statements in a production database? Should one
always check for errors with INSERTS or UPDATES? Or, with errors like I
describe (but not with business logic), does SQL Server automatically
rollback everything in an SP? Or, should one just save those statements for
when the SQL script and logic itself takes care of rolling back a database
when a series of updates or inserts are made?
Thanks for any thoughts."Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES?
No,

>Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP?
No, but the client will get an error message and rollback.
Usually, stored procedures should not contain transactional logic. Let the
client take care of it.
David|||Don Miller wrote:
> As a matter of best practice, should SQL programmers always enclose INSERT
s,
> UPDATEs within transactional statements in a production database? Should o
ne
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements fo
r
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
My thoughts are:
1) Transactions are only required if there's more than one DML
statement (or SELECT statement that needs to maintain a lock).
2) Error checking should be done after any statement that can fail.
This includes all DML and DDL statements. Pretty much everything except
SELECTs (though I guess they could technically fail as well...).
Kris|||David wrote:
> Usually, stored procedures should not contain transactional logic. > Let the clie
nt take care of it.
Are you sure? Isn't it better to ensure your stored procedures are
transactionally correct regardless of where they are executed from?
Kris|||To my knowledge, an error is not sufficient for MTS to roll back: if you do
not call ObjectContext.SetAbort explicitely, MTS will think that the whole
operation has been successfull and will commit it; even if there have been
one or multiple errors.
In the same way, if there is an error inside a SP, SQL-Server will not
rollback the transaction automatically for you: you must check for any error
(@.@.error) and call the rollback operation yourself.
Even if the SP is already enrolled in a transaction, you still need to check
for a transaction error (@.@.error) if there is such a possibility inside the
SP and this, even if you have not included a BEGIN TRAN inside it.
To be clear, transactions and errors are not same: the fact that there have
been an error doesn't mean that the transaction will be or need to be
aborted.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
> procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
> maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs,
> UPDATEs within transactional statements in a production database? Should
> one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
> for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>|||Don Miller (nospam@.nospam.com) writes:
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
> INSERTs, UPDATEs within transactional statements in a production
> database? Should one always check for errors with INSERTS or UPDATES?
> Or, with errors like I describe (but not with business logic), does SQL
> Server automatically rollback everything in an SP? Or, should one just
> save those statements for when the SQL script and logic itself takes
> care of rolling back a database when a series of updates or inserts are
> made?
If it's a single statement, there is no reason to have BEGIN/COMMIT
TRANSACTION around it, since the statement is a transaction in itself.
However, if you procedure performs several INSERT/UPDATE/DELETE statements
there should be a transaction around it. The procedure should not rely on
that the caller has set up a transaction. Sometimes you have a procedure
that you know is only performing part of a game. In this case, it is a
good habit to have this in the beginning:
IF @.@.trancount = 0
BEGIN
RAISERROR ('This procedure must be called with an active transaction',
16, 1)
RETURN 1
END
In SQL 2005, error checking in stored procedures can be handled with
TRY-CATCH. In SQL 2000, you need to check @.@.error, and if you have
started a transaction, you should rollback, since you know that you
were not able to fulfil your contract.
I have two articles on error handling in SQL Server on my web site.
http://www.sommarskog.se/error-handling-II.html gives more suggestions
on implementing error handling.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
The exception is when SET XACT_ABORT ON is active on the connection or proc.
SQL Server will then rollback the transaction and abort the batch when
runtime errors are encountered. However, compile errors are not affected
with XACT_ABORT ON so @.@.ERROR still needs to be checked when you want to
safeguard against all errors.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uUuZ5PNbGHA.1536@.TK2MSFTNGP02.phx.gbl...
> To my knowledge, an error is not sufficient for MTS to roll back: if you
> do not call ObjectContext.SetAbort explicitely, MTS will think that the
> whole operation has been successfull and will commit it; even if there
> have been one or multiple errors.
> In the same way, if there is an error inside a SP, SQL-Server will not
> rollback the transaction automatically for you: you must check for any
> error (@.@.error) and call the rollback operation yourself.
> Even if the SP is already enrolled in a transaction, you still need to
> check for a transaction error (@.@.error) if there is such a possibility
> inside the SP and this, even if you have not included a BEGIN TRAN inside
> it.
>
> To be clear, transactions and errors are not same: the fact that there
> have been an error doesn't mean that the transaction will be or need to be
> aborted.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
>|||<kriskirk@.hotmail.com> wrote in message
news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
> David wrote:
> Are you sure? Isn't it better to ensure your stored procedures are
> transactionally correct regardless of where they are executed from?
>
Ideally yes. Stored procedures should be atomic, consistent, and isolated.
They should typically not be durable because that makes assumptions about
where the procedure fits inside user transactions. But it requires quite a
bit of transaction handling code to make that happen.
Here's an example. A stored procedure should almost never issue a ROLLBACK
except to a savepoint. If it does then it can't be called in the scope of
an existing transaction. That might be OK for administrative stuff that you
know will be run from Management Studio, but for regular database
transactions.
create procedure foo
as
begin
begin transaction foo
begin try
'do work here
commit transaction
end try
begin catch
rollback transaction foo
commit transaction
exec usp_reraise_error
end catch
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:e4G3seSbGHA.4116@.TK2MSFTNGP05.phx.gbl...
> <kriskirk@.hotmail.com> wrote in message
> news:1146449658.432935.118620@.j73g2000cwa.googlegroups.com...
>
> Ideally yes. Stored procedures should be atomic, consistent, and
> isolated. They should typically not be durable because that makes
> assumptions about where the procedure fits inside user transactions. But
> it requires quite a bit of transaction handling code to make that happen.
> Here's an example. A stored procedure should almost never issue a
> ROLLBACK except to a savepoint. If it does then it can't be called in the
> scope of an existing transaction. That might be OK for administrative
> stuff that you know will be run from Management Studio, but for regular
> database transactions.
>
Oops, here's a correction after morinng coffee.
create procedure foo
as
begin transaction
save transaction proc_scope
begin try
--DO WORK HERE
commit transaction
end try
begin catch
rollback transaction proc_scope
commit transaction
declare @.errormessage nvarchar(4000),
@.errorseverity int
select
@.errormessage = error_message(),
@.errorseverity = error_severity()
raiserror(@.errormessage, @.errorseverity, 1)
end catch
In SQL 2005 you can just cut and paste all this junk around your procedure,
and you don't have to pollute the implementation with a bunch of error
handling noise.
So there is a right way to do transaction handling in a stored proceudre,
and it isn't all that hard, but transacaction handling is basically the
responsibility of the client code.
David|||Thanks to all who have responded, although I'm still not quite sure whether
I should (or need to) revisit about 100 SPs I have (that are called from a
client transaction through COM+ - and yes, the client does the
ObjectContext.SetAbort duties). It does work today with rollbacks as
necessary and expected but I felt lazy by relying on ASP to start the
transaction and have the MTS blackbox take care of the details especially
when dealing with SQL Server. But I guess that's a feature ;)
And thanks to Erland Sommarskog for the very thoughtful piece about error
handling.
"Don Miller" <nospam@.nospam.com> wrote in message
news:eKR64AMbGHA.4144@.TK2MSFTNGP04.phx.gbl...
> I have a web app (ASP) that does all updates, inserts by calling
> transaction-supported COM+ components (with the transaction started in the
> ASP page, i.e. transaction=required) that use ADO to call stored
procedures
> (that usually involve single tables). If there is any error (missing SP,
> parameter value of wrong type, etc.) with the database insert/update, MTS
> automatically rolls back everything that was done in the database (and
maybe
> SQL Server does that with any error in an SP anyway'). As such, when I
> write the SPs, I have not been including BEGIN TRAN, COMMIT TRAN, or
> checking for a transaction error (@.@.error) and then doing a ROLLBACK TRAN.
> So, I have many SPs (that do not return any indication of success, or not)
> like
> INSERT INTO Table
> (ColumnA, ColumnB)
> VALUES
> (ValueA, ValueB)
> WHERE
> Some condition
> As a matter of best practice, should SQL programmers always enclose
INSERTs,
> UPDATEs within transactional statements in a production database? Should
one
> always check for errors with INSERTS or UPDATES? Or, with errors like I
> describe (but not with business logic), does SQL Server automatically
> rollback everything in an SP? Or, should one just save those statements
for
> when the SQL script and logic itself takes care of rolling back a database
> when a series of updates or inserts are made?
> Thanks for any thoughts.
>

Friday, February 24, 2012

beseeching wisdom

hey all,
goal: Automate my invoices
given:
My Transactions Table
Customer1, Cut Lawn, $20, Invoice#
Customer2, Cut Lawn, $20, Invoice#
Other tables
Invoice Header
Invoice Detail
I guess what I need to do is assign invoice numbers to each of my
transactions in my transactions table. Next, somehow i need to get this data
into my invoice header and details. what's the best way to do this?
thanks,
ariCyptic narratives are hardly useful for others to understand you problem.
Please post your table structures and sample data along with expected
results. Refer to: www.aspfaq.com/5006 for details.
Anith|||ok, sorry about that. i going to try and break this up into smaller
digestable parts and i think you've already helpled me with one of my posts
today. So thank you very much.
"Anith Sen" wrote:

> Cyptic narratives are hardly useful for others to understand you problem.
> Please post your table structures and sample data along with expected
> results. Refer to: www.aspfaq.com/5006 for details.
> --
> Anith
>
>

Sunday, February 12, 2012

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.
KristinaKristinaDBA@.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:
> > 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:
> > 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|||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.googlegroups.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:
>> > 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
>|||<KristinaDBA@.gmail.com> wrote in message
news:1165334426.713996.85810@.79g2000cws.googlegroups.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.googlegroups.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- 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.googlegroups.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:
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.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- Hide quoted text -- Show quoted text -
>|||Kalen,
I did read your reply. Can you give me a transact sql example of a
statement that won't roll back - like you mentioned a constraint
violation. I am not aware of this and would be happy to learn..
Thanks.
On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> 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 MVPhttp://sqlblog.com
> <Kristina...@.gmail.com> wrote in messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.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:
> >> <Kristina...@.gmail.com> wrote in
> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.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- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -|||"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|||I apologize. I should really finish my coffee in the morning before become
insistent upon anything!
I was thinking of multiple statements where one failed but the rest
succeeded. But that of course would always have to have BEGIN TRAN.
One single statement will have a partial success. It will be all or nothing.
That being said, adding the BEGIN TRAN does not really add any extra
overhead, UNLESS you somehow forget to COMMIT or ROLLBACK and your
transaction ends up staying open for longer than necessary. Then there are
all kinds of ramifications.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<KristinaDBA@.gmail.com> wrote in message
news:1165339226.543180.164360@.f1g2000cwa.googlegroups.com...
> Kalen,
> I did read your reply. Can you give me a transact sql example of a
> statement that won't roll back - like you mentioned a constraint
> violation. I am not aware of this and would be happy to learn..
> Thanks.
> On Dec 5, 11:46 am, "Kalen Delaney" <replies@.public_newsgroups.com>
> wrote:
>> 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 MVPhttp://sqlblog.com
>> <Kristina...@.gmail.com> wrote in
>> messagenews:1165336494.705787.285740@.l12g2000cwl.googlegroups.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:
>> >> <Kristina...@.gmail.com> wrote in
>> >> messagenews:1165334426.713996.85810@.79g2000cws.googlegroups.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- Hide quoted text -- Show quoted text -- Hide quoted text --
>> >> Show quoted text -
>|||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 did a quick benchmark:
CREATE TABLE aa(i INT)
go
CREATE PROCEDURE Test1
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
END
END
go
CREATE PROCEDURE Test2
AS
BEGIN
SET NOCOUNT ON
DECLARE @.i INT
SET @.i = 0
WHILE(@.i < 10000) BEGIN
BEGIN TRANSACTION
INSERT aa(i) VALUES(@.i)
SET @.i = @.i + 1
IF @.@.ERROR<>0 BEGIN
ROLLBACK
END ELSE BEGIN
COMMIT
END
END
END
go
Test1
go
Test1
/*
Profiler results:
CPU:406
Reads: 10720
Writes: 104
Duration: 2406
*/
go
Test2
go
Test2
/*
Profiler results:
CPU:373
Reads: 10844
Writes: 116
Duration: 2406
*/
go
DROP PROCEDURE Test1
go
DROP PROCEDURE Test2
go
DROP TABLE aa
go
I ran them 3 times and I did not notice any significant differences in
neither of 4 counters.
I do not know anything about your environment, but I would not worry
about performance penalties of BEGIN TRAN in mine.
--
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thanks Alex,
That definitely clarifies the issue!
Also, Kalen - glad you are not *mad* any more...
This is a great group, I am going to stick with it. Today was my first
day checking it out :).
Kristina.
Alex Kuznetsov wrote:
> 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 did a quick benchmark:
> CREATE TABLE aa(i INT)
> go
> CREATE PROCEDURE Test1
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> END
> END
> go
> CREATE PROCEDURE Test2
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.i INT
> SET @.i = 0
> WHILE(@.i < 10000) BEGIN
> BEGIN TRANSACTION
> INSERT aa(i) VALUES(@.i)
> SET @.i = @.i + 1
> IF @.@.ERROR<>0 BEGIN
> ROLLBACK
> END ELSE BEGIN
> COMMIT
> END
> END
> END
> go
> Test1
> go
> Test1
> /*
> Profiler results:
> CPU:406
> Reads: 10720
> Writes: 104
> Duration: 2406
> */
> go
> Test2
> go
> Test2
> /*
> Profiler results:
> CPU:373
> Reads: 10844
> Writes: 116
> Duration: 2406
> */
> go
> DROP PROCEDURE Test1
> go
> DROP PROCEDURE Test2
> go
> DROP TABLE aa
> go
> I ran them 3 times and I did not notice any significant differences in
> neither of 4 counters.
> I do not know anything about your environment, but I would not worry
> about performance penalties of BEGIN TRAN in mine.
> --
> Alex Kuznetsov
> http://sqlserver-tips.blogspot.com/
> http://sqlserver-puzzles.blogspot.com/

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but don
t
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but dont
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you

BEGIN TRAN increments @@TRANCOUNT to 2

I have a problem with the below code that seems to open 2 transactions(why
not just one) - what am I doing wrong?
Regards,
Janusz
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)Hi,
The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
was opened for Begin Tran.
If you run the following code you will find the same, I am not sure what is
your requirement here.
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
COMMIT --This is for the the imlicit transaction on
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
o/p
After commiting trans. Opened trans::1
After commiting trans. Opened trans::0
Vishal Khajuria
SUNGARD SCT INDIA
"rejki" wrote:

> I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
>|||See SET IMPLICIT_TRANSACTIONS in BOL.
It has a perfect example showing the variation in @.@.TRANCOUNT
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"rejki" <rejki@.discussions.microsoft.com> wrote in message
news:DE9B1D00-8D58-4F10-9614-866EC64BF1E5@.microsoft.com...
>I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' +
> convert(varchar,@.@.TRANCOUNT)
>|||Thanks for reply,
If you add print statement after "SET IMPLICIT_TRANSACTIONS ON" you will see
that it does not open transaction. @.@.TRANCOUNT goes to 2 after "BEGIN TRAN"
statement.
The problem I am having is that I do only one commit, but when I execute my
SQL script (similar to one I posted) SQL Analyzer thinks that I have still
uncommitted transaction and when I try to exit SQL Analyzer it asks me
whether I want to close open transaction.
I could add one more “commit” but I would prefer to understand what is g
oing
on there.
Regards,
Janusz
"Vishal Khajuria" wrote:
> Hi,
> The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
> was opened for Begin Tran.
> If you run the following code you will find the same, I am not sure what i
s
> your requirement here.
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> COMMIT --This is for the the imlicit transaction on
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> o/p
> After commiting trans. Opened trans::1
> After commiting trans. Opened trans::0
>
> Vishal Khajuria
> SUNGARD SCT INDIA
>
> "rejki" wrote:
>|||Hi,
What happens with IMPLICIT_TRANSACTIONS is this:
If there's already a transaction in progress (say at a higher scope in a cal
ling stored procedure or some such), nothing else happens; you simply join t
hat transaction.
If, however, there's no current transaction in progress, then executing any
DML/DDL statement will start a new transaction. In this case, all you need t
o do is COMMIT or ROLLBACK. A better approach even than this is to use XACT_
ABORT, which will ensure that any failures below 21 rollback the current tra
nsaction scope. That way, you can raise a suitable error instead of just rol
ling back (or worse, writing extra code to rollback and raise an error!).
The following demonstrates...
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
SELECT|||Sorry, here's the complete demonstration
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
UPDATE table1
SET field1 = NULL
WHERE field2 = 'some value'
IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)
BEGIN
RAISERROR (N'Failed to update fact table.', 16, 1)
END
-- if we got this far then we're good!
COMMIT TRANSACTION
GO|||One other thing, by way of explanation about the following
bit of SQL:

IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)

This is testing some condition to see if the operation was
successful. You may not care about the outcome, in which case you
can just COMMIT, but given that you're in a transaction in the
first place, I'd guess you're going to want to test some sort
of condition to see if this really worked out, before deciding
whether or not to commit your changes.
Cheers,
Tim

Tuesday, March 20, 2012

Best Practices - SQL Transactions

Friday, February 24, 2012

beseeching wisdom

Sunday, February 12, 2012

begin transactions

begin transactions

begin transactions

begin transaction in a sp or not

begin transaction in a sp or not

BEGIN TRAN increments @@TRANCOUNT to 2

BEFORE Triggers

Blog Archive

About Me