Solved

Identity Column locking

Posted on 2004-09-21
15
247 Views
Last Modified: 2012-05-05
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
Comment
Question by:smegghead
  • 4
  • 4
  • 3
  • +1
15 Comments
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
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
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
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
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
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
 
LVL 10

Author Comment

by:smegghead
Comment Utility
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
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
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
 
LVL 9

Expert Comment

by:solution46
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Author Comment

by:smegghead
Comment Utility
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
 
LVL 12

Expert Comment

by:patrikt
Comment Utility
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
 
LVL 44

Expert Comment

by:bruintje
Comment Utility
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
 
LVL 10

Author Comment

by:smegghead
Comment Utility
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
 
LVL 12

Accepted Solution

by:
patrikt earned 250 total points
Comment Utility
I think that trace is a best solution to track it down.
0
 
LVL 44

Assisted Solution

by:bruintje
bruintje earned 250 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now