• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Identity Column locking

Hi,

I have a database with about 10 tables in it, each table has an ID field which is an identity with a seed of (1,1)

This generally works fine, but occasionally, I lose records which are written to the table.

If I look at the ID field, there are occasionally gaps between the numbers..

I'm not sure if I should be locking the record before I insert or whatever ??

Is there any other reason for gaps in the ID column ?? apart from someone deleting them obviously.

Thanks
Smg.
0
smegghead
Asked:
smegghead
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
patriktCommented:
Yes there can be gaps. It is becouse of how identity works.
Imagine situation when you start transaction and insert to identity column. New indentity must be alocated nad if you rollback transaction there will be gap.
It is normal system behavior.

Patrik
0
 
bruintjeCommented:
most likely reason for gaps in identity values is if there was a started insert but didn't finish it, or if a record is deleted after it's inserted

most of the time an autonumber/identity is nothing but a unique record identifier and shouldn't have any other significance other than being unique, if you really need sequential numbers you might add an additional column that gets it's value from some process you can control

or you write code/stored procedures that rolls back inserts that don't make it through the end
0
 
patriktCommented:
If you accidentaly lost record it may be becouse some unseen error.
Each statement is implicit transaction and if there is error it is automaticaly rollbacked.

Look on situation when record is lost and analyze why.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
smeggheadAuthor Commented:
It's so rare, it's only happened a few times in the last year.

burintje: I realise that gaps can appear due to a row being deleted. I also, don't necessarily want a sequential number, I just want a unique one, as you state. But I was wondering how these gaps come about.. surely if you insert a new row into a table with an identity field, SQL server must lock the 'count' before getting the next value, and incrementing itself.

It's actually being updated from an asp.net web program, so there can be multiple people adding records at the same time, but surely SQL would take care of this ??

Smg.
0
 
bruintjeCommented:
Patrik did it add in his comments too

if you have an app that inserts but doesn't commit the transaction [crashing while inserting] or just didn't complete the insert somehow it will leave a gap the server doesn't do anything with the column since each entry will still be unique
0
 
solution46Commented:
I may be wrong, but I believe SQL Server will increment the identity for each INSERT that starts, whether or not is commits.

Otherwise, SQL Server would have to lock a table for INSERTS each time an INSERT was started.

s46.
0
 
smeggheadAuthor Commented:
I'm not using transactions, unless they're done automatically by the .net framework for each call to the db.

If there was an error while inserting, it would throw an exception, which I deal with, and there was no such error.

Smg.
0
 
patriktCommented:
Event if you don't use explicit BEGIN TRANSACTION there still be implicit transaction coverring each statement. Identity is incremented on start of each such statement regardles what will be result.

Can you track down when row is lost?

Patrik
0
 
bruintjeCommented:
a good article source : http://www.sqlteam.com/item.asp?ItemID=8003
on the subject contains a quote that's already been stated here

-------
SQL Server makes no attempt to guarantee sequential gap-free values in identity columns. If records are deleted SQL Server won't go back and populate using those values. It's also possible for an insert to fail and "use up" an identity value.
-------

they even describe a way to return the used identity value so you can use that in your logging if needed
0
 
smeggheadAuthor Commented:
The application in question is very similar to the experts exchange system (in principal anyway, just without the advertising etc..)

What happens is, someone fills in a comment, like I'm doing now, they click submit, the incident re-appears, including the new comment, but then after checking again, it seems to have gone. and there is suspiciously a gap in the ID fields.

This happens too infrequently for me to identify what's going on. I suppose what I could do it run a trace on the server, until this happens again, then check the trace file. Or is there a better way of identifying what is going wrong ?

Smg.
0
 
patriktCommented:
I think that trace is a best solution to track it down.
0
 
bruintjeCommented:
yes that would be a good way, otherwise using a trigger on the insert/update action to audit the action in a special table would help too

source : http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20896997.html
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now