Debugging MySQL Deadlocks on Amazon RDS

Question

I'm getting an increasing number of "Deadlock" and "Lock timeout" issues on my MySQL database (an Amazon RDS instance). The database was working very well until a couple of days ago. No changes to the server code (that executes the queries) were made for more than a week. I know what the exceptions are, but I'm at a loss as to the best way to debug and find the specific queries/statements that are causing the issues.

Info on setup:

• MySQL Amazon RDS instance (small)
• Everything default in RDS profile/MySQL settings except for character encoding, which is changed to UTF-8
• Server making the queries is Tomcat Java server with Hibernate
• Information on exceptions is read from the Tomcat server log
• Queries / Min between 50 - 5000

My best guess is there's something in the settings (either Hibernate or MySQL) that's causing this issue. The server will run fine and then latency will go through the roof causing all sorts of negative behavior. When this happens, the CPU utilization of the instance will be near 100%.

The hibernate settings are:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass"><value>com.mysql.jdbc.Driver</value></property>
    <property name="jdbcUrl"><value>XXXX</value></property>
    <property name="user"><value>XXXX</value></property>
    <property name="password"><value>XXXX</value></property>
    <property name="initialPoolSize"><value>3</value></property>
    <property name="minPoolSize"><value>3</value></property>
    <property name="maxPoolSize"><value>50</value></property>
    <property name="idleConnectionTestPeriod"><value>200</value></property>
    <property name="acquireIncrement"><value>1</value></property>
    <property name="maxStatements"><value>0</value></property>
    <property name="numHelperThreads"><value>6</value></property>
</bean>

Any idea where to start debugging of if something in the settings might be causing this problem would be appreciated.

Answer 1

I know it hard to locate the problem but you can start with locate the query that cause a deadlocks using:

SHOW FULL PROCESSLIST

MYSQL Reference

Hope this will help you to start