PLEASE GUIDE ME, SQL SERVER + VB.NET RECORD LOCKING

[vijithamar]

HI

please help me to change sql server locking option to TABLE LOCK to PAGE/ROW LOCK

I tried like this :-

cmd.Connection = Conn
trn= Conn.BeginTransaction()
cmd.Transaction = trn

For i=1 to NumOfProd 
  cmd.CommandText = "Update batch set stock=" & vqty & "   where batchid=" & vid
  cmd.ExecuteNonQuery()

Next i
trn.commit

my problem is, entier table 'Batch' get locked for updation until the commit statement. other computers has to wait until the For-Loop gets over


i tried 'WITH (ROWLOCK)' option in update statement but no effect.

Thank you
Viju


please help me

 

[jhaith]

The statement itself is not performing the lock, it is the transaction so WITH (ROWLOCK) inside the update statement would not help. Not sure of everything involved in the scenario, but as an option to speed up the process you could build a #temp table, populate the table in the for loop, the do 1 update statement inside a transaction that would update batch.stock with your #temp.stock by the batchid's. This would be more effective imo. If you need more detail let me know.

-jhaith

 

[vijithamar]

Thanks for your reply

This is a POS Program where BATCH Table is holding the available stock. All users are reading the batch table
Continuously for knowing available stock, and updating the stock after each sale is made. the batch table holding many product's stock. and in one sale there will be more than 500 products.

There are more tables updating inside the loop. (i am not mentioned in code sample)

so it taking time to other users to update one sale.

plese tell me in detail, how i can do this with #temp table


Thanks

 

[jhaith]

Try something like this. There could be some errors this is just off the top of my head.

oCmd.ExecuteNonQuery(CREATE TABLE #tmp ([batchid] int, [stock] int))

For i = 1 to NumOfProd
oCmd.CommandText = "INSERT #tmp ([batchid],[stock]) VALUES(" & vid & ", " & vqty & ")"
oCmd.ExecuteNonQuery(strSql)
Next

oTran = oConn.BeginTransaction()
oCmd.Transaction = oTran
oCmd.CommandText = "UPDATE batch SET stock = t.stock FROM batch b INNER JOIN #tmp t ON b.[batchid] = t.[batchid]"
oTran.Commit()


-jhaith

 

[vijithamar]

Thanks you, Jhaith....


this is a new lesson for me..

I solved the locking problem. i created a index with pagelocking option with batchid. now it is working


Thanks

Rigards