SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions 4


Transactions 4

Author
Message
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7797 Visits: 25280
Comments posted to this topic are about the item Transactions 4

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
M&M
M&M
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3533 Visits: 3906
Thanks for the question. I am glad I got it right :-)

Have a happy weekend.

M&M
paul.knibbs
paul.knibbs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2523 Visits: 6232
Looking at the answers, I guess a lot of people think IGNORE_DUP_KEY means it'll allow duplicates to be inserted--which, to be fair, actually makes sense from a strict English point of view!
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5878 Visits: 5080
paul.knibbs (6/8/2012)
Looking at the answers, I guess a lot of people think IGNORE_DUP_KEY means it'll allow duplicates to be inserted--which, to be fair, actually makes sense from a strict English point of view!


Nice question!
Yes, I agree. That option makes commands violating the constraint skipped.

Regards
IgorMi

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2435 Visits: 3575
Thanks for the question.

The result of the final SELECT statement would have been the same even if you had dropped the IGNORE_DUP_KEY=ON. The only difference is that it would have raised an error (Cannot insert duplicate key row...), but that wouldn't have aborted the transaction since XACT_ABORT is OFF (by default).

So in my opinion the explanation is a little bit wrong. It is not the IGNORE_DUP_KEY that causes three rows to be returned, but the fact that a run time error does not cause the transaction (some do, but not a duplicate key error) to rollback as long as XACT_ABORT is OFF.

Reference:
http://msdn.microsoft.com/en-us/library/ms188792.aspx
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

Mighty
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4409 Visits: 1697
I would like to point out the fact that the select statement would have returned the same results, even if the WITH (IGNORE_DUP_KEY = ON) would not have been specified with the creation of the index.

This is correctly described in the explanation, but some people might overlook the fact, that in this case, only the third INSERT statement fails but not is being rolled back, so not everything in between the BEGIN TRANSACTION...COMMIT TRANSACTION.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8839 Visits: 7281
Interesting question, Ron. thanks
Have never used the IGNORE_DUP_KEY option before, so learned something new.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14063 Visits: 15950
I got this right, but I'm still a little confused. Am I correct in thinking that what is being tested here is knowledge of the IGNORE_DUP_KEY setting and not anything to do with transactions? Since IGNORE_DUP_KEY means that there is no error, it doesn't matter whether the statements are wrapped in a transaction or not. Furthermore, as we saw in previous questions in this series, the transaction would commit regardless of a unique key violation, unless XACT_ABORT is set to OFF, which is not the default.

John

Edit: didn't mean to parrot what was written in the previous few posts - they weren't there when I started writing this one!
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5878 Visits: 5080
Nils Gustav Stråbø (6/8/2012)
Thanks for the question.

The result of the final SELECT statement would have been the same even if you had dropped the IGNORE_DUP_KEY=ON. The only difference is that it would have raised an error (Cannot insert duplicate key row...), but that wouldn't have aborted the transaction since XACT_ABORT is OFF (by default).

So in my opinion the explanation is a little bit wrong. It is not the IGNORE_DUP_KEY that causes three rows to be returned, but the fact that a run time error does not cause the transaction (some do, but not a duplicate key error) to rollback as long as XACT_ABORT is OFF.

Reference:
http://msdn.microsoft.com/en-us/library/ms188792.aspx
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.



I don't agree with you.
Ensured with a direct try.

You can execute the following code and ensure yourself that the IGNORE_DUP_KEY = ON has done its effect.

create table qotd5
(
col1 int,
col2 char(1) not null,
col3 varchar(20))

set xact_abort on
Begin transaction
insert into qotd5(col1,col2,col3) values(1,'w','some')
insert into qotd5(col1,col2,col3) values(2,'y','or that')
insert into qotd5(col1,col2,col3) values(1,'x','thing')
insert into qotd5(col1,col2,col3) values(3,'z','or what')
Commit transaction
select col2 from qotd5 order by col2

Regards
IgorMi

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14063 Visits: 15950
IgorMi (6/8/2012)

I don't agree with you.
Ensured with a direct try.

You can execute the following code and ensure yourself that the IGNORE_DUP_KEY = ON has done its effect.

create table qotd5
(
col1 int,
col2 char(1) not null,
col3 varchar(20))

set xact_abort on
Begin transaction
insert into qotd5(col1,col2,col3) values(1,'w','some')
insert into qotd5(col1,col2,col3) values(2,'y','or that')
insert into qotd5(col1,col2,col3) values(1,'x','thing')
insert into qotd5(col1,col2,col3) values(3,'z','or what')
Commit transaction
select col2 from qotd5 order by col2

Regards
IgorMi


Yes, but XACT_ABORT is not ON by default. Since it wasn't explicitly set in the question, we have to assume it's OFF.

John
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