You are here: Re: Transaction isolation levels « MsSQL Server « IT news, forums, messages
Re: Transaction isolation levels

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

 

Navigation:

[Reply to this message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация