database locks in update query

Question

i want your help. I have table that the select queries in the same second exceed the 1000. When i am trying to make an update in one record the 'select queries' stop until the update is made. This lasts up to 2-5 minutes. It seems that there are some locκs in this table. Can you help me?

Thank you, bk

Yes, very likely this is because of lock. You need to provide following information, then people can give right answer. What is your transaction isolation level, what does your select query look like, what is your table DDL, how many index do you have, what is your update statement etc. You can also check the DMV(sp_who2) by your own, see what blocked your update query? What have you tried? — ljh, Mar 29 '13 at 22:39

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

What kind of TransactionOption are you running your Select query under? Also look in to using NO LOCK in your select query if you are okay to read uncommitted data. Look into this thread Effect of NOLOCK hint in SELECT statements

edited May 23 '17 at 11:49

Community

1
1

answered Mar 29 '13 at 22:19

amassani

91
3

You should *never* use NOLOCK in production code. In addition to getting uncommitted data, you run the risk of *not* getting committed data. See http://stackoverflow.com/a/7355121/806549 – Mar 29 '13 at 22:28
That is exactly what I said, please read my comment carefully before commenting. "If you are okay to read uncommitted data". – amassani Mar 31 '13 at 20:53
Your statement remains incorrect. The correct comment should have been "If you are okay to read uncommitted data and perhaps not get committed data." – Mar 31 '13 at 22:13

score 0 · Answer 2 · answered Mar 29 '13 at 22:34

0

perhaps your update does require some additional field indexing. Also, consider splitting the update in parts.

answered Mar 29 '13 at 22:34

koriander

3,110
2
15
23

database locks in update query

2 Answers2