July 1, 2014 at 1:12 pm
So all I’m trying to do is create a count from how many emails were passed in. The inserts into a log table are fine but my counts don’t create the record nor do they keep a running total until about half way through the execution. Example: I pass in 30,000 in batches of 500 and the count won’t even create the row until about 18,000 have passed and then will only count around 13,000. I’m so lost I have never had this happen so I gave up and reaching out to more eyes.
Database has snapshot isolation on if that matters and I’m using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Query
BEGIN TRANSACTION
IF EXISTS (SELECT 1 FROM dbo.tblStatsHourlyCampaign WITH (UPDLOCK) WHERE QueueID = @iQueueID AND ReportDay = @ReportDate)
UPDATE dbo.tblStatsHourlyCampaign SET [EmailsSent] = [EmailsSent] + @num WHERE QueueID = @iQueueID AND ReportDay = @ReportDate;
ELSE
INSERT dbo.tblStatsHourlyCampaign (QueueID, ReportDay,[EmailsSent]) VALUES (@iQueueID, @ReportDate, @num);
COMMIT TRAN
The query right after it is:
BEGIN TRANSACTION
UPDATE dbo.tblQueue WITH (XLOCK)
SET [EmailsSent] = [EmailsSent] + @num
WHERE QueueID = @iQueueID
COMMIT TRANSACTION
The bottom query shows the right number once we are done the top one does not. It doesn’t even create a row until half way through EVERY test. I’ve also tried the MERGE statemtn with the exact same results.
July 1, 2014 at 1:24 pm
Have you tried this without the table hints? Is this inside of a loop or something? It is hard to know what is going on without seeing all of the code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 2, 2014 at 1:26 pm
Thanks for the reply and sorry for the delay after the US Soccer game i crawled into a corner and cried for a few hours. The original code is below but this is what is happening.
This is a stored procedure that a third party application calls in a multi-threaded environment at insanely quick speeds passing in a table type of emails a queue id and a typeenum of what type of emails. transactional, single, replied..again just for stats. The dbo.tblSentLog insert and the dbo.tblQueue incremental stats work flawlessly. The problem is with the other batches below mainly the dbo.tblStatsHourlyCampaign table. Its a UPSERT that if the hour isn't there it creates it and updates the stats. I can run it and watch the tblQueue stats increment and the tblSentLog get insrted data but the UPSERT creates nothing until about half way through and it finally does and it starts working. Ive tried it NOT in a MERGE as you see in my origanal post i tried with different hints including none but it just won't create the first record and no error gets generated. I even tried it outside a transaction which works much better but then i get racing conditions.
Code:
ALTER PROCEDURE [dbo].[log_EmailsSent]
-- Add the parameters for the stored procedure here
@iTypeEnum int = 1, --1 campaigns, 2 workflow, 3 relaysend, 4 singlesend
@iQueueID int = 0,
@tblEmailItem dbo.EmailDataType READONLY
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
DECLARE @ReportDate smalldatetime = GETDATE()
DECLARE @num int
SET NOCOUNT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--Set hour for stats
SET @ReportDate = LEFT(CONVERT(VARCHAR,@ReportDate,120),13) + ':00'
SELECT @num = COUNT(EmailID) FROM @tblEmailItem
IF @iTypeEnum = 1 --Campaign
BEGIN
RETRY1:
BEGIN TRY
BEGIN TRANSACTION
--can correct stats based off log tables
INSERT INTO dbo.tblSentLog (EmailID, QueueID, CreateDate)
SELECT EmailID, @iQueueID, GETDATE()
FROM @tblEmailItem
COMMIT TRANSACTION
END TRY
BEGIN CATCH
BEGIN
ROLLBACK TRANSACTION
WAITFOR DELAY '00:00:00.50'
GOTO RETRY1
END
END CATCH
RETRY3:
BEGIN TRY
BEGIN TRANSACTION
--Now lets update the tblQueue AmountSent field and lets make sure it is thread-safe
UPDATE dbo.tblQueue WITH (XLOCK)
SET [EmailsSent] = [EmailsSent] + @num
WHERE QueueID = @iQueueID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
BEGIN
ROLLBACK TRANSACTION
WAITFOR DELAY '00:00:00.50'
GOTO RETRY3
END
END CATCH
BEGIN TRANSACTION
MERGE dbo.tblStatsHourlyCampaign WITH (UPDLOCK, HOLDLOCK) AS stat_target
USING (SELECT @iQueueID, @ReportDate) AS stat_source (QueueID, ReportDay)
ON (stat_target.QueueID = stat_source.QueueID AND stat_target.ReportDay = stat_source.ReportDay)
WHEN NOT MATCHED THEN
INSERT (QueueID, ReportDay,[EmailsSent])
VALUES (stat_source.QueueID, stat_source.ReportDay, @num)
WHEN MATCHED THEN
UPDATE SET [EmailsSent] = stat_target.[EmailsSent] + @num;
COMMIT TRANSACTION
END
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply