tsql - SQL Server Deadlocking Issue -

i looking proper way prevent deadlocking issues being caused multiple processes trying update same record in table. i've been able prevent deadlocks first selecting record with (updlock) doing update. however, i'm not sure if work time or if cause other blocking issues when other processes inserting new records or updating other records in table.

create procedure usp_reduceorderamount            @orderid         int,            @reductionamount int begin  set nocount on  declare @ddatetime datetime                   set @ddatetime = getutcdate()  begin transaction  --quick fix... attempt block other callers trying update same record.  select * orders (updlock) order_id = @orderid  update dbo.orders set qty_open = qty_open - @reductionamount, updated_when = @ddatetime order_id = @orderid             commit  end 

first, don't understand why have select query inside transction. you're not using in update query, think can put outside.

then, maybe try change isolation level

set transaction isolation level     { read uncommitted     | read committed     | repeatable read     | snapshot     | serializable     } [ ; ] 

might try 1 of this

read uncommitted specifies statements can read rows have been modified other transactions not yet committed.

repeatable read specifies statements cannot read data has been modified not yet committed other transactions , no other transactions can modify data has been read current transaction until current transaction completes.

snapshot specifies data read statement in transaction transactionally consistent version of data existed @ start of transaction. transaction can recognize data modifications committed before start of transaction. data modifications made other transactions after start of current transaction not visible statements executing in current transaction. effect if statements in transaction snapshot of committed data existed @ start of transaction.

serializable specifies following: statements cannot read data has been modified not yet committed other transactions. no other transactions can modify data has been read current transaction until current transaction completes. other transactions cannot insert new rows key values fall in range of keys read statements in current transaction until current transaction completes.