Unable to reproduce stored procedure error.

[Former Member]

I have a stored procedure running as part of a batch process, and have an issue where every once in a great while (maybe 2 or three months for a procedure that runs every day) it will throw an error to "Primary key for table x not unique". But when I attempt to re run it in ISQL to see where the issue is, it DOES NOT throw the error..

Has anyone run into anything like this?

[thomas_duemesnil]

Don't advertise here.

[MarkCulp]

The "Primary key for table x is not unique" error is raised when an attempt is made to insert (or update) a row that has a primary key value equal to a value that is already in the table. There are several reasons why this can happen but most of the the time this is caused by an application logic error that is not generating unique primary key values.

For example, if the application generates the primary key value using this algorithm

set @pk = ( select 1+max( pk ) from T );
insert into T( pk, ... ) values( @pk, ... );
commit;

then it can fail when two copies of the application (or two threads/transactions in the app) runs concurrently. The solution that is often suggested is to use DEFAULT AUTOINCREMENT on your PK columns (if column is numeric).

Without more context - e.g. source of your procedure - it is difficult to determine the exact reason why you are seeing the error.

HTH

[Breck_Carter]

> Has anyone run into anything like this?

Yes, many many times.

In addition to what Mark said: please show us the PRIMARY KEY part of the CREATE TABLE for the table in question.

If the primary key is based on a timestamp, it is possible that two successively-generated timestamp values may be identical on rare occasion, making the bug difficult to reproduce.

If the primary key column is declared as DEFAULT CURRENT TIMESTAMP, the default_timestamp_increment option is not helpful since it applies to DEFAULT TIMESTAMP columns which you don't want to use as primary keys... in other words, avoid timestamps as primary keys... you can still have an index on the timestamp column, just add a DEFAULT AUTOINCREMENT integer as the primary key.

[Former Member]

I think you're onto it. The primary key is as follows:

PRIMARY KEY ( "act_nbr" ASC, "machine_id" ASC, "eff_dtm" ASC )

The field eff_dtm defaults to current time stamp, so this would explain why when I run the procedure right away after getting the error, it finishes without throwing the error again.

Thanks!

[VolkerBarth]

So the "solution" might be to "slow down" the procedure with a WAITFOR DELAY '00:00:00.001'; or the like 🙂