Re: Error 35602 Key is not unique in collection

[jjlbmcnews]

Hi,

I have this problem with one of our applications and can't for the life of me work out what it is. Every now and again our application displays the above error, now the developer has gone through and discovered that when the application is retreiving the data from the stored procedure once in a while the SP returns duplicate rows, hence causing the application to crash. I'm not a big VB user so I don't understand the program side of things. I have ran several tests and commands on the database but can find nothing wrong. The developer seems to think that locking or something is not working correctly on the database. Has anyone ever seen anything like this before? Can anyone offer any thoughts/advice on this?

 

[SQLSister]

My first thought would be that the stored procedure is at fault.

If there are several paths the process could take depending on various conditions, it could be that one of those paths accidentally does the insert twice which could cause the application to tell you the key value already exists in a record. I would be there is a problem with the begin and end statements in his stored procedure and that it does not always do exactly what was intended. Because the other paths are correct, the sp works most of the time.

Another problem that could happen is if the stored procedure is pulling the last value for a key that you create manually through the stored procedure and using it to create the next value. If two users pull the last value before the first user finishes processing and adds another value, the second user's manually created key won't work because it already exists in the database. This is a problem inherent in manually creating unique values that is one of the reasons why this is usually a bad practice. In this case the problem isn't exactly a locking problem, it is a not locking problem. Data read in a select statement is not normally uses a shared lock, the data is accesible to other people. You may need to adjust the stored procedure to specifially create an exclusive lock on the row and hold the lock on the row until it is finished processing. Be aware that in doing this, you may cause the application to appear to slow down because the second user is held up until the first user finishes processing. Also this may slow down other users who are trying to query the table for other reasons, especially if you are doing frequent data inserts.

 

[jjlbmcnews]

Hi,

Thanks for your reply, I don't think the SP can be at fault as its just a simple select statement but thanks for your help anyway. Do you know of a simple way to check for duplicate values in a table, although the table has a primary key its almost as if the data set being returned contains a duplicate value.