f***@gmail.com
2014-02-27 12:25:05 UTC
Its almost 10 years after, but with ASE 15.7 sp102 we ran into the same problem as posted by Eugene. As this post is not conclusive, I've slightly changed the above example to get a simple reproducible scenario.
We use two tables, rll_source which is will be the source table with 10 rows.
rll_test is going to be the table that is deleted and inserted in one transaction:
create table rll_test (
rowid int not null,
descr varchar(255) null,
primary key (rowid)
)
lock datarows
go
create table rll_source (
rowid int not null,
descr varchar(255) null,
primary key (rowid)
)
lock datarows
go
-- Populate the source table with 10 rows.
insert into rll_source
select number, 'this is row ' + convert(varchar(4), number)
from master..spt_values where type = 'P' and number between 1 and 10
order by number
go
Then save a sql script with a loop that deletes the entire rll_test table and repopulate it from rll_source in one transaction like this:
set flushmessage on
go
while 1=1
begin
begin transaction
delete rll_test
if @@error <> 0 break
insert rll_test
select * from rll_source
if @@error <> 0 break
commit
end
go
Run the script with isql in parallel in 2 sessions.
Both sessions are able to perform several loops (about 7000 and 500), but then one of the sessions breaks on the duplicate key row error:
...
(10 rows affected)
(10 rows affected)
(10 rows affected)
(0 rows affected)
Msg 2601, Level 14, State 6:
Server 'FLUXERT', Line 8:
Attempt to insert duplicate key row in object 'rll_test' with unique index 'rll_test_7040025082'
Command has been aborted.
With this example it seems very weird that this could happen.
isolation level in the example is read committed.
no dbcc traceflags are set on the server.
We use two tables, rll_source which is will be the source table with 10 rows.
rll_test is going to be the table that is deleted and inserted in one transaction:
create table rll_test (
rowid int not null,
descr varchar(255) null,
primary key (rowid)
)
lock datarows
go
create table rll_source (
rowid int not null,
descr varchar(255) null,
primary key (rowid)
)
lock datarows
go
-- Populate the source table with 10 rows.
insert into rll_source
select number, 'this is row ' + convert(varchar(4), number)
from master..spt_values where type = 'P' and number between 1 and 10
order by number
go
Then save a sql script with a loop that deletes the entire rll_test table and repopulate it from rll_source in one transaction like this:
set flushmessage on
go
while 1=1
begin
begin transaction
delete rll_test
if @@error <> 0 break
insert rll_test
select * from rll_source
if @@error <> 0 break
commit
end
go
Run the script with isql in parallel in 2 sessions.
Both sessions are able to perform several loops (about 7000 and 500), but then one of the sessions breaks on the duplicate key row error:
...
(10 rows affected)
(10 rows affected)
(10 rows affected)
(0 rows affected)
Msg 2601, Level 14, State 6:
Server 'FLUXERT', Line 8:
Attempt to insert duplicate key row in object 'rll_test' with unique index 'rll_test_7040025082'
Command has been aborted.
With this example it seems very weird that this could happen.
isolation level in the example is read committed.
no dbcc traceflags are set on the server.