Post reply

Transactions 4

  • June 7, 2012 at 10:06 pm

    #270828

    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[/url]
    Before posting a performance problem please read[/url]

  • June 7, 2012 at 10:07 pm

    #1498486

    Thanks for the question. I am glad I got it right 🙂

    Have a happy weekend.

    M&M

  • June 8, 2012 at 1:10 am

    #1498519

    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!

  • June 8, 2012 at 1:45 am

    #1498532

    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,My blog: www.igormicev.com

  • June 8, 2012 at 1:52 am

    #1498534

    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.

  • June 8, 2012 at 1:53 am

    #1498535

    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.

  • June 8, 2012 at 1:53 am

    This was removed by the editor as SPAM

  • June 8, 2012 at 2:00 am

    #1498538

    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!

  • June 8, 2012 at 2:04 am

    #1498543

    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,My blog: www.igormicev.com

  • June 8, 2012 at 2:07 am

    #1498544

    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

  • June 8, 2012 at 2:11 am

    #1498546

    John Mitchell-245523 (6/8/2012)

    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

    Try with XACT_ABORT = OFF and you'll see the same results as it is ON. In this case XACT_ABORT does not have impact of the inserted rows. However on the messages, yes it has.

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • June 8, 2012 at 2:12 am

    #1498547

    IgorMi, you added SET XACT_ABORT ON, and if you read my answer you'll see that I have explained about this difference. XACT_ABORT was not set in the question, and I assume it therefore will be OFF.

  • June 8, 2012 at 2:16 am

    #1498548

    IgorMi (6/8/2012)

    Try with XACT_ABORT = OFF and you'll see the same results as it is ON. In this case XACT_ABORT does not have impact of the inserted rows. However on the messages, yes it has.

    IgorMi

    True again, but you did not create a unique constraint, so there is nothing to fail. Therefore four rows are returned whether the setting is ON or OFF.

    John

  • June 8, 2012 at 2:16 am

    #1498549

    IgorMi, you have forgotten to add the unique index in your customized example.

    And yes, I have tried it. There is a huge difference between XACT_ABORT ON or OFF, and that is 0 rows versurs 3 rows (when you have the unique index in place)

  • June 8, 2012 at 2:24 am

    #1498552

    John Mitchell-245523 (6/8/2012)

    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!

    John, the difference is visible when you try to insert all those rows in one transaction like

    INSERT INTO [QOTD4] ([col1], [col2], [col3])

    SELECT 1, 'w', 'something'

    UNION ALL

    SELECT 2, 'x', 'or other'

    UNION ALL

    SELECT 1, 'y', 'thing'

    UNION ALL

    SELECT 3, 'z', 'or what'

    With IGNORE_DUP_KEY = OFF the result would be that there are 0 records inserted. When IGNORE_DUP_KEY = ON it results in 3 records being inserted.

Viewing 15 posts - 1 through 15 (of 36 total)

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