Bug #67760 Deadlock when concurrently executing prepared statements with Timestamp objects
Submitted: 29 Nov 2012 20:02 Modified: 4 Feb 2015 20:03
Reporter: Mathieu Mallet Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.22 OS:Any (Observed on Debian 64bits and Windows 64 bits)
Assigned to: Filipe Silva CPU Architecture:Any
Tags: connector, deadlock, gregoriancalendar, java, regression, timestamp

[29 Nov 2012 20:02] Mathieu Mallet
Description:
When a single Connection object is used to create multiple PreparedStatement objects and those PreparedStatement objects are executed concurrently, a deadlock can occur when multiple threads are making use of Timestamp objects (e.g. where one thread uses preparedStatement.setTimestamp(1, value) while another thread uses preparedStatement.getResultSet().getTimestamp(1))

When the deadlock occurs, the following stack traces and lock objects were obtained:

Thread-0
Lock name = java.util.GregorianCalendar@43e65667
Lock owner = Thread-1
Stacktrace:
com.mysql.jdbc.PreparedStatement.setTimestampInternal(PreparedStatement.java:4821)
com.mysql.jdbc.PreparedStatement.setTimestamp(PreparedStatement.java:4785)
com.apollo.atp.playground.TestsMySQLDeadlock$1.run(TestsMySQLDeadlock.java:76)

Thread-1
Lock name = com.mysql.jdbc.JDBC4Connection@4e731c20
Lock owner = Thread-0
Stacktrace:
com.mysql.jdbc.ResultSetImpl.fastTimestampCreate(ResultSetImpl.java:1065)
com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1352)
com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:127)
com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6591)
com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6191)
com.apollo.atp.playground.TestsMySQLDeadlock$2.run(TestsMySQLDeadlock.java:99)

This problem was observed on:
OS: Linux draco 3.4-5.towo-siduction-amd64 #1 SMP PREEMPT Tue Jul 17 06:47:47 UTC 2012 x86_64 GNU/Linux
JVM: Oracle JDK 1.7.0_09-b05
MySQL Connector/J: v5.1.22
MySQL server: 5.5.24+dfsg-9 (Debian package)

How to repeat:
The following sample Java code can be used to reproduce the problem. Just edit the source with a valid database URL, username and password. The database only needs SELECT access.

public class MySQLDeadlockSample
{
	private final static String url = "jdbc:mysql://localhost:3306/database_name";
	
	private final static String username = "myUsername";
	
	private final static String password = "myPassword";

