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 awasthiPosted Oct 10, 2014, 6:59 AM
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(10,2),
@Tax nvarchar(10),
@Tax_per decimal(5,2),
@Tax_amt decimal(10,2),
@Round_off decimal(10,2),
@Total decimal(10,2),
@Uname nvarchar(10),
@Ref nvarchar(40),
@ship_amt decimal(10,2),
@due_date datetime,
@voc_no int output,
@other_amt decimal(10,2),
@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(pcode,party,padd1,padd2,under,Ref,op_bal,op_date) values (@pcode,@party,'','','Debtors',@ref,0,@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(10,2),
@Total decimal(10,2),
@Tax nvarchar(10),
@Tax_amt decimal(10,2),
@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(voucher,voc_no,debit,CREDIT,party ,nar ,DATE ,pay,nparty,under,pcode) VALUES
('Sales' ,@voc_no ,0,@Amount ,'Sales A/c' ,'Sale Items',@voc_date,'True',@party,'Sales',2)
INSERT INTO ledger(voucher,voc_no,debit,credit,party ,nar ,DATE ,pay,nparty,under,pcode,ref) 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(voucher,voc_no,debit,CREDIT,party ,nar ,DATE ,pay,nparty,under,pcode) 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(10,2),@sale_qty decimal(10,3),@ser_icode nvarchar(1),@amt decimal(10,2)
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 gcode,sku_code,voc_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(gcode,sku_code,voc_date,sale_qty,purc_qty,price)values (@gcode,@sku_code,@voc_date,@sale_qty,0,@Price)
end
end
Munesh SharmaPosted Oct 10, 2014, 6:52 AM
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.