Transaction

Question

Post reply

Transaction

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Dana Medley SSCertifiable Points: 6764 More actions · Answer 1

Great question. Finally redeemed myself after missing a couple this week. Great way to start a Friday morning. 😀

Everything is awesome!

EL Jerry SSCertifiable Points: 7061 More actions · Answer 2

Good question. Interesting that running the code one line at a time inserts one row and you get an error when executing COMMIT (No corresponding transaction exists). The error terminates the whole batch when run as a block.

Have an excellent weekend everyone!!

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

Revenant SSC-Forever Points: 42467 More actions · Answer 3

Koen Verbeeck (6/29/2012)
Ron's series on transactions led me to believe it should be 2. 🙂
Got it wrong, but definately learned something. Good question!
(3 wrong in a row, this is not a good week)

+1, except that I got only two wrong this week...

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 4

thanks for the question. cheers

in case anyone is interested, here is another link on batches that explains things going on here:

http://msdn.microsoft.com/en-us/library/ms175502(v=sql.105).aspx

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

(Bob Brown) (6/29/2012)
I go this wrong because I answered 1. But that was because if you execute the select statement again by itself, you will get one row returned. The 'a' value is stored in the test table. So I'm not sure what this proves.

Actually, check that again. I think you will find that the column name is 'a'. no rows are returned if you run the select after the error.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

nice question

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ken Hiatt SSC-Addicted Points: 469 More actions · Answer 7

Apparently it's a good thing my knowledge is limited and I didn't put too much thought into it, I got it right.

I figured if any part of a transaction failed, the whole transaction would fail and roll back. Isn't that the reason for starting a transaction in the first place?

bayazidahmed Old Hand Points: 364 More actions · Answer 8

Not all types of error rollback the batch transaction.

Try this out:

--create table

create table test(a int not null primary key)

--execute following statements

begin tran

insert into test values (1)

insert into test values (2)

commit

--error message

/*

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 7

Violation of PRIMARY KEY constraint 'PK__test__3BD0198E595B4002'. Cannot insert duplicate key in object 'dbo.test'. The duplicate key value is (1).

The statement has been terminated.

*/

--execute

select * from test

You'll get two rows. Only the second insert rolled back, not the full batch.

neprosto posted a list that mentions what type of rollback happens for different types of errors.

CarlaAbanes Right there with Babe Points: 782 More actions · Answer 9

i got this wrong, i read all your post but am not still sure i got it.

Cheers! 🙂
[/url]

CarlaAbanes Right there with Babe Points: 782 More actions · Answer 10

ok i think i got it now :), it's because the whole thing is executed as a transaction and not as a statement. so the whole transaction was rolled back. 🙂

Cheers! 🙂
[/url]

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 11

At first glance I thought, hang on there is one row returned with the value 'a' but when I out put the results to text you can see

a

-----------

(0 row(s) affected)

Gppd question.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

miguel angel mejia -471128 Newbie Points: 9 More actions · Answer 12

if you don't have begin tran, the transaction execute.

Ken SSCoach Points: 17191 More actions · Answer 13

Nice question on transaction behavior. Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 14

SQL Server error handling is bizarre.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 15

Great question! Thought it would be 2. But there is a explicit transaction beginning there.

I just didn't know it would all be rolled back on error. Does the XACT_ABORT setting affect anything?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA