Re: Transaction isolation levels

joshsackett (joshsackett@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I checked my existing connections events in the profiler, ... I don't see anything about isolation level. ... only using transaction in stored procedures. ... I though it was always Exclusive lock. ...
    (microsoft.public.sqlserver.programming)
  • Re: Pessimistic Locking
    ... > A normal select statement executed in a transaction with serializable ... > isolation level was still possible, ... > was called which should lock the row. ... > in a transacion with serializable isolation level and another client has ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: help: Timeout expired. The timeout period elapsed prior to completion of the operation or the se
    ... Actually we are putting records in a temp table which qualify according ... code fails very much even before transaction starts. ... unless really needed pls dont use lock hints as SQL ...
    (microsoft.public.sqlserver.server)
  • Re: Transactions in SQL Server
    ... >>4) Close the transaction ... >>because of the SET TRANSACTION ISOLATION LEVEL default behavior? ... > Hi Todd, ... > exclusive lock to do the update. ...
    (microsoft.public.sqlserver.server)