@@IDENTITYin very busy site - if many insert in same time What happend?

Question

@@IDENTITYin very busy site - if many insert in same time What happend?

Valentin Petkov

SSC Rookie

Points: 49
More actions
August 4, 2008 at 12:27 pm

#195330

Hello to all
I am debugging in busy site call INSERT in each second 2-3 inserts
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'
We have Identity = Null
http://msdn.microsoft.com/en-us/library/aa933167.aspx
If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
One of the problems there is no Transaction... 🙁 but can not add at this moment
what else can improve?
How behaves the @@IDENTITY?
What happened when many users INSERT in same time?
Thanks
Valentin

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You do not want to us e@@IDENTITY. It returns that last Identity value which could be another table if you have triggers, etc... You want to use th SCOPE_IDENTITY() function. Check out @@IDENTITY in BOL for a comparison.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Valentin Petkov SSC Rookie Points: 49 More actions · Answer 2

There is One table No trigers

But it is return Null that's mean did not success to INSERT and this is confim!!! no inserted row

Thanks

for respond

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

There is a transaction it is just an implicit transaction that is being rolled back. In SQL Server 2005 you can use TRY CATCH error handling which I think you would want here.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Valentin Petkov SSC Rookie Points: 49 More actions · Answer 4

Thanks for respond

The problem is we do not want to redo the store procedure because there is replication....

in this situation I can not improve the query.

But still I want to understand why when is very busy

in each 1 second there is 1-3 INSERTs

in 6000 inserts it is drops 40-50

INDENTITY = Null and there is no insert in this table....

and the second my problem is that someone know that is this drops... and ask me where the rest

and saying 1-3 per second "How the other is doing this"

Thanks

valentin

GSquared SSC Guru Points: 260824 More actions · Answer 5

The problem is not with @@Identity (though that does have problems). The problem is that no row is being inserted. That means some other problem with the code that's doing the inserting.

Can you post the code for the insert proc?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Valentin Petkov SSC Rookie Points: 49 More actions · Answer 6

INSERT INTO Results (companycode, GUID, ComeTime)

VALUES (@companyCode, @GUID, GETDATE())

SELECT @@IDENTITY as ID

where there is column ID autonumer identity (1,1)

ID is Null - but after that is drop everything.... the proof is this GUID which is triger that someone try to do somthing and missing in Results

Statistic:

6000 rows : 40-50 lost INSERT on each one second 1-3 rows

Please share expiriance WHAT HAPPEND when many INSERT in same time

Thanks for responces

Valentin

GSquared SSC Guru Points: 260824 More actions · Answer 7

There has to be more surrounding that simple statement. Either that, or the code is never getting run in those cases.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

I'll just bet a plugged nickel that there isn't an IDENTITY column in the table...

CREATE TABLE #MyHead (RowNum INT, Something VARCHAR(20))

INSERT INTO #MyHead (Something)

VALUES ('Test row')

SELECT @@IDENTITY, SCOPE_IDENTITY()

DROP TABLE #MyHead

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 9

Jeff Moden (8/4/2008)
I'll just bet a plugged nickel that there isn't an IDENTITY column in the table...
CREATE TABLE #MyHead (RowNum INT, Something VARCHAR(20))
INSERT INTO #MyHead (Something)
VALUES ('Test row')
SELECT @@IDENTITY, SCOPE_IDENTITY()
DROP TABLE #MyHead

Nah. Then it would always be null. He says it's sometimes null.

To me, that means the variable was initialized, but probably never assigned a value. That means somewhere in the proc, that step isn't being run. Might be an "If" ahead of it, or a loop, that exits or skips over it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Valentin Petkov SSC Rookie Points: 49 More actions · Answer 10

Yes SOMETIMES is Null

What is your oppinion can relly on

http://msdn.microsoft.com/en-us/library/ms175098.aspx

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an :w00t: IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

It is just the DB can not handle multiple inserts in same time

I aslo can confirm there is NO GAP in indentity column!!!!!!!!!!!!

We have no DB error log also...!!!!!

:w00t:

Please share your oppinion

running

NSERT INTO Results (companycode, GUID, ComeTime)

VALUES (@companyCode, @GUID, GETDATE())

SELECT @@IDENTITY as ID

where there is column ID autonumer identity (1,1)

BECAUSE THERE IS NO TRANSACTION that's why NO GAPS into the table but ID return Null..???????

Thanks

Valentin

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

There is ALWAYS a transaction, you just don't have an explicit transaction. If the posted code is really the only code being run in the process then I don't have an explanation. I would recommend setting up a Trace using Profiler, save it as a script, and run it server-side for a couple hours and then check out the results. I'd start with these events:

Errors And Warnings - Exception

Locks - Lock:Timeout

Locks - Lock:Deadlock

Stored Procedures - RPC:Starting

Stored Procedures - RPC:Completed

Stored Procedures - SP:Starting

Stored Procedures - SP:Completed

TSQL - SQL:StmtStarting

TSQL - SQL:StmtCompleted

TSQL - SQL:BatchStarting

TSQL - SQL:BatchCompleted

Transactions - SQLTransaction

Transactions - DTCTransaction -- if you have multi-database issues

You'd need the TextData column, DatabaseName column (filter on this), ApplicationName (you could filter on this too if you have single application accessing the DB), StartTime, EndTime, LoginName at the very least.

If a couple hours doesn't show you anything you could run it again for a longer period.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 12

By any chance, would there be an Instead Of trigger defined on the table?

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms

myke SSC Journeyman Points: 84 More actions · Answer 13

First, how do you know that you're losing these inserts?

Second, are you positive that the data you're attempting to insert is valid?

I would go with the previous advice of running a trace against the DB, and looking to see EXACTLY what's going on. That's really the only way to tell what's failing, and to get an idea of why it would be failing.

I also have to agree with what was previously stated regarding the difference between @@IDENTITY and SCOPE_IDENTITY().

@@IDENTITY isn't completely arbitrary, but it is unreliable for identifying specific rows within a specific table, especailly if you're dealing with the volume of traffic you mentioned. In that case you always want to use scope_identity().

GSquared SSC Guru Points: 260824 More actions · Answer 14

SQL Server can, depending on hardware, handle a huge number of simultaneous inserts. That's not the problem.

If the inserts were failing and being rolled back, you'd have gaps in the identity column.

The fact that you don't means the code never reached the insert command. Somewhere before that, after you declare the variable but before the insert, something is either aborting the insert command, skipping it, or something of that sort.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon