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


Transactions 4


Transactions 4

Author
Message
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10649 Visits: 5158
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
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3759 Visits: 3575
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.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36048 Visits: 16700
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
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3759 Visits: 3575
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)
Mighty
Mighty
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5017 Visits: 1752
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.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36048 Visits: 16700
Ah yes, good point. I think that would have been a better way to put the question.

John
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10649 Visits: 5158
John Mitchell-245523 (6/8/2012)
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


Hi
I missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.

IgorMi

Igor Micev,
My blog: www.igormicev.com
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10649 Visits: 5158
John Mitchell-245523 (6/8/2012)
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


Hi
I missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.

IgorMi

Igor Micev,
My blog: www.igormicev.com
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3759 Visits: 3575
IgorMi (6/8/2012)Hi
I missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.

IgorMi
Let's end the discussion once and for all :-)

The original question with ignore_dup_key=on:
create table qotd4(col1 int not null, col2 char(1) not null, col3 varchar(20))
create unique index q04_index on qotd4(col1) with (ignore_dup_key=on)
go
begin tran
insert into qotd4(col1,col2,col3) values(1,'W','Some')
insert into qotd4(col1,col2,col3) values(2,'Y','Some')
insert into qotd4(col1,col2,col3) values(1,'X','Some')
insert into qotd4(col1,col2,col3) values(3,'Z','Some')
commit tran
go

select col2 from qotd4 order by col2
go
drop table qotd4

Returns three rows: W,Y,Z

The same statements, but this time with ignore_dup_key=off:
create table qotd4(col1 int not null, col2 char(1) not null, col3 varchar(20))
create unique index q04_index on qotd4(col1) with (ignore_dup_key=off)
go
begin tran
insert into qotd4(col1,col2,col3) values(1,'W','Some')
insert into qotd4(col1,col2,col3) values(2,'Y','Some')
insert into qotd4(col1,col2,col3) values(1,'X','Some')
insert into qotd4(col1,col2,col3) values(3,'Z','Some')
commit tran
go

select col2 from qotd4 order by col2
go
drop table qotd4

Returns three rows: W,Y,Z
In addition, SQL server throws an exception, but this does not affect tha fact that three rows are returned.
Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object 'dbo.qotd4' with unique index 'q04_index'.


Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10649 Visits: 5158
Nils Gustav Stråbø (6/8/2012)
IgorMi (6/8/2012)Hi
I missed it intentionally. I just tried with IGNORE_DUP_KEY=OFF and the result is same.

IgorMi
Let's end the discussion once and for all :-)

The original question with ignore_dup_key=on:
create table qotd4(col1 int not null, col2 char(1) not null, col3 varchar(20))
create unique index q04_index on qotd4(col1) with (ignore_dup_key=on)
go
begin tran
insert into qotd4(col1,col2,col3) values(1,'W','Some')
insert into qotd4(col1,col2,col3) values(2,'Y','Some')
insert into qotd4(col1,col2,col3) values(1,'X','Some')
insert into qotd4(col1,col2,col3) values(3,'Z','Some')
commit tran
go

select col2 from qotd4 order by col2
go
drop table qotd4

Returns three rows: W,Y,Z

The same statements, but this time with ignore_dup_key=off:
create table qotd4(col1 int not null, col2 char(1) not null, col3 varchar(20))
create unique index q04_index on qotd4(col1) with (ignore_dup_key=off)
go
begin tran
insert into qotd4(col1,col2,col3) values(1,'W','Some')
insert into qotd4(col1,col2,col3) values(2,'Y','Some')
insert into qotd4(col1,col2,col3) values(1,'X','Some')
insert into qotd4(col1,col2,col3) values(3,'Z','Some')
commit tran
go

select col2 from qotd4 order by col2
go
drop table qotd4

Returns three rows: W,Y,Z
In addition, SQL server throws an exception, but this does not affect tha fact that three rows are returned.
Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object 'dbo.qotd4' with unique index 'q04_index'.




Hi,

It is not possible the same result to be returned for IGNORE_DUP_KEY = ON and OFF. Are you missing something?

When IGNORE_DUP_KEY = ON three rows are returned
When IGNORE_DUP_KEY = OFF four rows are returned

Here is my code for the both cases

--1
create table qotd4
(
col1 int,
col2 char(1) not null,
col3 varchar(20))
create unique index q4_index on qotd4(col1) with (ignore_dup_key = on)
Begin transaction
insert into qotd4(col1,col2,col3) values(1,'w','some')
insert into qotd4(col1,col2,col3) values(2,'y','or that')
insert into qotd4(col1,col2,col3) values(1,'x','thing')
insert into qotd4(col1,col2,col3) values(3,'z','or what')
Commit transaction
select col2 from qotd4 order by col2

--2
create table qotd5
(
col1 int,
col2 char(1) not null,
col3 varchar(20))
create unique index q5_index on qotd4(col1) with (ignore_dup_key = off)
delete from qotd5

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

The ouput 1:
w
y
z
The ouput 2:
w
x
y
z

Regards
IgorMi

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