2

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!

  • Please include the output of SHOW CREATE TABLE User. I am assuming UserID is the primary key here? – Morgan Tocker May 8 '14 at 17:08
2

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".

0
+100

I have found the answer in the meantime:

It has to do with the MySql version - the newest MySql version has a number of improvements in the concurrency area which, unfortunately, I could not use at the moment. Probably, if it is configured properly, it will work very good but for me reverting to a older version fixed it.

  • What version(s) are you pointing to? – Tuukka Mustonen Mar 3 '17 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.