Article: Introduction to Transactions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Introduction to Transactions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-26 : 09:58:39

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

313 Posts

Posted - 2004-01-28 : 05:40:17

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

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-28 : 06:29:42

begin tran
select * from t where 0=1
dbcc opentran
select @@trancount
commit tran

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-01-28 : 10:26:17

Please guys, leave me something for part 2

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

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2004-01-28 : 10:28:34

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

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-29 : 06:42:20

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.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-01-29 : 09:05:27

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.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-30 : 04:14:21

lol, Andrew, I am quite aware of this fact.
But you should agree "all this" is not obvious.

thisisfutile
Starting Member

3 Posts

Posted - 2006-02-26 : 13:52:34

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?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-02-26 : 18:42:32

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.

havij
Starting Member

3 Posts

Posted - 2009-07-07 : 01:59:00

Hi,
Why select * from <invalid table name> does not work in a transaction?

Cheers,
Havij

MRD_Whistler
Starting Member

1 Post

Posted - 2009-07-14 : 18:03:27

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.

havij
Starting Member

3 Posts

Posted - 2009-07-14 : 19:42:51

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.

jessiefun
Starting Member

35 Posts

Posted - 2010-10-09 : 06:10:18

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

jessiefun
Starting Member

35 Posts

Posted - 2010-10-15 : 06:53:04

Any one can give some answers?

Thanks,
Jessie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources