|
Posted by joshsackett on 02/15/06 23:55
I am redesigning an application that distributes heldesk tickets to our
50 engineers automatically. When the engineer logs into their window a
stored procedure executes that searches through all open tickets and
assigns a predetermined amount of the open tickets to that engineer.The
problem I am running into is that if 2 or more engineers log in at the
same time the stored procedure will distribute the same set of tickets
multiple times.
Originally this was fixed by "reworking" the way SQL Server handles
transactions. The original developer wrote his code like this:
-----
DECLARE @RET_STAT INT
SELECT 'X' INTO #TEMP
BEGIN TRAN
UPDATE #TEMP SET 'X' = 'Y'
SELECT TOP 1 @TICKET_# =TICKET_NUMBER FROM TICKETS WHERE STATUS = 'O'
EXEC @RET_STAT = USP_MOVE2QUEUE @TICKET_#, @USERID
IF @RET_STAT <> 0
ROLLBACK TRAN
RETURN @RET_STAT
END
COMMIT TRAN
-----
The UPDATE of the #TEMP table forces the transaction to kick off and
locks the row in table TICKETS until the entire transaction has
completed.
I would like to get rid of the #TEMP table and start using isolation
levels, but I am unsure which isolation level would continue to lock
the selected data and not allow anyone else access. Do I need a
combination of isolation level and "WITH (ROWLOCK)"?
Additionally, the TICKETS table is used throughout the application and
I cannot exclusively lock the entire table just for the distribution
process. It is VERY high I/O!
Thanks for the help.
Navigation:
[Reply to this message]
|