	public static void main(String[] args)
	{
		try
		{
			System.out.println("Establishing connection...");
			Class.forName("com.mysql.jdbc.Driver");
			final Connection connection = DriverManager.getConnection(url, username, password);
			final Statement statement = connection.createStatement();
			
			System.out.println("Preparing statements...");
			final PreparedStatement pre1 =
					connection.prepareStatement("SELECT ?");
			final PreparedStatement pre2 = 
					connection.prepareStatement("SELECT '2012-11-29 13:23:45'");
			
			final int iterations = 1000;
			System.out.println("Executing statements sequentially...");
			for (int i = 0; i < 1000; i++)
			{
				pre1.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
				pre1.execute();
				
				pre2.execute();
				final ResultSet results = pre2.getResultSet();
				if (results.next())
					results.getTimestamp(1);				
			}
			System.out.println("No problems encountered.");
			
			System.out.println("Executing statements concurrently...");
			final Thread thread1 = new Thread() {
				@Override
				public void run() {
					try
					{
						for (int i = 0; i < iterations; i++)
						{
							pre1.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
							pre1.execute();
						}
						System.out.println("Thread 1 execution completed.");
					}
					catch (final SQLException e)
					{
						e.printStackTrace();
					}
				}
			};
			
			final Thread thread2 = new Thread() {
				@Override
				public void run() {
					try
					{
						for (int i = 0; i < iterations; i++)
						{
							pre2.execute();
							final ResultSet results = pre2.getResultSet();
							if (results.next())
								results.getTimestamp(1);
						}
						System.out.println("Thread 2 execution completed.");
					}
					catch (final SQLException e)
					{
						e.printStackTrace();
					}
				}
			};
			
			thread1.start();
			thread2.start();
			
			System.out.println("Waiting for threads to complete...");
			final Object o = new Object();
			final long end = System.currentTimeMillis() + 5000;
			synchronized(o)
			{
				while (System.currentTimeMillis() < end)
				{
					o.wait(100);
					
					if (!thread1.isAlive() && !thread2.isAlive())
						break;
				}
			}
			
			if (thread1.isAlive() && thread2.isAlive())
			{
				System.err.println("Likely deadlock detected.");
				
				final ThreadMXBean bean = ManagementFactory.getThreadMXBean();
				
				final ThreadInfo thread1Info = bean.getThreadInfo(thread1.getId());
				System.err.println("\n" + thread1.getName());
				System.err.println("Lock name = " + thread1Info.getLockName());
				System.err.println("Lock owner = " + thread1Info.getLockOwnerName());
				System.err.println("Stacktrace:");
				System.err.println(getStackTraceString(thread1.getStackTrace()));
				
				final ThreadInfo thread2Info = bean.getThreadInfo(thread2.getId());
				System.err.println("\n" + thread2.getName());
				System.err.println("Lock name = " + thread2Info.getLockName());
				System.err.println("Lock owner = " + thread2Info.getLockOwnerName());
				System.err.println("Stacktrace:");
				System.err.println(getStackTraceString(thread2.getStackTrace()));
				
				System.exit(2);
			}
			else
			{
				System.out.println("Execution completed successfully without deadlock.");
			}
		}
		catch (final Exception e)
		{
			e.printStackTrace();
			System.exit(1);
		}
	}

	public static String getStackTraceString(StackTraceElement[] elements)
	{
		if (elements == null)
			return null;
		
		final StringBuilder builder = new StringBuilder();
		boolean first = true;
		for (final StackTraceElement element : elements)
		{
			if (first)
				first = false;
			else
				builder.append('\n');
			builder.append(element);
		}
		
		return builder.toString();
	}
}
[29 Nov 2012 20:34] Sveta Smirnova
Thank you for the report.

Verified as described. Problem is not repeatable with version 5.1.20
[12 Dec 2012 20:33] Todd Farmer
Please do not use a single Connection object across multiple threads without proper synchronization.  Connector/J - and more importantly, the MySQL client-server protocol - does not allow for concurrent operations using the same Connection object.  If a Connection object must be shared across threads, it is the responsibility of the application code author to ensure operations are properly serialized.  

You may also have some limited success by adding the configuration option, useFastDateParsing=false.  The default value (true) optimizes behavior by creating only a single Calendar object for date conversions per Connection.  The deadlock reported is caused by different locking order when the optimized (single Calendar per Connection) is used.  Bypassing this optimization may provide temporary relief, but there is no guarantee that sharing a single Connection across multiple threads will produce the results you expect, and you should prioritize rearchitecture of how the application uses Connection objects.
[28 Jan 2013 22:26] Mathieu Mallet
Doesn't the JDBC spec require thread-safeness of the Connection objects? I understand that it's not required to allow concurrent execution of queries, but at the very least concurrent access shouldn't result in deadlocks.

Ref: http://docs.oracle.com/javase/1.3/docs/guide/jdbc/spec/jdbc-spec.frame9.html
[29 Jan 2013 15:08] Mark Matthews
Thread-safe does not necessarily imply deadlock-free. What it means is that you won't corrupt the state of the object. 

