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