[DERBY-6837] ERROR 40XL1 due to incorrect lock

• Type: Bug
• Status: Closed
• Priority: Trivial
• Resolution: Not A Problem
• Affects Version/s: 10.11.1.1
• Component/s: SQL
• Environment: Windows 7 - Java jdk1.6.0_24 x86
• Bug behavior facts: Crash
• Assignee: Unassigned
• Reporter: Jean-Yves LINET
• Created: 23/Sep/15 08:08
• Updated: 09/Sep/16 09:53
• Resolved: 24/Sep/15 21:03

Description

When I add an index on new created table (software installation) I get ERROR 40XL1 when I try to update records.
If I add the same index on software update when table has been used, I don't have the error.
On a new installation I have to insert rows and restart my application, before adding the index, and then I don't have the error.
I tried to make a small project to reproduce the bug but after two days of research I gave up.
Anyway I have some interesting data, I think.
When it is working I have only one lock on the table (SYSCS_DIAG.LOCK_TABLE) :
4816 - TABLE - IS - IDAXX_SIS - Tablelock - GRANT - T - 1 - null
When I have the error there is another lock on rows :
4756 - TABLE - IS - IDAXX_SIS - Tablelock - GRANT - T - 1 - null
4756 - ROW - S - IDAXX_SIS - (1,28) - GRANT - T - 1 - null

The index column is a CHAR(1).
I use embeddeddriver with "territory=fr_FR; collation=TERRITORY_BASED:PRIMARY" option, but I tried without this option and the error persist.

For now my workaround is to delay the index creation.