August 29, 2016 at 4:18 am
Hi all, I have the prove that SQL Server sometimes generate the same identity value.
I insert a row in a table and log the identity that was generated using SCOPE_IDENTITY(). Sometimes two consecutive insert (but there are at least 5 minutes between the 2 insert instructions) log the same identity and obviously I get the primary key violation (the id is the key).
I did not have found any documentation on this.
I can't reproduce the error "on demand", but it happen at random.
Thank you
August 29, 2016 at 5:17 am
Pivot-156509 (8/29/2016)
Hi all, I have the prove that SQL Server sometimes generate the same identity value.I insert a row in a table and log the identity that was generated using SCOPE_IDENTITY(). Sometimes two consecutive insert (but there are at least 5 minutes between the 2 insert instructions) log the same identity and obviously I get the primary key violation (the id is the key).
I did not have found any documentation on this.
I can't reproduce the error "on demand", but it happen at random.
Thank you
Quick remedy suggestion, change the query from using SCOPE_IDENTITY() to the OUTPUT clause of the insert or use IDENT_CURRENT to evaluate the identity value of SCOPE_IDENTITY(). The server does not generate conflicting values in the source table so the problem is with the values returned from the SCOPE_IDENTITY(), the problem is that one doesn't know which table if more than one are in scope at the time 😉
😎
To fully analyse this problem one has to have the DDL of the tables affected, the code in question and then some (more info).
August 29, 2016 at 6:26 am
Make sure you're up to date on service packs and cumulative updates. It's pretty unlikely such a fundamental bug is unaddressed by Microsoft. There's be a lot more yelling and screaming if lots of people were experiencing random errors with IDENTITY values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 29, 2016 at 6:47 am
Eirikur Eiriksson (8/29/2016)
Quick remedy suggestion, change the query from using SCOPE_IDENTITY() to the OUTPUT clause of the insert or use IDENT_CURRENT to evaluate the identity value of SCOPE_IDENTITY(). The server does not generate conflicting values in the source table so the problem is with the values returned from the SCOPE_IDENTITY(), the problem is that one doesn't know which table if more than one are in scope at the time 😉😎
To fully analyse this problem one has to have the DDL of the tables affected, the code in question and then some (more info).
I'am using SCOPE_IDENTITY only to log the problem. I want last identity generated on my scope.
This is the table:
CREATE TABLE [dbo].[SESSION](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SS_USER_ID] [int] NULL,
[SS_GUID] [uniqueidentifier] NULL,
CONSTRAINT [PK_SS_ID] PRIMARY KEY NONCLUSTERED
(
[SS_ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
This is the T-SQL code that insert new records:
declare @gdGUID UNIQUEIDENTIFIER
set @gdGUID = NEWID()
insert SESSION (SS_USER_ID, SS_GUID)
values (-1, @gdGUID)
-----------------------------------------------
--Temp Only to Log the problem
insert into _LOG_SESSION ([LL_GUID],[LL_SESSIONE_CURRENT_IDENT])
select @gdGUID, SCOPE_IDENTITY()
-----------------------------------------------
Log table result when problem happen:
LL_ID LL_GUID LL_TIMESTAMP LL_SESSIONE_CURRENT_IDENT
----------- ------------------------------------ ----------------------- -------------------------
28095 84C552AD-0646-4731-B8BA-05BDF18E261E 2016-08-28 18:00:01.293 2045840
28096 78296EF9-3DA4-42FE-92DA-679FAA024741 2016-08-28 18:05:01.350 2045840
LL_ID is the identity of the log table. You can see different GUID (two different execution of insert, column LL_GUID), 5 minutes from first to second execution (LL_TIMESTAMP). Same identity (LL_SESSIONE_CURRENT_IDENT)!
I can tell that every row inserted into SESSION table is deleted after very few time. Seem (sometimes, very rarely) that after deletion the last identity is reused. The insert on table SESSION does not fail, because the previous row with same identity was already deleted.
This procedure is used for many years without this problem. Happened 3 times in last 4 months.
Thank you
August 29, 2016 at 6:53 am
Grant Fritchey (8/29/2016)
Make sure you're up to date on service packs and cumulative updates. It's pretty unlikely such a fundamental bug is unaddressed by Microsoft. There's be a lot more yelling and screaming if lots of people were experiencing random errors with IDENTITY values.
Yes, this was my first opinion too. But the log prove that.
I have looked all CU, SP release notes, but nothing about identity is reported.
August 29, 2016 at 8:48 am
Possible causes:
1) SQL Server bug, as mentioned by others. VERY unlikely
2) bug in your code. possible issues:
a) you hold a value over from prior execution
b) you insert multiple rows and get improper identity, leading to overlap on subsequent run
c) scope_identity() is getting identity value from some other table. Very unlikely but needs to be mentioned:
https://msdn.microsoft.com/en-us/library/ms190315.aspx
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 29, 2016 at 9:00 am
With all due respect - just because your log table is showing the same value from another table twice doesn't quite compute to IDENTITY() being flawed. Also - for what it's worth - if identity were having an issue, then you'd see it whether or not you use SCOPE_IDENTITY or OUTPUT; if on the other hand you don't have the problem with OUTPUT is in play - then SCOPE_IDENTITY is the problem. Remember, you're not guaranteed that SCOPE_IDENTITY is returning the id from the table you just inserted into, it could be something inserted thanks to a trigger etc....
You'd have to be able show the SESSION table with the same Identity value in a single query, AND ensure you don't have anything odd like IDENTITY INSERT turned on, etc....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 29, 2016 at 9:46 am
TheSQLGuru (8/29/2016)
Possible causes:1) SQL Server bug, as mentioned by others. VERY unlikely
2) bug in your code. possible issues:
a) you hold a value over from prior execution
b) you insert multiple rows and get improper identity, leading to overlap on subsequent run
c) scope_identity() is getting identity value from some other table. Very unlikely but needs to be mentioned:
1) I understand and agree, but...
2a) I don't hold any value.
2b) I always insert only one row at time as you can see on the sql code above.
2c) I have used scope_identity only to log the problem and try to understand what happens. I don't use scope_identity in effective code. I don't calculate/insert manually the identity. I only do an insert.
I have a trigger on SESSION table on delete the saves the deleted row. The historical table has the identity of table SESSION has primary key. Sometimes the insert fails due to duplicate identity, but the GUID field is different, then the row generated in SESSION was different (see the code in previous post).
PS: Sorry for my English
August 29, 2016 at 9:53 am
From Books Online:
IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:
•IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
•@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
•SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
Here is the link I used: https://msdn.microsoft.com/en-us/library/ms175098(v=sql.110).aspx
August 29, 2016 at 9:59 am
Matt Miller (#4) (8/29/2016)
With all due respect - just because your log table is showing the same value from another table twice doesn't quite compute to IDENTITY() being flawed. Also - for what it's worth - if identity were having an issue, then you'd see it whether or not you use SCOPE_IDENTITY or OUTPUT; if on the other hand you don't have the problem with OUTPUT is in play - then SCOPE_IDENTITY is the problem. Remember, you're not guaranteed that SCOPE_IDENTITY is returning the id from the table you just inserted into, it could be something inserted thanks to a trigger etc....You'd have to be able show the SESSION table with the same Identity value in a single query, AND ensure you don't have anything odd like IDENTITY INSERT turned on, etc....
I don't use SCOPE_IDENTITY to insert any row.
I don't use identity insert on.
The duplicated identity are from two different sp execution that call the same sp to insert row in SESSION table.
August 29, 2016 at 10:00 am
Lynn Pettis (8/29/2016)
From Books Online:IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:
•IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
•@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
•SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
Here is the link I used: https://msdn.microsoft.com/en-us/library/ms175098(v=sql.110).aspx
Yes, I know 😉 I don't use any of them.
August 29, 2016 at 10:15 am
Pivot-156509 (8/29/2016)
Matt Miller (#4) (8/29/2016)
With all due respect - just because your log table is showing the same value from another table twice doesn't quite compute to IDENTITY() being flawed. Also - for what it's worth - if identity were having an issue, then you'd see it whether or not you use SCOPE_IDENTITY or OUTPUT; if on the other hand you don't have the problem with OUTPUT is in play - then SCOPE_IDENTITY is the problem. Remember, you're not guaranteed that SCOPE_IDENTITY is returning the id from the table you just inserted into, it could be something inserted thanks to a trigger etc....You'd have to be able show the SESSION table with the same Identity value in a single query, AND ensure you don't have anything odd like IDENTITY INSERT turned on, etc....
I don't use SCOPE_IDENTITY to insert any row.
I don't use identity insert on.
The duplicated identity are from two different sp execution that call the same sp to insert row in SESSION table.
Then I'm confused. In your previous answer - you specifically showed that you insert rows using scope_identity(). I understand you're trying to troubleshoot, but I've been bitten at time by writing poor troubleshooting code....
This is the T-SQL code that insert new records:
declare @gdGUID UNIQUEIDENTIFIERset @gdGUID = NEWID()
insert SESSION (SS_USER_ID, SS_GUID)
values (-1, @gdGUID)
-----------------------------------------------
--Temp Only to Log the problem
insert into _LOG_SESSION ([LL_GUID],[LL_SESSIONE_CURRENT_IDENT])
[highlight="#ffff11"]select @gdGUID, SCOPE_IDENTITY()[/highlight]
-----------------------------------------------
I think the issue is that you're not really showing us the code that IS inserting and/or retrieving. The basic code you showed for inserting wouldn't screw up IDENTITY on its own (like Grant said - if it were that screwy you'd have millions of MS customers protesting really loudly for the last 4 years!). I realized you're trying to stay generic - but there's an issue somewhere and you're not showing enough for it to be apparent.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 29, 2016 at 11:04 am
Can you please provide the FULL code involved here, including the trigger you mentioned?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 29, 2016 at 11:38 am
SESSION table:
CREATE TABLE [dbo].[SESSION](
[SS_ID] [int] IDENTITY(1,1) NOT NULL,
[SS_USER_ID] [int] NULL,
[SS_GUID] [uniqueidentifier] NULL,
CONSTRAINT [PK_SS_ID] PRIMARY KEY NONCLUSTERED
(
[SS_ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Historical table:
CREATE TABLE [dbo].[SESSION_HIS](
[SH_ID] [int] NOT NULL,
[SH_USER_ID] [int] NULL,
[SH_GUID] [uniqueidentifier] NULL,
CONSTRAINT [PK_SH_ID_SESSION_HIS] PRIMARY KEY NONCLUSTERED
(
[SH_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
Trigger on SESSION:
ALTER Trigger [dbo].[TR_SESSION_D] ON [dbo].[SESSION] FOR DELETE
AS
BEGIN
insert SESSION_HIS
select *
from DELETED
END
SESSION code:
declare @gdGUID UNIQUEIDENTIFIER
set @gdGUID = NEWID()
insert SESSION (SS_USER_ID, SS_GUID)
values (-1, @gdGUID)
--some code that does not work on SESSION
delete SESSION
where SS_GUID = @gdGUID
What happen is (3 times in last 4 months, never happened for many year before):
Violation of PRIMARY KEY constraint 'PK_SH_ID_SESSION_HIS'. Cannot insert duplicate key in object 'dbo.SESSION_HIS'.
Surely I can't view something that causes the problem.
August 29, 2016 at 11:57 am
Was the session table ever reseeded? If so - your history table may have id's greater than the current max SS_ID.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply