Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions in T-SQL


Transactions in T-SQL

Author
Message
venus.pvr
venus.pvr
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 558
Comments posted to this topic are about the item Transactions in T-SQL
Koen Verbeeck
Koen Verbeeck
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24236 Visits: 13268
Lots of reading :-)
Thanks for the question.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4999 Visits: 4636
Koen Verbeeck (11/12/2012)
Lots of reading :-)
Thanks for the question.

Agree - good question though.

-------------------------------
Posting Data Etiquette - Jeff Moden
Smart way to ask a question

There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
Raghavendra Mudugal
Raghavendra Mudugal
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1892 Visits: 2958
awesome read. Smile

(there were some statements where I guess were added to create extra confusion... OR to make it more interesting... ;-) :rolleyesSmile

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7716 Visits: 7245
Interesting question, lots of reading.
thanks

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Toreador
Toreador
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2618 Visits: 8072
Rather complicated!
But easy to guess the right answer, as there had to be at least 2 rows in #temp (the 2 inserted outside the loop) which ruled out half the answers; and the loop was either going to insert 6 rows or none, which ruled out the other.
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 3575
Thanks for the question.
Got it wrong because I missed the last INSERT statement after the WHILE LOOP.

If the intention was to test the knowledge of nested transactions then the code was too complicated in my opinion.

A much more readable example that also tests the users knowledge of transactions could have been

create table #t1(id int)
insert into #t1 values(1)

begin tran t1
insert into #t1 values(2)

begin tran t2
insert into #t1 values(3)

begin tran t3
insert into #t1 values(3)

if @@trancount>0
rollback tran

if @@trancount>0
commit tran t2

if @@trancount>0
rollback tran t1

select * from #t1


Raghavendra Mudugal
Raghavendra Mudugal
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1892 Visits: 2958
Nils Gustav Stråbø (11/13/2012)
Thanks for the question.
Got it wrong because I missed the last INSERT statement after the WHILE LOOP.

If the intention was to test the knowledge of nested transactions then the code was too complicated in my opinion.

A much more readable example that also tests the users knowledge of transactions could have been

create table #t1(id int)
insert into #t1 values(1)

begin tran t1
insert into #t1 values(2)

begin tran t2
insert into #t1 values(3)

begin tran t3
insert into #t1 values(3)

if @@trancount>0
rollback tran

if @@trancount>0
commit tran t2

if @@trancount>0
rollback tran t1

select * from #t1



the point here is.. once you check the tran count and you roll back them - it rolls back all of them... so there is no point to check the transaction again and commit it, as it never going to be greater than 0 after rolled back, in your example you created the transaction each one manually and the same is achieved via while loop in the Qtod, and there were some statements which adds kind of interest to the analysis where the making a choice going to take a while.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
rodjkidd
rodjkidd
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3911 Visits: 8196
Godd Question.
I did a big oops though, missed the first insert before the loop, so my second count was always out. Must rememeber to drink cofee before QOD...

Rodders...



Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2315 Visits: 3575
Raghavendra, exactly. Keep it simple. That way you'll be able to test the reader's knowledge about transactions. No need to add a lot of extra noise.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search