| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
United Kingdom
313 Posts |
 Posted - 01/28/2004 : 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 - 01/28/2004 : 06:29:42
|
begin tran
select * from t where 0=1
dbcc opentran
select @@trancount
commit tran |
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 01/28/2004 : 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
United Kingdom
313 Posts |
Posted - 01/28/2004 : 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 - 01/29/2004 : 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
Flowing Fount of Yak Knowledge
Ireland
2915 Posts |
Posted - 01/29/2004 : 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 - 01/30/2004 : 04:14:21
|
lol, Andrew, I am quite aware of this fact.
But you should agree "all this" is not obvious. |
|
|
|
thisisfutile
Starting Member
USA
3 Posts |
Posted - 02/26/2006 : 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
USA
4128 Posts |
Posted - 02/26/2006 : 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 - 07/07/2009 : 01:59:00
|
Hi,
Why select * from <invalid table name> does not work in a transaction?
Cheers,
Havij |
|
|
|
MRD_Whistler
Starting Member
USA
1 Posts |
Posted - 07/14/2009 : 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 - 07/14/2009 : 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 - 10/09/2010 : 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 - 10/15/2010 : 06:53:04
|
Any one can give some answers?
Thanks,
Jessie |
|
|
| |
Topic |
|
Article: Introduction to Transactions
