I'm having some deadlock problems. I have a very large table (currently almost 1 700 000 rows), which is being filled with several thousand rows a day. This s done by a relatively simple stored procedure which also locks and modifies the data in another, smaller table, before it inserts into the big one.
Anyway, the users get deadlock error messages (esql = -243, eisam = -143) very often when the load on this procedure is highest.
I've been looking for the source of this deadlock, and couldn't find one. What I found out confuses me completely: the error occurrs BEFORE I lock anything, on the first try to lock a part of a table. How can this happen? By definition of a deadlock, I would have to lock something someone else wants first. I even set the locking mode for this table to row level, but it didn't help.
Yes, I know that, about the throughput, that's exactly what happened, but I couldn't find a reason for this in the settings. Finally, I had to remove a locking part from my stored procedure, which should theoretically be there, but I had no choice.
Now, there's a small probability of making an error in the inserted data (which can be repaired), but the performance is much better.
I still don't understand where the deadlock was, but it's a fact that every user was trying very often to lock a great part of the table, which had a terrible effect on performance.