Posted by Erland Sommarskog on 02/16/06 00:50

joshsackett (joshsackett@gmail.com) writes:
> 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.

No, that's a misunderstanding. The UPDATE of #TEMP is entirely
meaningless. The lock taken out on TICKETS will be a shared lock
that under the default isolation level will be released.

> 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)"?

You need a non-clustered index on (STATUS, TICKET_NUMBER), and you
need to add the locking hint WITH (UPDLOCK, HOLDLOCK). Finally, you
should add a ORDER BY TICKET_NUMBER to the query.

HOLDLOCK gives you serializable isolation level that prevents the result
of the query to be changed while the query is running. UPDLOCK is a
read-lock, which only can be held by one process. Thus, if a second
process arrive here, it will be held up until the first commits.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]