Solved

Identity Column locking

Posted on 2004-09-21
15
251 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
ID: 12110954
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
ID: 12110962
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
ID: 12110966
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!

 
LVL 10

Author Comment

by:smegghead
ID: 12111108
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
ID: 12111142
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
ID: 12111148
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
 
LVL 10

Author Comment

by:smegghead
ID: 12111160
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
ID: 12111240
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
ID: 12111250
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
ID: 12111295
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
ID: 12111346
I think that trace is a best solution to track it down.
0
 
LVL 44

Assisted Solution

by:bruintje
bruintje earned 250 total points
ID: 12111430
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
The AZure backup problem 11 51
push and Pull replication 31 45
SqlServer Table Triggers 3 28
add stored proc on publlication 4 15
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

679 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