Creating a Read Once SP for Multi Threaded App

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Creating a Read Once SP for Multi Threaded App

Author

Topic

terry.aney
Starting Member

1 Post

Posted - 2012-01-17 : 11:32:32

I'm having a problem in a multi-threaded application where I'm getting results duplicated. I've looked at both the SP and the code that calls/processes the SP and I can not spot any problems, yet the duplicates happen at random times/amounts. I was hoping to have some experts review this SP and see if it looks all right.

Essentially, I have a DB schema of Jobs -> (1-Many) JobData -> (1-Many) Result. A Job has many JobData rows. Any processing thread can grab a JobData row then inserts one or more results as it processes. So the Result table is being populated by many threads at the same time.

The calling application, once it finishes creating the Job and inserting the JobData rows, it then enters a loop to keep polling for Results until all results are read. It doesn't matter which JobData the result comes from, it just wants the next chunk of results. We've narrowed down the code creating duplication to either the SP or the looping code. The code that is calling the procGetResults is single threaded, but while it is looping/reading, there are still threads populating the Results table. Not sure if any contention for the table there is causing problem.

Final notes:

There are NOT duplicates in the Result table, only in our reading/processing of them.

As the SP indicates below I have a rReadAttempts (int) that I increment, and that is ALWAYS set to 1 hinting that the SP never grabbed more than one result.

We used to only use the rResultRead (bit) as a flag and used OUTPUT INTO temp table, but we have since added a rReadLock (unique identifier) as the locking mechanism instead. I've left the commented code in there as reference.

IF the SP is the problem (which looking over this code over and over, I'm not convinced it is), my only guess is that we have some setting making Transactions behave differently than I expect.

Thanks in advance. Any suggestions of a better implementation of a Read Once SP is welcome.

CREATE PROCEDURE dbo.procGetResults
@jobKey uniqueidentifier,
@pageSize int,
@currentTimeUtc datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @return int, @error int, @rowCount int, @inTransaction bit;
DECLARE @lock uniqueidentifier;

SET @return = 0
SET @inTransaction = 0
SET @lock = NewID()

IF( @@TRANCOUNT = 0 ) BEGIN

BEGIN TRANSACTION
SET @inTransaction = 1

END ELSE BEGIN

SET @inTransaction = 0

END

/*
-- Create a temp table TO store the select results
DECLARE @UnReadResults TABLE
(
ResultKey uniqueidentifier
)
*/
UPDATE Result
SET rResultRead = 1, rReadLock = @lock, rReadAttempts = rReadAttempts + 1
-- OUTPUT INSERTED.rKey INTO @UnReadResults
FROM (
SELECT TOP ( @pageSize ) rKey
FROM Result
INNER JOIN JobData ON rJobDataKey = jdKey AND jdJobKey = @jobKey
WHERE rResultRead = 0
) unread
WHERE unread.rKey = Result.rKey AND Result.rResultRead = 0 AND rReadLock IS NULL

UPDATE Job
SET jDateLastActivity = @currentTimeUtc
WHERE jKey = @jobKey

/* If all job results have been read and we are not still waiting for more JobData rows to process, then I can set the flag on the Job row saying all results have been read. */
IF NOT EXISTS( SELECT rKey FROM Job INNER JOIN JobData ON jKey = jdJobKey INNER JOIN Result ON jdKey = rJobDataKey WHERE jKey = @jobKey AND ( rResultRead = 0 OR jBatchStatus = 1 /* PendingData */ ) ) BEGIN
UPDATE Job
SET jResultsRead = 1
WHERE jKey = @jobKey
AND jStatus != 0 /* New */ AND jStatus != 1 /* Processing */ -- If processing possible no result rows added yet and EXISTS() query would return nothing, thus try to set job to 'copmlete'
END

-- Return results that were read
SELECT j.jKey, j.jStatus, j.jResultsRead,
jd.jdKey, jd.jdJobKey, jd.jdDateStart, jd.jdDateComplete, jd.jdStatus,
r.rKey, r.rProfileKey, r.rProfileAuthID, r.rResult
FROM Job j
INNER JOIN JobData jd
ON jKey = jdJobKey
INNER JOIN Result r
ON jdKey = rJobDataKey
WHERE rReadLock = @lock
/*
INNER JOIN @UnReadResults urr
on rKey = urr.ResultKey
*/

IF( @inTransaction = 1 ) BEGIN

SET @inTransaction = 0
COMMIT TRANSACTION

END

RETURN @return

Cleanup:

IF( @inTransaction = 1 ) BEGIN
SET @inTransaction = 0
ROLLBACK TRANSACTION
END

RETURN @error

END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources