Question about Identity generation and issues..

Has anyone run into this issue before?
I'm creating test scenarios doing Deletes/Updates/Inserts and after the test
scenario is completed need to remove any and all changes to the Database
that were made. The connection is made and many statements are run and all
are encapsulated in a single transaction. When all transactions are
completed a rollback is performed.
The problem arises here:
Table A has an identity column defined as the primary key on it.
A process (run in a .Net transaction) executes and performs an insert into
Table A to generate an Identity but before the transaction is completed the
process runs other statements using that generated value in other tables for
reference purposes. During the time these queries run, another instance of
the same process performs the same insert allowing the table to auto
generate its identity value. A certain percentage of the time, I'll get an
error whereby a duplicate primary key violation occurs.
Has anyone encountered anything like this and is there a way around it?
I've tried setting differing Isolation levels, minimizing the transaction to
only the section that is actually changing data, unique transaction
names...?
Thanks
DAre you using a column with IDENTITY property or are you generating the
sequencial value?. If you are generating it, can we see the code used?
AMB
"news" wrote:

>
> Has anyone run into this issue before?
>
> I'm creating test scenarios doing Deletes/Updates/Inserts and after the te
st
> scenario is completed need to remove any and all changes to the Database
> that were made. The connection is made and many statements are run and al
l
> are encapsulated in a single transaction. When all transactions are
> completed a rollback is performed.
>
> The problem arises here:
>
> Table A has an identity column defined as the primary key on it.
>
> A process (run in a .Net transaction) executes and performs an insert into
> Table A to generate an Identity but before the transaction is completed th
e
> process runs other statements using that generated value in other tables f
or
> reference purposes. During the time these queries run, another instance o
f
> the same process performs the same insert allowing the table to auto
> generate its identity value. A certain percentage of the time, I'll get a
n
> error whereby a duplicate primary key violation occurs.
>
> Has anyone encountered anything like this and is there a way around it?
> I've tried setting differing Isolation levels, minimizing the transaction
to
> only the section that is actually changing data, unique transaction
> names...?
>
> Thanks
>
> D
>
>|||The column has been defined as an INT IDENTITY(1,1) NOT NULL
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E07EDB1D-6F14-4F87-9D77-13E58115A473@.microsoft.com...
> Are you using a column with IDENTITY property or are you generating the
> sequencial value?. If you are generating it, can we see the code used?
>
> AMB
> "news" wrote:
>
test
all
into
the
for
of
an
transaction to|||Is there a way to reproduce the problem in our computer?
AMB
"news" wrote:

