SQL Clone
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

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

Group: General Forum Members
Points: 110252 Visits: 13338
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
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9550 Visits: 4856
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 questionThere 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 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
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27156 Visits: 7545
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
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: 4969 Visits: 8184
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ø
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5367 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4580 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
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8137 Visits: 8826
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ø
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5367 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