I attempted to read an essay about locks and deadlocks, but it simply didn’t sink in, all the many types of locks did. We’re running two processes that both attempt to edit records in the same table. The first process reads the new data and transmits it to an external party, updating the status appropriately; the second process gets the external party’s results and changes the status correspondingly.
We are increasingly encountering deadlocks (in which case one of the two is the victim and gets aborted).
So far, so good because you can predict it and try to redo the statement, but the same impasse happens every time. So, first and foremost, why does the same impasse always reoccur?
Second, is there a method to tell the database not to try to gain an exclusive hold on a record (we update single records through a stored procedure) while another process is already reading and updating it, but instead to ‘wait at the side’ till the other process is ready? Isn’t it a far too simplistic way of describing a deadlock?
Third, is there a method to ask LINQ to SQL which locks were causing the difficulty, so I can gain a better understanding of which sections of the process are creating the problem?