> The column has been defined as an INT IDENTITY(1,1) NOT NULL
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:E07EDB1D-6F14-4F87-9D77-13E58115A473@.microsoft.com...
> test
> all
> into
> the
> for
> of
> an
> transaction to
>
>|||>> The problem arises here: Table A has an identity column defined as
the primary key on it. <<
That is a problem! You have hired someone who does not know better
than use IDENTITY as a key in a schema. The right thing to do is
re-design that table with a proper relational key. This is a much
better idea than any of the kludges you are going to be given.|||Instead claiming that the sky is falling like senor Joe and give up identity
columns, there are a couple of more practical ideas. The most important
solution is to reproduce the problem. What version of SQL Server? How many
records in the database? How hard is the table being hit? What do the
inserts look like?
For example, one solution would be a simple retry mechanism (which btw, Joe,
would be needed with any sort of sequencing mechanism).
Thomas
"news" <DavidP> wrote in message
news:ugrawntOFHA.2144@.TK2MSFTNGP09.phx.gbl...
>
> Has anyone run into this issue before?
>
> I'm creating test scenarios doing Deletes/Updates/Inserts and after the
> test
> scenario is completed need to remove any and all changes to the Database
> that were made. The connection is made and many statements are run and
> all
> are encapsulated in a single transaction. When all transactions are
> completed a rollback is performed.
>
> The problem arises here:
>
> Table A has an identity column defined as the primary key on it.
>
> A process (run in a .Net transaction) executes and performs an insert into
> Table A to generate an Identity but before the transaction is completed
> the
> process runs other statements using that generated value in other tables
> for
> reference purposes. During the time these queries run, another instance
> of
> the same process performs the same insert allowing the table to auto
> generate its identity value. A certain percentage of the time, I'll get
> an
> error whereby a duplicate primary key violation occurs.
>
> Has anyone encountered anything like this and is there a way around it?
> I've tried setting differing Isolation levels, minimizing the transaction
> to
> only the section that is actually changing data, unique transaction
> names...?
>
> Thanks
>
> D
>|||In a schema where you have several branches to a snowflake describing say a
insurance distributor model, I needed a way to create a unique identifier
for each instance without having a three part key to carry to each relating
table.
The server is SQL Server 2000 with latest service packs and patches et al.
The table is being hit pretty hard as this data is being loaded to load up
the insurance distributor plans and such.
Currently the table has only in the neighborhood of 100039484 records
Inserts are simple inserts where there is one value set being put in i.e.:
INSERT INTO InsuranceDistributorPlan
(DistributorID, PlanPeriodId, PlanTypeID, Name, Source)
-- VALUES (-90000, -2004, 1, 'Davids test category', 4);
SELECT -90000, -2004, 1, 'Jeffs test category', 4
This creates and unique identifier for me to then relate other data items
to.
This type of query is being run from a .Net application (explicit
transactions don't appear to affect the issue).
Identities are reset when a rollback occurs so why would this issue arrise?
"Thomas" <thomas@.newsgroup.nospam> wrote in message
news:%23X7fSKyOFHA.1932@.tk2msftngp13.phx.gbl...
> Instead claiming that the sky is falling like senor Joe and give up
identity
> columns, there are a couple of more practical ideas. The most important
> solution is to reproduce the problem. What version of SQL Server? How many
> records in the database? How hard is the table being hit? What do the
> inserts look like?
> For example, one solution would be a simple retry mechanism (which btw,
Joe,
> would be needed with any sort of sequencing mechanism).
>
> Thomas
>
> "news" <DavidP> wrote in message
> news:ugrawntOFHA.2144@.TK2MSFTNGP09.phx.gbl...
into
transaction
>|||I presume that SQL has been patched to service pack 3a? What
indexes are on the table? Script them using the QA and post
them to them to group if you can. (Change the column names
and/or index names if you like)
There is a knowledge base article on an identity problem,
however it is old and has presumably been fixed in one of
the service packs. (http://tinyurl.com/46yme)
Thomas
"news" <DavidP> wrote in message
news:%23P$Hwh5OFHA.3512@.TK2MSFTNGP15.phx.gbl...
> In a schema where you have several branches to a snowflake
> describing say a
> insurance distributor model, I needed a way to create a
> unique identifier
> for each instance without having a three part key to carry
> to each relating
> table.
> The server is SQL Server 2000 with latest service packs
> and patches et al.
> The table is being hit pretty hard as this data is being
> loaded to load up
> the insurance distributor plans and such.
> Currently the table has only in the neighborhood of
> 100039484 records
> Inserts are simple inserts where there is one value set
> being put in i.e.:
> INSERT INTO InsuranceDistributorPlan
> (DistributorID, PlanPeriodId, PlanTypeID, Name, Source)
> -- VALUES (-90000, -2004, 1, 'Davids test category',
> 4);
> SELECT -90000, -2004, 1, 'Jeffs test category', 4
> This creates and unique identifier for me to then relate
> other data items
> to.
> This type of query is being run from a .Net application
> (explicit
> transactions don't appear to affect the issue).
> Identities are reset when a rollback occurs so why would
> this issue arrise?
> "Thomas" <thomas@.newsgroup.nospam> wrote in message
> news:%23X7fSKyOFHA.1932@.tk2msftngp13.phx.gbl...
> identity
> Joe,
> into
> transaction
>|||On Thu, 7 Apr 2005 10:36:42 -0700, news wrote:
(snip)
>Identities are reset when a rollback occurs
(snip)
Hi news,
While I must admit that I don't really understand the proble you
describe, I do know that this statement is incorrect. A rollback will
not reset the identity.
CREATE TABLE Test (Ident int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Descr varchar(60) NOT NULL)
go
INSERT Test (Descr)
VALUES ('First row')
go
BEGIN TRANSACTION
INSERT Test (Descr)
VALUES ('Second row - will disappear after rollback')
SELECT * FROM Test
ROLLBACK TRANSACTION
go
INSERT Test (Descr)
VALUES ('Third row, to prove that IDENTITY value 2 is not reused')
SELECT * FROM Test
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||sorry, that was a typo..
My statement was meant to say that
"Identities aren't reset"
Thanks,
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:am2b51t2qil494ud2uc4nsmrd7glqe0d4m@.
4ax.com...
> On Thu, 7 Apr 2005 10:36:42 -0700, news wrote:
> (snip)
> (snip)
> Hi news,
> While I must admit that I don't really understand the proble you
> describe, I do know that this statement is incorrect. A rollback will
> not reset the identity.
> CREATE TABLE Test (Ident int IDENTITY(1,1) NOT NULL PRIMARY KEY,
> Descr varchar(60) NOT NULL)
> go
> INSERT Test (Descr)
> VALUES ('First row')
> go
> BEGIN TRANSACTION
> INSERT Test (Descr)
> VALUES ('Second row - will disappear after rollback')
> SELECT * FROM Test
> ROLLBACK TRANSACTION
> go
> INSERT Test (Descr)
> VALUES ('Third row, to prove that IDENTITY value 2 is not reused')
> SELECT * FROM Test
> go
> DROP TABLE Test
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)