db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3980) Conflicting select then update with REPEATABLE_READ gives lock timeout instead of deadlock
Date Wed, 10 Dec 2008 22:38:44 GMT

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

Kathey Marsden commented on DERBY-3980:
---------------------------------------

I am debugging this on 10.1 since at least there the Lock table showed two waiters on lock
timeout.
I changed the test case a bit to have one of the threads sleep for 10 seconds after the select
and also to have the program dump the lock table every ten seconds.  I found that after adding
the sleep I usually get a deadlock instead of the timeout.
Expected exception:A lock could not be obtained due to a deadlock, cycle of locks and waiters
is:
Lock : ROW, T, (1,7)
  Waiting XID : {254, U} , DB2ADMIN, delete from t  where i = 456
  Granted XID : {256, S}
Lock : ROW, T, (1,7)
  Waiting XID : {256, X} , DB2ADMIN, delete from t  where i = 456
  Granted XID : {256, S} , {254, S}

but still I sometimes get the lock timeout.  I also saw that at the time of deadlock checking,
the lock tables for both the lock timeout run and the deadlock run show the same lock table
which should deadlock.
xid     |username|trantype|type    |cnt|mode|tabname     |lockname  |state|status
---------------------------------------------------------------------------------
279     |UserTran|TABLE   |1  |IS  |T           |Tablelock |GRANT|ACTIVE
281     |UserTran|TABLE   |1  |IS  |T           |Tablelock |GRANT|ACTIVE
279     |UserTran|TABLE   |2  |IX  |T           |Tablelock |GRANT|ACTIVE
281     |UserTran|TABLE   |2  |IX  |T           |Tablelock |GRANT|ACTIVE
279     |UserTran|ROW     |1  |S   |T           |(1,7)     |GRANT|ACTIVE
281     |UserTran|ROW     |1  |S   |T           |(1,7)     |GRANT|ACTIVE
279     |UserTran|ROW     |0  |U   |T           |(1,7)     |WAIT|ACTIVE
281     |UserTran|ROW     |1  |U   |T           |(1,7)     |GRANT|ACTIVE
281     |UserTran|ROW     |0  |X   |T           |(1,7)     |WAIT|ACTIVE

Mike mentioned to me that he thought it might be a problem with both threads doing the deadlock
check at the same time, but  it seems like with the sleep that couldn't be the problem.  Let
me know if you would like me to post the updated program.


> 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.log, derby.log.10_1, javacore.20081209.092827.9800.txt, TryTimeout.java
>
>
> 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.


Mime
View raw message