July 17, 2012 at 12:07 am
Here is the DDL for the Table:
CREATE TABLE [dbo].[SeqNo](
[SeqNoGUID] [dbo].[DPrimaryKey] ROWGUIDCOL NOT NULL CONSTRAINT [SeqNoAUTO] DEFAULT (newsequentialid()),
[StGUID] [dbo].[DForeignKey] NOT NULL,
[TheDay] [datetime] NOT NULL,
[LastSeqNo] [int] NOT NULL,
CONSTRAINT [PK_SeqNo] PRIMARY KEY NONCLUSTERED
(
[SeqNoGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [AK_SeqNo] UNIQUE NONCLUSTERED
(
[TheDay] ASC,
[StGUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SeqNo] WITH NOCHECK ADD CONSTRAINT [FK_SeqNo_St] FOREIGN KEY([StGUID])
REFERENCES [dbo].[St] ([StGUID])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[SeqNo] CHECK CONSTRAINT [FK_SeqNo_St]
GO
The failing Stored Procedure:
CREATE PROCEDURE [dbo].[mspResetDayNo] @Today DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @DateSrc DATETIME
DECLARE @StGUID UNIQUEIDENTIFIER
-- Check Input is Valid
IF @Today IS NULL
BEGIN
RAISERROR (N'@Today cannot be NULL',
10, -- Severity,
1);
RETURN 1;
END
-- Chop off the time part:
SELECT
@DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today))
-- Get Current Store
SELECT
@StGUID = dbo.MyStGUID()
-- If this is the first entry for the day then initialise:
IF NOT EXISTS ( SELECT
1
FROM
dbo.SeqNo AS sn
WHERE
sn.TheDay = @DateSrc
AND sn.StGUID = @StGUID )
BEGIN
INSERT INTO dbo.SeqNo
(
StGUID
,TheDay
,LastSeqNo
)
VALUES
(
@StrGUID
,@DateSrc
,0
);
END
RETURN(0)
END
July 17, 2012 at 12:12 am
How often is the procedure [dbo].[mspResetDayNo] called for each store in a day?
July 17, 2012 at 12:15 am
Also, the following appear to be user defined types of some sort. What are the base type for them (I can't create your table as it is defined).
[dbo].[DPrimaryKey]
[dbo].[DForeignKey]
July 17, 2012 at 12:21 am
Good idea for posting code, test it all in an empty sandbox database. Found a typo in your procedure and you provided a check constraint and FK that wouldn't work due to a missing table.
July 17, 2012 at 12:58 am
Lyn:
1) The stored proc is called on each application load, but the insertion should only run once.
2) Here are the definitions of the 2 user defined types. Don't ask why they were used like this, I inherited the system...
CREATE TYPE [dbo].[DPrimaryKey] FROM [uniqueidentifier] NOT NULL
CREATE TYPE [dbo].[DForeignKey] FROM [uniqueidentifier] NULL
3) What was the typo? I can't post the code in its pure form in a public place due to 'reasons' but a representation is acceptable, so I might have made a typo. I've not added the St (Store = Lets say this table has StGUID DPrimaryKey and StName varchar(50) fields) table or the Self table, but I think I've posted about the Self table before.
July 17, 2012 at 1:02 am
Scott Anderson-466019 (7/17/2012)
Lyn:1) The stored proc is called on each application load, but the insertion should only run once.
2) Here are the definitions of the 2 user defined types. Don't ask why they were used like this, I inherited the system...
CREATE TYPE [dbo].[DPrimaryKey] FROM [uniqueidentifier] NOT NULL
CREATE TYPE [dbo].[DForeignKey] FROM [uniqueidentifier] NULL
3) What was the typo? I can't post the code in its pure form in a public place due to 'reasons' but a representation is acceptable, so I might have made a typo. I've not added the St (Store = Lets say this table has StGUID DPrimaryKey and StName varchar(50) fields) table or the Self table, but I think I've posted about the Self table before.
Look at the insert.
July 17, 2012 at 1:14 am
Yes, well spotted. A typo.
So other than that, what could it be that would make the SQL fail?
I know it shouldn't happen but it appears that something is running the stored proc twice and somehow creating a race condition where one thread is only up to performing the check if the record exists and another thread is up to performing the insert. I would have thought having both the check and insert in the same SQL statement would avoid that race condition if it was possible.
July 17, 2012 at 1:39 am
Scott Anderson-466019 (7/17/2012)
Yes, well spotted. A typo.So other than that, what could it be that would make the SQL fail?
I know it shouldn't happen but it appears that something is running the stored proc twice and somehow creating a race condition where one thread is only up to performing the check if the record exists and another thread is up to performing the insert. I would have thought having both the check and insert in the same SQL statement would avoid that race condition if it was possible.
Two calls to the stored procedure at the same time.
Here is a modified version of your stored procedure. Do with it what you think appropriate.
ALTER PROCEDURE [dbo].[mspResetDayNo] @Today DATETIME
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE @DateSrc DATETIME;
DECLARE @StGUID UNIQUEIDENTIFIER;
-- Check Input is Valid
IF @Today IS NULL
BEGIN
RAISERROR (N'@Today cannot be NULL',
10, -- Severity,
1);
RETURN 1;
END
-- Chop off the time part:
SELECT
@DateSrc = DATEADD(d, 0, DATEDIFF(d, 0, @Today))
-- Get Current Store
SELECT
@StGUID = dbo.MyStGUID()
BEGIN TRY
BEGIN TRANSACTION
-- If this is the first entry for the day then initialise:
IF NOT EXISTS ( SELECT
1
FROM
dbo.SeqNo AS sn
WHERE
sn.TheDay = @DateSrc
AND sn.StGUID = @StGUID)
BEGIN
INSERT INTO dbo.SeqNo
(
StGUID
,TheDay
,LastSeqNo
)
VALUES
(
@StGUID
,@DateSrc
,0
);
END
COMMIT TRANSACTION; -- No error, commit the transaction
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION; -- Have an error, rollback the transaction;
-- Declare variables to capture error information
--DECLARE @ErrorLine int,
-- @ErrorMessage nvarchar(2048),
-- @ErrorNumber int,
-- @ErrorProcedure nvarchar(126),
-- @ErrorSeverity int,
-- @ErrorState int;
-- Capture the error information, these variables can be used to write information
-- to an error log table, and/or used in a RAISERROR statement.
-- ERROR_NUMBER = 2627 represents UNIQUE KEY Violation. This could be used to trap and not raise this
-- specific error, RAISERROR could be used to raise other errors to the application.
--select
-- @ErrorLine = ERROR_LINE(),
-- @ErrorMessage = ERROR_MESSAGE(),
-- @ErrorNumber = ERROR_NUMBER(),
-- @ErrorProcedure = ERROR_PROCEDURE(),
-- @ErrorSeverity = ERROR_SEVERITY(),
-- @ErrorState = ERROR_STATE();
--select
-- ErrorLine = ERROR_LINE(),
-- ErrorMessage = ERROR_MESSAGE(),
-- ErrorNumber = ERROR_NUMBER(),
-- ErrorProcedure = ERROR_PROCEDURE(),
-- ErrorSeverity = ERROR_SEVERITY(),
-- ErrorState = ERROR_STATE();
END CATCH
RETURN(0)
END
go
July 17, 2012 at 6:57 pm
Thanks for that Lynn. I modified the procedure to add your try, error collection and RaiseError then changed the SQL to not perform the IF Exists check to force the error to occur every time. The exception generated had these extra values which were the same as before except for the line number which pointed back to the failing insert sql code. So nothing more than we already know.
The thought did occur to me some time ago to use the try/catch to filter out out the error number for this "problem" and make it go away, but I know it will come back later like a "check engine light" in a worse form when you put "sweep it" and "under the rug" together.
I figured there should be a reasonable explanation for either it working like this ("by MS design") or a typo fix in the stored procedure. Though, if the proc has to swallow the error, I guess a comment saying this will suffice.
Thanks again for your help.
July 17, 2012 at 7:04 pm
One more thing, is there a difference between a TRANSACTION ISOLATION LEVEL REPEATABLE READ and the following sql?
INSERT INTO ...
SELECT ...
WHERE NOT EXISTS ( SELECT ... );
One would think that within the SQL statement, the sub-query select should be effectively a repeatable read, right?
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply