Solved

Identity Column locking

Posted on 2004-09-21
15
248 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

863 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

25 Experts available now in Live!

Get 1:1 Help Now