[jira] [Commented] (DERBY-6837) ERROR 40XL1 due to incorrect lock

["Mike Matrigali (JIRA)" <ji...@apache.org>]

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

Mike Matrigali commented on DERBY-6837:
---------------------------------------

if you can not reproduce, please add to your problem description about the transactions/connections involved by the
one doing the index creation and the one doing updates.  As a rule of thumb it is best when you do any type of ddl (like
index creation) to do it in an "autocommit" transaction to avoid holding table level locks and avoid deadlocks.  autocommit
will insure that the transaction commits and immediately releases the locks after the index creation.   You should avoid trying
to do index creation while any other activity is happening in the database on that table, it will try to lock the table exclusively.

Often users are confused by the TABLE locks as you describe above.  Both of them in 4816 and 4756 are "intent" locks indicating
that the system is doing row level locking and will acquire S and X row locks as necessary.  In 4816 there is likely no rows in the table
and thus no row locks.  in 4756 it looks like one row has been read in a transaction and still holds it while whatever you did to
see the locks.



> ERROR 40XL1 due to incorrect lock
> ---------------------------------
>
>                 Key: DERBY-6837
>                 URL: https://issues.apache.org/jira/browse/DERBY-6837
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.1.1
>         Environment: Windows 7 - Java jdk1.6.0_24 x86
>            Reporter: Jean-Yves LINET
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)