I normally hang around in StackOverflow but I think this issue is better suited here.
I've built an application in C#.NET that uses MySql as a database.
My application inserts and updates data in the database in a using the Parallel API (so multithreaded). For reference, the queries that are being fired multithreaded would look like:
Update User set IsActive = 1 where UserID = 1 --each thread updates a different user id
This has always worked on servers that I manage and development machines.
We recently migrated the MySql database to Amazon RDS, however. Since then, if I run this query multithreaded (even with as low as two concurrent threads), it throws an exception:
Deadlock found when trying to get lock; try restarting transaction
Any experience with this issue? Are there some settings in Amazon RDS which are configured differently from a default MySql installation?
Thanks in advance!
Best Answer
The short answer is no: I can't think of a difference from MySQL defaults that would cause this.
The longer answer is that it is possible to reduce some locking with READ-COMMITTED as the isolation level + Row-based Replication. To which RDS does not support switching to Row-based Replication :(
Whether or not this reduction in locking will help you depends on your schema. See my blog post here under "Write scalability of certain statements".