Re: Deadlock in multiple CIC.

>
> Since the purpose of CIC is to build an index with minimal impact on other
> users, I think wanting to use it in concurrent cases might be rather rare.
> In a maintenance window, I wouldn't want to use CIC because it is slower
> and I'd rather just hold the stronger lock and do it fast, and as a hot-fix
> outside a maintenance window I usually wouldn't want to hog the CPU with
> concurrent builds when I could do them sequentially instead. Also, since
> deadlocks are "expected" errors rather than "should never happen" errors,
> and since the docs don't promise that you can do parallel CIC without
> deadlocks, many people would probably shrug it off (which I initially did)
> rather than report it as a bug. I was looking into it as an enhancement
> rather than a bug until I discovered that it was already enhanced and then
> undone.
>
>
FWIW, yes I agree it is a rather rare use case. For me, it's for doing
concurrent index builds on logical replica tables especially after initial
copy with non-indexed tables, where replicated tables may have traffic
coming in constantly. That means DBA doesn't have to wait for hours for
them to build 1 by 1, and also doesn't have to worry about long locks.

However IIRC, we have also run into deadlocks before when trying to build
multiple indexes in an OLTP system, which may have been due to this issue
as opposed to only trying to index the same table.