db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6837) ERROR 40XL1 due to incorrect lock
Date Thu, 24 Sep 2015 04:11:04 GMT

    [ 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)

Mime
View raw message