Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Identity Column locking

Posted on 2004-09-21
15
Medium Priority
?
254 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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 1000 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 1000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Viewers will learn how the fundamental information of how to create a table.

730 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