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 Friday, June 8, 2012 2:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 2,901, Visits: 2,927
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,
SQL Server developer at Seavus
www.seavus.com
Post #1312939
Posted Friday, June 8, 2012 2:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 1,839, Visits: 3,421
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.
Post #1312940
Posted Friday, June 8, 2012 2:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
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
Post #1312941
Posted Friday, June 8, 2012 2:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 1,839, Visits: 3,421
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)
Post #1312942
Posted Friday, June 8, 2012 2:24 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: Today @ 6:48 AM
Points: 3,201, Visits: 1,239
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.
Post #1312945
Posted Friday, June 8, 2012 2:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 5,233, Visits: 9,476
Ah yes, good point. I think that would have been a better way to put the question.

John
Post #1312949
Posted Friday, June 8, 2012 2:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 2,901, Visits: 2,927
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,
SQL Server developer at Seavus
www.seavus.com
Post #1312950
Posted Friday, June 8, 2012 2:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 2,901, Visits: 2,927
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,
SQL Server developer at Seavus
www.seavus.com
Post #1312951
Posted Friday, June 8, 2012 2:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 1,839, Visits: 3,421
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'.

Post #1312961
Posted Friday, June 8, 2012 3:27 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:35 AM
Points: 2,901, Visits: 2,927
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,
SQL Server developer at Seavus
www.seavus.com
Post #1312982
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse