when to use with(nolock) and with(rowLock) in sql server

Question

when to use with(nolock) and with(rowLock) in sql server 2008. and what is the advantage and disadvantages of using this. and what impact on performance of application of using this. i am getting error :- transaction process id was deadlocked on lock resources with another process and has been detect as deadlock victiom

naveen awasthi · Answer

I have the following code and i am getting error :- transaction process id was deadlocked on lock resources with another process and has been detect as deadlock victiom so what is the solution to remove this error this procedure is use to save data in sale table alter procedure Pro_Sale_Insert ( @Voc_date datetime @Party nvarchar(40) @Sadd1 nvarchar(50) @Sadd2 nvarchar(max) @Po_no nvarchar(30) @Po_date datetime @Cform nvarchar(3) @Fform nvarchar(3) @Amount decimal(102) @Tax nvarchar(10) @Tax_per decimal(52) @Tax_amt decimal(102) @Round_off decimal(102) @Total decimal(102) @Uname nvarchar(10) @Ref nvarchar(40) @ship_amt decimal(102) @due_date datetime @voc_no int output @other_amt decimal(102) @pcode int @gcode int @type nvarchar(2) @BarCode nvarchar(300) ) as Begin declare @Invc_no int @op_date datetime @CallFrom Nvarchar(20) set @CallFrom='Insert' set @Invc_no=(select (isnull(max(Invc_no)0)+1) from sale with(rowlock) where gcode=@gcode) set @op_date=getdate() if(@type=2) begin set @pcode=(select (isnull(max(pcode)0)+1) from party with(rowlock)) insert into party(pcodepartypadd1padd2underRefop_balop_date) values (@pcode@party'''''Debtors'@ref0@op_date) end set @voc_no=(select (isnull(max(voc_no)0)+1) from sale) insert into sale(Invc_no Voc_no Voc_date Party Sadd1 Sadd2 Po_no Po_date Cform Fform Amount Tax Tax_per Tax_amt Round_off Total Uname ref due_date ship_amt rec_amt other_amt pcode gcode BarCode) values ( @Invc_no  @Voc_no  @Voc_date  @Party  @Sadd1  @Sadd2  @Po_no  @Po_date  @Cform  @Fform  @Amount  @Tax  @Tax_per  @Tax_amt  @Round_off  @Total  @Uname  @Ref @due_date  @ship_amt  0  @other_amt @pcode @gcode @BarCode ) exec insert_sale_ledger @voc_no@voc_date @pcode@party @Amount@Total@Tax @Tax_amt@Ref @CallFrom end and this procedure is use to save data in ledger table alter procedure insert_sale_ledger ( @voc_no decimal(10) @voc_date datetime @Pcode int @party nvarchar(40) @Amount decimal(102) @Total decimal(102) @Tax nvarchar(10) @Tax_amt decimal(102) @Ref nvarchar(40)  @CallFrom Nvarchar(20)=null ) as begin if(@CallFrom='Update') begin delete from ledger where voucher='Sales' AND voc_no=@voc_no end declare @under nvarchar(17) ------------------------------------no1 ki entery--------------------------------------------------------- INSERT INTO ledger(vouchervoc_nodebitCREDITparty nar DATE paynpartyunderpcode) VALUES ('Sales' @voc_no 0@Amount 'Sales A/c' 'Sale Items'@voc_date'True'@party'Sales'2) INSERT INTO ledger(vouchervoc_nodebitcreditparty nar DATE paynpartyunderpcoderef) VALUES ('Sales' @voc_no @Total 0@party'Sale Items'@voc_date'False''Sales A/c''Debtors'@pcode@Ref) if(@Tax_amt>0) begin select @pcode=pcode@under=under from vatcstNew where party=@Tax INSERT INTO ledger(vouchervoc_nodebitCREDITparty nar DATE paynpartyunderpcode) VALUES ('Sales' @voc_no 0@Tax_amt @Tax'Sale Items'@voc_date'False'@party@under@pcode) end end and this is trigger to update stock ALTER trigger tri_salei_stock_add on salei after insert as begin declare @gcode int@voc_date datetime@sku_code nvarchar(10)@Price decimal(102)@sale_qty decimal(103)@ser_icode nvarchar(1)@amt decimal(102) select @gcode=gcode@voc_date=voc_date @sku_code=sku_code@Price=Price@sale_qty=Qty@amt=amt from inserted set @ser_icode=(select sum(1) from stock with(nolock) where voc_date=@voc_date and sku_code=@sku_code and gcode=@gcode group by gcodesku_codevoc_date) if @ser_icode=1 begin update stock set sale_qty=sale_qty+@sale_qty where voc_date=@voc_date and sku_code=@sku_code and gcode=@gcode end else begin insert into stock(gcodesku_codevoc_datesale_qtypurc_qtyprice)values (@gcode@sku_code@voc_date@sale_qty0@Price) end end

Munesh Sharma · Answer

WITH (NOLOCK) is the equivalent of using READ UNCOMMITED as a transaction isolation level. So you stand the risk of reading an uncommitted row that is subsequently rolled back i.e. data that never made it into the database. So while it can prevent reads being deadlocked by other operations it comes with a risk. In a banking application with high transaction rates it's probably not going to be the right solution to whatever problem you're trying to solve with it IMHO. The with (rowlock) is a hint that instructs the database that it should keep locks on a row scope. That means that the database will avoid escalating locks to block or table scope. You use the hint when only a single or only a few rows will be affected by the query to keep the lock from locking rows that will not be deleted by the query. That will let another query read unrelated rows at the same time instead of having to wait for the delete to complete. If you use it on a query that will delete a lot of rows it may degrade the performance as the database will try to avoid escalating the locks to a larger scope even if it would have been more efficient.