I'm getting some DUPs on Insert for one particular user in a piece of code that has been unchanged for a decade or more. The table is intended to be very high volume insert, but this particular client only has light use and probably most of the time, at present, just one user.
The user is tech-savvy and I asked if they were doing "double click" or somesuch that might trigger duplicate insert (attempts) in quick succession, but that seems very unlikely - that user is frequently generating duplicates, this only started happening recently, and impossible that they are double-clicking every time - perhaps one or twice out of frustration that something was slow, but even that would be "two clicks" rather than a double-click
Possible that the STATs are stale (I haven't updated them in case I should check that in some way first?)
If I retry the exact same Sproc call (with deliberately, newly, unique LogName) I cannot reproduce the error, so it must be a subtle timing issue.
Tracing with SQL Profiler I can see the SProc call at the exact time that the issue arose, but there is no other SProc logged at the same time, so I don't think it is a second-user / second-thread issue
Here's the code - could this generate a DUP on INSERT somehow?
CREATE PROCEDURE dbo.USP_LogName_Get -- Returns ID of Log Name, or -1 if error (e.g. @LogName IS NULL) @LogName varchar(255) -- Log Name to find (or create) AS SET NOCOUNT ON DECLARE @ln_ID int, -- ID of LogName record @intErrNo int, -- Error No @intErrLocation int, -- (unique) Location of error @intRowCount int -- Row Count IF @LogName IS NOT NULL BEGIN -- Get ID for existing record, if any SELECT @ln_ID = ln_ID FROM dbo.LogName WHERE ln_Name = @LogName SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT , @intErrLocation = 1
IF @intErrNo = 0 AND @intRowCount = 0 BEGIN BEGIN TRANSACTION -- Insert new record (with safeguard that it does not already exist) INSERT INTO dbo.LogName ( ln_Name ) SELECT [ln_Name] = @LogName WHERE NOT EXISTS ( SELECT * FROM dbo.LogName WHERE ln_Name = @LogName ) SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT , @intErrLocation = 2, @ln_ID = scope_identity()
-- The INSERT failed (no record created - just added by someone else?) -- ... retrieve that ID IF @intErrNo = 0 AND @intRowCount = 0 BEGIN SELECT @ln_ID = ln_ID FROM dbo.LogName WHERE ln_Name = @LogName SELECT @intErrNo = @@ERROR, @intRowCount = @@ROWCOUNT , @intErrLocation = 3 END COMMIT END END ELSE BEGIN SELECT @intErrNo = -2, @intRowCount = 0, @intErrLocation = 4 END
IF COALESCE(@intErrNo, -1) <> 0 OR COALESCE(@intRowCount, -1) <> 1 BEGIN -- Error occurred, including attempt to store NULL DECLARE @strErrMsg varchar(255) -- Late declaration to improve efficiency SELECT @strErrMsg = 'USP_LogName_Get:Error sLogName[%s:%d/%d(%d)]', @ln_ID = -1 -- Return -1 to indicate Logname not valid RAISERROR (@strErrMsg, 16, 1, 1, @LogName, @intErrNo, @intRowCount , @intErrLocation) END RETURN @ln_ID SET NOCOUNT OFF GO
I don't think this should EVER fail [creating a DUP], is there some circumstance that I am missing?
Here's the table: (I don't know why ln_Name was not created as NOT NULL)
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE dbo.LogName ( ln_EditNo smallint NULL, ln_CreateDt datetime NULL, ln_CreateUser varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ln_UpdateDt datetime NULL, ln_UpdateUser varchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, ln_ID int IDENTITY(10000,1) NOT NULL, ln_Name varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Clustered Unique Index on ln_Name, and (non clustered) PKey on the ID (just to keep it unique)
CREATE UNIQUE CLUSTERED INDEX [IX_LogName_ln_Name] ON dbo.LogName ( ln_Name ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE dbo.KK_SM_yVLN_ValidateLogName ADD CONSTRAINT [PK_KK_SM_yVLN_ValidateLogName] PRIMARY KEY NONCLUSTERED ( yvln_ID ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY] GO
No idea why there is a Trigger, other than that we "always have one" to increment the EditNo column and update the UpdateDate and UpdateUser columns
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE TRIGGER dbo.UTR_LogName ON dbo.LogName FOR INSERT, UPDATE -- DELETE AS SET NOCOUNT ON DECLARE @dNow datetime, @sUser varchar(4) SET @dNow = GetDate() SET @sUser = LEFT(user_name(), 4) UPDATE U SET U.ln_EditNo = COALESCE(U.ln_EditNo, 0)+1, -- Increment edit counter U.ln_CreateDt = COALESCE(U.ln_CreateDt, @dNow), U.ln_CreateUser = COALESCE(U.ln_CreateUser, @sUser), U.ln_UpdateDt = @dNow, U.ln_UpdateUser = @sUser FROM dbo.LogName AS U, inserted AS I WHERE U.ln_ID = I.ln_ID GO