Table contention
Hi there our application has multiple process paths that run at random times - but constantly...
Several of these processes write to the same table and at times we're seeing failed DB transactions. I assume it is due to DB contention - because if I stop 2 of the three processes - the single one runs perfectly.
I am after some advice on how I might go solving the issue.
I am tempted to lower the isolation level to READ COMMIT. But will this solve the issue? And do I do it just in the SQL transaction in my application code? Session or global?
And to complicate matters, perhaps... is that we use an ORM in our apllication code.
As always - thanks very much for your help!
Answer Answered by Daniel Black in this comment.
What constitutes a failed transaction? A deadlock?
Looking at SHOW ENGINE INNODB STATUS can report the latest deadlock. Does this look like the processes you are talking about?
READ COMMITTED can help will some, but not all forms of deadlock. As can specific indexing to ensure a minimial number of records are locked at the same time.
Also handling deadlock exceptions in your application code is a good way of doing it. The deadlock handling depends on the application which is why the database doesn't do this automaticly.
