HELP ME... RECORD LOCKING PROBLEM

[vijithamar]

HI


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

I AM USING .NET + SQL SEVER 2005 EXPRESS

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

 

[mrdenny]

That's the way that transactions work. You need to either remove the transaction or commit more often.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Crowley16]

if the other computers or processes don't need to write to that table, then you can get them to return data by making sure you use with (nolock, readuncommitted) options against the table in a select query.

--------------------
Procrastinate Now!

 

[SQLSister]

Maybe you should consider not using a loop. ANd perhaps I'm missing something, but since you don't seem to change the variables in the update with each loop, you seem to be running the exact same update repeatedly.

Perhaps you could give us an example of the data you have and the results you want and we could help you find a set-based (much faster) way to perform the action.

"NOTHING is more important in a database than integrity." ESquared

 

[vijithamar]

thanks for your replay

There are some code within the loop i omitted...

My problem is about locking. why all the records get locked once a single row is updated(when i=1). row level locking is not happing here...


thanks

 

[SQLDenis]

>>row level locking is not happing here...

maybe your index is not selective enough and the optimizer will escalate it to a table lock since it will appear to be 'cheaper' remember locks are expensive

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[vijithamar]

sorry... it was "happening"

Thanks... it was the problem with indexing. its working now

bye.