June 17, 2011 at 1:30 pm
SQL Server 2008, EE, 64-bit
I've got a table in a DB that get's populated with daily reporting information (reportID, scheduleID, Frequency, etc). We want to have multiple processes read from and update the table (ie. flagging it as processed) without fetching the same record twice (as this would result in generating a report more than once).
Every day we need to prep out reporting queue table:
TRUNCATE TABLE dbo.Reports
INSERT INTO Reports
SELECT a.[ReportID]
,a.[ATID]
,a.[AID]
,a.[Source]
,CONVERT(VARCHAR(40),a.NextDateTime,121)
,b.[ReportFileName]
,b.Name
,REPLACE(b.Name,' ','') + '_' + Convert (VARCHAR(10),a.[AID]) + '_' + Convert (VARCHAR(10),DATEADD(DAY,-1,a.[NextDateTime]),121) + '_' + REPLACE(Convert (VARCHAR(5),a.[NextDateTime],8),':','') as reportname
,a.Frequency
,a.ID
,a.CCScheduleID
,a.CCRecipientID
,a.Frequency,
a.running
FROM dbo.CC_Report_Schedules as a WITH(UPDLOCK, READPAST)
JOIN [dbo].[CC_ReportList] as b WITH(UPDLOCK, READPAST)
ON b.ReportId=a.ReportId
WHERE
CONVERT(VARCHAR(10),a.NextDateTime,121) <= CONVERT(VARCHAR(10),@RUNTIME,121)
AND a.Frequency not in (2,99)
AND running = 0
ORDER BY a.NextDateTime asc,a.[AID],a.reportid
SET @Cnt = @@ROWCOUNT
/* If there are reports to run, execute avaliable jobs */
IF (@Cnt > 0 )
BEGIN TRY
/* Fetch Jobs ID's and if the job isn't running, run it */
EXEC msdb.dbo.sp_start_job 'Instance1'
EXEC msdb.dbo.sp_start_job 'Instance2'
EXEC msdb.dbo.sp_start_job 'Instance3'
EXEC msdb.dbo.sp_start_job 'Instance4'
EXEC msdb.dbo.sp_start_job 'Instance5'
END TRY
BEGIN CATCH
PRINT ('There was an error starting the instances!')
END CATCH
Inside these "Instance' SQL Agent jobs are a few lines of code that execute another procedure. While there are literally thousands of reports to run, we loop through the available reports until the count reaches 0:
WHILE (SELECT COUNT(1) FROM dbo.Reports WITH(NOLOCK) WHERE Running = 0) >= 1
BEGIN
EXEC dbo.utl_ApplicationReOccurring 1 --> (Param would be 1-5, one for each instance (used only to compare the results after testing so we can see if anything was duplicated)
END
So using the code above would result in running the utl_ApplicationReOccurring procedure 5 times, each reading that Reports table and looping continuously until the count reaches 0.
Here is the code for the utl_ApplicationReOccurring procedure...which ultimately does all the work:
CREATE PROCEDURE [dbo].[utl_ApplicationReOccurring] (
@Instance int
) AS
/*
exec utl_ApplicationReOccurring 1
ALTER TABLE REPORTS SET (LOCK_ESCALATION = DISABLE) --> I played with this
because I saw table locking going on despite have used the UPDLOCK/READPAST
*/
SET NOCOUNT ON
DECLARE @err1 int, @Err2 int, @Err3 int
DECLARE @reports TABLE
(idnt int IDENTITY (1,1)
,ReportID INT
,ATID varchar(4)
,AcctID varchar(10)
,Source varchar (20)
,NextDateTime VARCHAR(40)
,ReportFileName VARCHAR(100)
,[Name] VARCHAR (60)
,ReportName VARCHAR (500)
,DeliveryFrequency char(4)
,RID VARCHAR(40)
,ScheduleID bigint
,RecipientID bigint)
WHILE (SELECT COUNT(1) FROM dbo.Reports WITH(NOLOCK) WHERE Running = 0) >= 1
BEGIN
BEGIN TRANSACTION AppReport
INSERT INTO @reports
SELECT TOP 1 ReportID
,ATID
,AcctID
,[Source]
,CONVERT(VARCHAR(40),NextDateTime,121)
,ReportFileName
,Name
,REPLACE(Name,' ','') + '_'
+ Convert (VARCHAR(10),AcctID) + '_'
+ Convert (VARCHAR(10),DATEADD(DAY,-1,NextDateTime),121) + '_'
+ REPLACE(Convert (VARCHAR(5),NextDateTime,8),':','') as reportname
,Frequency
,RID
,ScheduleID
,RecipientID
FROM dbo.Reports WITH(ROWLOCK, READPAST)
-- Also tried these hints: WITH(UPDLOCK, READPAST)
WHERE running = 0
ORDER BY NextDateTime ASC, AcctID, reportid
SET @err1 = @@ERROR
DELETE FROM dbo.Reports
WHERE ScheduleID in (SELECT ScheduleID FROM @Reports )
SET @Err2 = @@ERROR
INSERT INTO dbo.Results --> Used for testing purposes to compare the results
SELECT @Instance, ReportID, ScheduleID, GETDATE() FROM @Reports
SET @Err3 = @@ERROR
DELETE FROM @Reports
IF (@Err1 = 0) AND (@Err2 = 0) AND (@Err3 = 0)
BEGIN
/* Added to mimic that the system is runnign something */
WAITFOR DELAY '00:00:01'
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END
Output (snippet):
Instance | RptID | SchedID
10 7463
58 8124
10 8125
18 8128
48 8132
208133
308137
288138
108141
.
.
.
189412
389412
1279432
409450
589451
289451
109457
289458
309473
109473
489474
509480
.
.
.
As you can see it runs fine for a while...then after several hundred interations...it starts to duplicate reports (illustrated by looking at different InstanceID's being pulled by the same SchedID)
I've tried to follow the example illustrated quote nicely at http://www.mssqltips.com/tip.asp?tip=1257 and while I can recreate the example properly, I cannot get the above code to work without fetching the same record multiple times.
Can anyone assist?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 17, 2011 at 2:07 pm
My first spot to correct would be the Delete from dbo.Reports in utl_applicationReOccurring. I would guess the query above is returning the same reportID for two of your SqlAgent calls. AKA
SELECT TOP 1 ReportID
...
from dbo.reports
...
Delete from dbo.Reports where scheuldeID in (select yada from @reports)
if( @@rowCount = 0)
--Another process has grabbed this report
continue -- delete from @reports, grab another reportID
--note put a delete from @reports after while Begin
while(...)
Begin
delete from @reports
Begin Trans...
Note Call @@rowCount immediately after delete.
Good Luck
Daryl
June 17, 2011 at 2:44 pm
Thanks for the suggestions...will try them out shortly.
I wanted to clarify this though, wouldn't:
SELECT TOP 1 (...) FROM Reports WITH(UPDLOCK, READPAST) or (ROWLOCK,READPAST)
lock the row from the Reports table so no other SELECT can fetch the same record until the transaction is complete?
The DELETE that's occurring is based upon the locked record in the temp table (whose scope is different for each instance running)...not sure how it would attempt to delete the same record.
Just thinking out loud here...
Also, the @Reports table will be populated
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 18, 2011 at 1:22 pm
Got this working. A colleague forwarded me this article (http://www.dwdmbi.com/2010/12/use-updlock-readpast-table-hints-to.html) which made an interesting note about a needed index in order to avoid SQL parsing through the entire table to look through the correct row for the next transaction:
To enable this to work correctly, there must be an index on the column(s) being used to identify the selected row(s), in this case the RowID column. Without that index, SQL Server will need to look through the table to find the correct row for the second transaction, which will then get blocked since it won't be able to get past the row that's locked by the first transaction. The result in such a case will be that duplicates will be avoided but deadlocks will still occur - the same as using UPDLOCK without READPAST.
I also added the statement you reccomended:
BEGIN
DELETE FROM @Reports
BEGIN TRANSACTION AppReport
INSERT INTO @reports
SELECT TOP 1 ...
Thanks for the assitance Daryl!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply