[DERBY-3980] Conflicting select then update with REPEATABLE_READ gives lock timeout instead of deadlock

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.1.1
• Fix Version/s: 10.5.3.2, 10.6.2.4, 10.7.1.4, 10.8.1.2
• Component/s: Services
• Labels: derby_triage10_5_2
• Urgency: Normal
• Issue & fix info: High Value Fix
• Assignee: Knut Anders Hatlen
• Reporter: Katherine Marsden
• Created: 09/Dec/08 01:00
• Updated: 19/Apr/11 12:38
• Resolved: 14/Mar/11 16:47

Description

The attached program TryTimeout.java should detect a deadlock but instead throws a lock timeout exception. The program has two threads that attempt:

threadConnection.setAutoCommit(false);
/* set isolation level to repeatable read */
threadConnection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

ResultSet rs = stmt.executeQuery("select * from t where i = 456");
while (rs.next());
stmt.executeUpdate("update t set i = 456 where i = 456");
threadConnection.commit();

This gives SQLState 40001 (deadlock) with DB2 but a lock timeout with Derby.

Issue Links

• is duplicated by: DERBY-5073 Derby deadlocks without recourse on simultaneous correlated subqueries
• is related to: DERBY-2877 Print the entire lock list when a deadlock occurs and deadlock tracing is on
• relates to: DERBY-5137 Enable or remove Derby3980DeadlockTest