[jira] Issue Comment Edited: (DERBY-2991) Index split deadlock

["Tomohito Nakayama (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-2991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12542746 ] 

naka edited comment on DERBY-2991 at 11/15/07 4:07 AM:
--------------------------------------------------------------------

Hello.

Reading the code of Repro program, 
I found the situation that insert thread inserts *multiple rows* in a transaction for times 
while select thread selects *rows in a range condition* results in scanning index for same table.

I think the problem is that
inserting multiple row in a transaction make multiple locks for data rows and index rows ,
and then, 
those locks causes deadlock between selecting same table,
because operation of selecting rows for range condition also needs locks for data rows and index rows in scanned range.


I think there exists two ways to walk around for this situation.

1:
Do not insert multiple rows in a transaction, 
when selecting rows of same table in a range condition simultaneously, 
as the Repro program.
I tried changing tracksPerBatch as 1 and 
the deadlock problem of Repro program was resolved.

2:
Loosen transaction isolation level.
I tried conn.setTransactionIsolation( Connection.TRANSACTION_READ_UNCOMMITTED ) and 
the deadlock problem of Repro program was resolved.


I'm not sure those walk around is suitable for your case, 
but I hope it helps you.


I also think feature like hint for sql may be helpful in those case.
If we can suppress the use of index for select operation with hint, 
we could also escape the problem using that feature.

      was (Author: naka):
    Hello.

Reading the code of Repro program, 
I found the situation that insert thread inserts *multiple* rows in a transaction for times 
while select thread selects *rows in a range condition* results in scanning index for same table.

I think the problem is that
inserting multiple row in a transaction make multiple locks for data rows and index rows ,
and then, 
those locks causes deadlock between selecting same table,
because operation of selecting rows for range condition also needs locks for data rows and index rows in scanned range.


I think there exists two ways to walk around for this situation.

1:
Do not insert multiple rows in a transaction, 
when selecting rows of same table in a range condition simultaneously, 
as the Repro program.
I tried changing tracksPerBatch as 1 and 
the deadlock problem of Repro program was resolved.

2:
Loosen transaction isolation level.
I tried conn.setTransactionIsolation( Connection.TRANSACTION_READ_UNCOMMITTED ) and 
the deadlock problem of Repro program was resolved.


I'm not sure those walk around is suitable for your case, 
but I hope it helps you.


I also think feature like hint for sql may be helpful in those case.
If we can suppress the use of index for select operation with hint, 
we could also escape the problem using that feature.
  
> Index split deadlock
> --------------------
>
>                 Key: DERBY-2991
>                 URL: https://issues.apache.org/jira/browse/DERBY-2991
>             Project: Derby
>          Issue Type: Bug
>          Components: Store
>    Affects Versions: 10.2.2.0, 10.3.1.4
>         Environment: Windows XP, Java 6
>            Reporter: Bogdan Calmac
>         Attachments: derby.log, InsertSelectDeadlock.java, Repro2991.java, stacktraces_during_deadlock.txt
>
>
> After doing dome research on the mailing list, it appears that the index split deadlock is a known behaviour, so I will start by describing the theoretical problem first and then follow with the details of my test case.
> If you have concurrent select and insert transactions on the same table, the observed locking behaviour is as follows:
>  - the select transaction acquires an S lock on the root block of the index and then waits for an S lock on some uncommitted row of the insert transaction
>  - the insert transaction acquires X locks on the inserted records and if it needs to do an index split creates a sub-transaction that tries to acquire an X lock on the root block of the index
> In summary: INDEX LOCK followed by ROW LOCK + ROW LOCK followed by INDEX LOCK = deadlock
> In the case of my project this is an important issue (lack of concurrency after being forced to use table level locking) and I would like to contribute to the project and fix this issue (if possible). I was wondering if someone that knows the code can give me a few pointers on the implications of this issue:
>  - Is this a limitation of the top-down algorithm used?
>  - Would fixing it require to use a bottom up algorithm for better concurrency (which is certainly non trivial)?
>  - Trying to break the circular locking above, I would first question why does the select transaction need to acquire (and hold) a lock on the root block of the index. Would it be possible to ensure the consistency of the select without locking the index?
> -----
> The attached test (InsertSelectDeadlock.java) tries to simulate a typical data collection application, it consists of: 
>  - an insert thread that inserts records in batch 
>  - a select thread that 'processes' the records inserted by the other thread: 'select * from table where id > ?' 
> The derby log provides detail about the deadlock trace and stacktraces_during_deadlock.txt shows that the inser thread is doing an index split.
> The test was run on 10.2.2.0 and 10.3.1.4 with identical behaviour.
> Thanks,
> Bogdan Calmac.

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