dropping index dead lock

[Alex O'Ree <al...@apache.org>]

I've got a process that creates a bunch of temporary indexes, then does
some work, then drops the indexes. All of these indexes are on different
tables but in the same database. During the drop the indexes phase, i
frequently get dead lock warning messages, each drop index statement seems
to be blocking the others. Strangely enough, i'm not running the drop index
statement in parallel. It's all on the same thread linearly.

I'm not super sure what's going on but i'm guessing drop index is creating
a database level lock. Is that the case? Regardless, is there anything else
i can do to mitigate against this?

Re: dropping index dead lock

[Rick Hillegas <ri...@gmail.com>]

Derby DDL is a heavy-weight operation. CREATE/DROP statements invalidate 
the in-memory metadata cache, effectively seizing a database-wide lock 
on the cache. Applications which perform a lot of DDL at steady-state 
will perform badly.

I don't have any better advice than to recommend that you avoid DDL at 
steady-state.

On 2/15/21 10:14 AM, Alex O'Ree wrote:
> I've got a process that creates a bunch of temporary indexes, then does
> some work, then drops the indexes. All of these indexes are on different
> tables but in the same database. During the drop the indexes phase, i
> frequently get dead lock warning messages, each drop index statement seems
> to be blocking the others. Strangely enough, i'm not running the drop index
> statement in parallel. It's all on the same thread linearly.
>
> I'm not super sure what's going on but i'm guessing drop index is creating
> a database level lock. Is that the case? Regardless, is there anything else
> i can do to mitigate against this?
>