Deadlock when multiple users use same stored procedure
hi in our database we have used with(nolock). still we are facing deadlock is there any other option ? >Transaction was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. we are getting above mentioned error when multiple user try to use same sp this sp contain update and insert operation
Without seeing the code of the SP, or the details of the deadlock it is difficult to say. In the past I've seen similar situations when are doing an insert and update of the same table, where maybe there are indexing strategies that could be used to avoid deadlocking the index resource - but thats a guess. Using `with(nolock)` will have no effect on your updates and inserts - these still need to obtain locks to maintain data consistency.
A common issue that I see with the user of No Lock is how people think it is suppose to behave. I think the working of it gives people an impression that is different from what it really does. Not sure if this is part of the case here. If it sounds like this is the case, check out this out from msdn.
As Kev pointed out, nolock only affects reads. Any operation which does writing including inserts, updates, merges, and any DDL (like adding a column) will all still take locks. Generally, if two procedures are trying to get locks on the same table, the first one gets it and the second waits patiently. Deadlocks generally occur when there are two or more tables involved in the same transaction, in which case you can get a situation where neither transaction could ever finish without SQL's capacity to kill deadlocks. As others have said, knowing more about your procedure would help, but I would look for places in the code that would need a lock on more than one table at a time, such as explicitly created transactions. Also, as an aside, remember that using nolock can create dirty reads and inconsistent results. There are sometimes it is worth taking this risk, but it is a risk that should be taken with full awareness.
Enough already being said but just putting my 2 cents in... In such situations there are some general guidelines like - Make sure the database objects are accessed in the same order throughout - Do not use cursors. Set based solutions are generaly the best - Keep transactions as short as possible. Ensure that you are not performing the same reads over and over again. If your application does need to read the same data more than once or there is some complex logic with multiple joins, put the data in a temp table before starting an explicit transaction, and then re-reading it from there. You can also cache it in your application but it all depends. Since, as you said, the culprit is only one procedure, you can look into the possibilty of using application locks. If you are not familiar with this terminology, then you can visit the following articles