I have been handed an Excel document-program-thing to work with, which saves and loads its data to a MS SQL database. Seeing how I am not an expert on either Excel or databases, I have been having a bit of a bother with a deadlock problem that occurs when two users save their data to the database at the same time. To be honest I don't see how the code for saving to the database can result in a deadlock.
I won't post the entire part of code, because it's long, but I can describe its structure. It consists of many (perhaps 30) simple UPDATE statements of various length, updating perhaps 5 to 20 fields each in different (perhaps 15) single tables. The updates are made in table-order, so that if one table is updated with several UPDATE statements they all come one after another. No explicit transactions are used.
If two users run this code, sending the sequence of UPDATE statements to the database, how can it result in a deadlock? Because it does, and from what I have seen, it does so every time I start two save processes simultaneously. Obviously I have not understood well enough the nature of deadlocks and when they occur.
I have noticed that some table lack proper indexes or key fields, can such shortcomings result in deadlocks? Overall the database is of a poor relational design.
If anyone have an idea of what I could look at, I would be very happy to hear it. Because I have run out of things to try myself.
Thank-you.