Why do you want to use a single prepared statement from multiple threads? They logically belong to a connection, which scopes the transactions. It seems you're heading for a world of heartache to share that amount of state (including locks and mutexes inside the database) that exists outside the JVM between threads (also, speaking from experience).
[29 Jan 2013 15:17] Mathieu Mallet
You're right about thread safety, though thread safety typically implies deadlock-free. 

If you look at my sample code, you'll see that a single prepared statement isn't being shared. Instead, two threads each use a separate prepared statement, executed through a single connection.

I changed my own code months ago to ensure that only a single thread can access a connection at a time. However I posted this to 
a) avoid headaches for other connector users, and
b) so that connector developers can take appropriate steps to rectify this
[15 Feb 2013 16:22] MySQL Verification Team
bug #68389 is a duplicate
[22 Feb 2013 19:03] Sveta Smirnova
Thank you for the feedback.

I will set it back to "Verified", because this is regression and would be better to Connector/J developers to have look at it.
[20 Dec 2013 3:51] Wu Liu
5.1.27, it still doesn't resolve the problem
[18 Jul 2014 13:32] Bas Goossen
Currently we are in version 5.1.31 and this problem still exists, on the other hand the bug report area is silent now for over a few months. What is the curent status. It is reported a serius bug, but no action seems to be taken. Last 10 days i had 5 server crashed due to this bug. so it seems even to have become worse with the newest version of the connector.

Could someone please report on the progress?
[13 Oct 2014 15:17] Alessandro Forlani
I add here a little analysis of a deadlock that seams to be related with this bug.
---
A deadlock happens in mysql-connector-java 5.1.23 (but checking the source of 5.1.33 it can still happen):

in ResultSetRow.java the method
protected Timestamp getTimestampFast(int columnIndex, byte[] timestampAsBytes, int offset, int length, Calendar targetCalendar, TimeZone tz,
            boolean rollForward, MySQLConnection conn, ResultSetImpl rs) 
synchronizes sessionCalendar and then calls rs.fastTimestampCreate which synchronizes connection.getConnectionMutex()

In PreparedStatement.java the method
public void setObject(int parameterIndex, Object parameterObj)
synchronizes connection.getConnectionMutex() and then calls 
public void setTimestamp(int parameterIndex, Timestamp x)
which calls
void setTimestampInternal(int parameterIndex, Timestamp x, Calendar targetCalendar, TimeZone tz, boolean rollForward) 
in which connection.getConnectionMutex() is synchronized again and then sessionCalendar is synchronized too.

In the call that start from  ResultSetRow.getTimestampFast()  sessionCalendar is synchronized first and connection.getConnectionMutex() is synchronized as second.
In the other call (from  PreparedStatement.setObject(int parameterIndex, Object parameterObj)) connection.getConnectionMutex() is synchronized first and  sessionCalendar as second.
This can cause (it's happened to me) a deadlock.
[31 Oct 2014 4:22] Meng Jiann Lee
Any update on this?
[4 Feb 2015 20:03] Daniel So
Added the following entry to the Connector/J 5.1.35 changelog:

"A deadlock occurred when concurrent prepared statements making use of timestamp objects were executed in a single connection. To avoid this, the locking mechanism involved has been changed, so that the calendar object for the session is only locked when it is actually being used."
[2 Mar 2015 4:20] Brian Johnson
The timeline for Connector/J 5.1.35 is not clear right now.  Can you release a patch file for us to test the fix?  I have seen this issue multiple times now.
[11 Nov 2015 18:29] Dev Gude
Seems to be fixed in 5.1.37 . I went crazy debugging this problem.
[3 Nov 2017 9:24] Sourav Badami
Using v5.1.39, this is still reproducible. Anyone facing the same ? Or having a solution ? @Filipe Silva ?
[9 Nov 2017 17:00] Filipe Silva
This bug was fixed in Connector/J 5.1.35.

Please try using the latest Connector/J version and if you still observe a wrong behavior, please file a new bug, referring to this one, and provide a reproducible test case so that we can look into it.

Thank you,