Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Transactions 4 Expand / Collapse
Author
Message
Posted Thursday, June 7, 2012 10:06 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:36 PM
Points: 5,601, Visits: 25,005
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
Post #1312873
Posted Thursday, June 7, 2012 10:07 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 5:52 PM
Points: 2,270, Visits: 3,789
Thanks for the question. I am glad I got it right

Have a happy weekend.


Mohammed Moinudheen
Post #1312874
Posted Friday, June 8, 2012 1:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:07 AM
Points: 1,632, Visits: 5,587
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!
Post #1312910
Posted Friday, June 8, 2012 1:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 2,934, Visits: 2,959
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
www.seavus.com
Post #1312925
Posted Friday, June 8, 2012 1:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 4:51 AM
Points: 1,854, Visits: 3,451
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.
Post #1312927
Posted Friday, June 8, 2012 1:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:13 AM
Points: 3,237, Visits: 1,267
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.
Post #1312928
Posted Friday, June 8, 2012 1:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:18 AM
Points: 3,925, Visits: 5,112
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”
Post #1312929
Posted Friday, June 8, 2012 2:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 5,330, Visits: 9,777
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!
Post #1312931
Posted Friday, June 8, 2012 2:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 2,934, Visits: 2,959
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
www.seavus.com
Post #1312936
Posted Friday, June 8, 2012 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 5,330, Visits: 9,777
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
Post #1312937
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse