Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1279234848.1229987384246.JavaMail.jira@brutus>
Date: Mon, 22 Dec 2008 15:09:44 -0800 (PST)
From: "Knut Anders Hatlen (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3980) Conflicting select then update with
 REPEATABLE_READ gives lock timeout instead of deadlock
In-Reply-To: <259072576.1228784504459.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-3980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658710#action_12658710 ] 

Knut Anders Hatlen commented on DERBY-3980:
-------------------------------------------

I think I see now why we have a problem with a lower deadlock timeout. Since the updater is supposed to be granted the lock after two seconds, we need to set the deadlock timeout to one second in order to trigger the deadlock detection. However, just before the updater has waited for one second, the first select thread releases its lock and wakes up all waiters. This early wakeup makes the updater check if it can obtain the lock, which it can't, but it won't do a deadlock check. It then goes back to sleep for another second, but before it wakes up to perform deadlock detection, it is granted the lock. (It looks like we allow five early wakeups before we reduce the time to wait for a deadlock. So for each early wakeup, the deadlock timeout is effectively increased. This is probably a good way to do it, since early wakeups indicate that it's probably not a deadlock yet.)

Easy workaround: Set the deadlock timeout to 1 second, and make the select threads hold the locks for 4 seconds instead of 3 seconds. Then the update thread will wait for two seconds before it gets the early wakeup (because the first select thread releases the lock), and by that time it will already have performed deadlock detection.

> Conflicting select then update with REPEATABLE_READ gives lock timeout instead of deadlock
> ------------------------------------------------------------------------------------------
>
>                 Key: DERBY-3980
>                 URL: https://issues.apache.org/jira/browse/DERBY-3980
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.3.0, 10.4.2.0, 10.5.0.0
>            Reporter: Kathey Marsden
>         Attachments: derby-3980_javadoc_and_test_diff.txt, derby.log, derby.log.10_1, javacore.20081209.092827.9800.txt, LiveLockTest_diff.txt, LiveLockTest_with_Deadlock_look_diff.txt, LockTimeoutWithInserts.java, TryTimeout.java, TryTimeout2.java, TryTimeout2.out.10_1.deadlock, TryTimeout2.out.10_1.deadlock, TryTimeout2.out.10_1.locktimeout, TryTimeout2.out.10_1.locktimeout
>
>
> 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.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.