SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Introduction to Transactions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/26/2004 :  09:58:39  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
This article covers the basic of transactions. It describes them and gives examples of committing and rolling back transactions. It also shows how to properly trap errors inside a stored procedure using transactions.

Article Link.

VyasKN
SQL Server MVP & SQLTeam MVY

United Kingdom
313 Posts

Posted - 01/28/2004 :  05:40:17  Show Profile  Visit VyasKN's Homepage  Reply with Quote
It will be a good idea to introduce readers to @@TRANCOUNT. It is very handy when dealing with transactions.

--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 01/28/2004 :  06:29:42  Show Profile  Visit Stoad's Homepage  Reply with Quote
begin tran
select * from t where 0=1
dbcc opentran
select @@trancount
commit tran
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4138 Posts

Posted - 01/28/2004 :  10:26:17  Show Profile  Visit graz's Homepage  Reply with Quote
Please guys, leave me something for part 2

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

United Kingdom
313 Posts

Posted - 01/28/2004 :  10:28:34  Show Profile  Visit VyasKN's Homepage  Reply with Quote
Oh sequel ;-)
I only brought it up, because, as far as I know, not many developers make use @@TRANCOUNT.
quote:
Originally posted by graz

Please guys, leave me something for part 2


--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 01/29/2004 :  06:42:20  Show Profile  Visit Stoad's Homepage  Reply with Quote
btw, I meant that in my "code" above "dbcc opentran" shows this:

Server: Msg 7969, Level 16, State 1, Line 3
No active open transactions.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 01/29/2004 :  09:05:27  Show Profile  Reply with Quote
It would have helped you, if you had included a database updating command in your code.....
A SELECT doesn't contribute to a TRANSACTION...it's not logged!

Change your code to be an UPDATE/INSERT/DELETE statement...your DBCC statement will then report some info.
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 01/30/2004 :  04:14:21  Show Profile  Visit Stoad's Homepage  Reply with Quote
lol, Andrew, I am quite aware of this fact.
But you should agree "all this" is not obvious.
Go to Top of Page

thisisfutile
Starting Member

USA
3 Posts

Posted - 02/26/2006 :  13:52:34  Show Profile  Reply with Quote
I have a question...

The multi-statement example of this article shows two commands (Update and Update) in the TRAN and the corresponding explanation says:

If the statement fails after the first update, neither update statement will be applied when SQL Server is restarted. The log file will contain a BEGIN TRAN but no corresponding COMMIT TRAN.

(I assume this means the updates aren't applied to the database....allow me to continue in my confusion)

Then, further down the page the example for Create Proc has two commands (Insert and Update) and the first one fails on a primary key violation. However, the Update completes successfuly and is commited.

My confusion is that both TRANs have two commands. The 1st example says if either one fails the TRAN isn't committed. The 2nd example says the TRAN will commit the second command even though the first command fails. Is this because the TRAN in the 2nd example is part of a procedure? More directly, if the 1st example was put into the Create Proc like the 2nd example and the first update of that TRAN fails, will the second update NOW succeed and be commited?
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4138 Posts

Posted - 02/26/2006 :  18:42:32  Show Profile  Visit graz's Homepage  Reply with Quote
Interesting point. I've updated the article. Now it says if the system fails after the first update ... (or something close to that).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

havij
Starting Member

3 Posts

Posted - 07/07/2009 :  01:59:00  Show Profile  Reply with Quote
Hi,
Why select * from <invalid table name> does not work in a transaction?

Cheers,
Havij
Go to Top of Page

MRD_Whistler
Starting Member

USA
1 Posts

Posted - 07/14/2009 :  18:03:27  Show Profile  Reply with Quote
havij asked: Why select * from <invalid table name> does not work in a transaction?

Havij, I don't think it's a matter of it not working, but more a simple question of them applying to transactions.

Updates, Deletes, and Inserts are WRITE operations, i.e. they change data. Therefore, if something goes wrong, each change needs to be logged and rolled back.

However, a READ, like a Select, doesn't change anything, it leaves the data just as it found it. Therefore, there's nothing to roll back if things break. Therefore, it'll probably work just fine; it just doesn't really fall into the category of something to worry about protecting with a transaction.

Personally, unless you absolutely need a certain value at a precise moment during the transaction (i.e. a WRITE operation depends on it), I'd place your selects either fully before or fully after the transaction's code. It just simplifies things.
Go to Top of Page

havij
Starting Member

3 Posts

Posted - 07/14/2009 :  19:42:51  Show Profile  Reply with Quote
Hi MRD_Whistler,

Perhaps I wasn’t clear enough - the select statement is part of a larger SP that also has UPDATE statements which need to rollback if an error occurs like the one given. My understanding was that an error that occurs within the boundary of a transaction can be caught by the following code

SELECT @ErrorCode = @@Error
IF @ErrorCode <> 0
ROLLBACK TRANSACTION

But in our case, when the error occurs, the entire SP fails at that line exhibiting the error and execution halts. The transaction count is left at 1.

Thanks in advance for your help.
Havij


quote:
Originally posted by MRD_Whistler

havij asked: Why select * from <invalid table name> does not work in a transaction?

Havij, I don't think it's a matter of it not working, but more a simple question of them applying to transactions.

Updates, Deletes, and Inserts are WRITE operations, i.e. they change data. Therefore, if something goes wrong, each change needs to be logged and rolled back.

However, a READ, like a Select, doesn't change anything, it leaves the data just as it found it. Therefore, there's nothing to roll back if things break. Therefore, it'll probably work just fine; it just doesn't really fall into the category of something to worry about protecting with a transaction.

Personally, unless you absolutely need a certain value at a precise moment during the transaction (i.e. a WRITE operation depends on it), I'd place your selects either fully before or fully after the transaction's code. It just simplifies things.


Go to Top of Page

jessiefun
Starting Member

35 Posts

Posted - 10/09/2010 :  06:10:18  Show Profile  Reply with Quote
quote:
Originally posted by graz

Interesting point. I've updated the article. Now it says if the system fails after the first update ... (or something close to that).

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.


My question is: What's the main difference between the System fails and the statement fails?

Looking foward to your answer.
Thanks,
Jessie
Go to Top of Page

jessiefun
Starting Member

35 Posts

Posted - 10/15/2010 :  06:53:04  Show Profile  Reply with Quote
Any one can give some answers?

Thanks,
Jessie
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000