Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having some problems on my database. At any given time, I'm trying to execute an update with five records on my table, but this table is locked and I get a time out executing the query.

I realized that if I execute update in only four records, the statement run sucessfully. And when I try to update the five recods together, it doesn't work.

Example:

--ONLY LOCKS IF THE UPDATE IS IN ALL FIVE RECORDS. (any combination works fine)

UPDATE X SET STATUS = 1 WHERE ID IN (1,2,3,4,5) -- THIS LOCKS THE UPDATE!!

UPDATE X SET STATUS = 1 WHERE ID IN (2,3,4) -- THIS  DOESN'T LOCK AND RUN SUCESSFULLY!

UPDATE X SET STATUS = 1 WHERE ID IN (1,4,5) -- THIS  DOESN'T LOCK AND RUN SUCESSFULLY!

UPDATE X SET STATUS = 1 WHERE ID IN (1,2,3,5) -- THIS DOESN'T LOCK AND RUN SUCESSFULLY!

Does anyone have an idea to how I fix this situation?

EDIT 1: My isolation level is Read Commited.

share|improve this question
    
Is there a trigger running on UPDATE? –  Michael Perrenoud Jan 13 at 13:22
1  
Have you tried the WITH(NOLOCK) hint on the other thread? –  user3056839 Jan 13 at 13:33
2  
@LucasAbilidebob Locking is totally normal and happens every time data is changed or read. There are different types of locks with different behavior based on what the engine needs to do. This may be due to lock escalation. How big is your table? You can also try using the ROWLOCK hint to force row-level locking. –  JNK Jan 13 at 13:39
2  
There are very long whitepapers written on this so I can't give you a good summary in a comment here. Basically, when the DB locks a row, it sometimes ESCALATES the lock to a page or extent or table depending on how much of the object it locks. It's more efficient to track one table lock than 2 million row locks. If your table is small it may be escalating to a table lock when it gets to 5 rows. The ROWLOCK hint forces row-level locking which is less efficient for large scale operations. –  JNK Jan 13 at 13:45
3  
Also please do NOT put NOLOCK into production code. There are a lot of issues with using that hint, not least of which if you are changing a record that is being read and you use NOLOCK the reader may get wrong data depending on the timing. –  JNK Jan 13 at 13:54
show 9 more comments

migrated from stackoverflow.com Jan 13 at 16:25

This question came from our site for professional and enthusiast programmers.

1 Answer

When I run sp_lock, I realized that two locks in my table: PAG and KEY. Searching more about KEY LOCK, I created an index for the column on the where clause, and this solve my problem.

EDIT: When I removed the where clause (without index) , it worked fine too.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.