Can additional indices ever aggravate lock contention?

Question

Not considering index creation: Can it ever be disadvantageous to create an index on a table with regards to lock acquisition?

I have experienced already some cases where a carefully selected index has enabled much higher parallelism in a database, as is expected by (and documented for) MySQL's use of index (gap) locking.

But is the opposite possible as well, i.e. two or more queries from different transactions that will not get in each other's way without some index, but which given an additional index will incur additional waiting on a lock?

My question is not limited to a single RDS implementation, but I am most interested about MySQL and Postgres.

Answer 1

Speaking for Postgres.

In default READ COMMITTED transaction isolation, indexes don't introduce the possibility for deadlocks where there wouldn't be without them - AFAIC. It's still possible to experience deadlocks only after creating a new index. Changed query plans can make lurking design problem surface as side effect, but not as immediate fault of the index.

Creating or deleting indexes is special, quoting the manual:

An exclusive lock on the index as a whole will be taken only during index creation, destruction, or REINDEX.

And there are corner cases with SERIALIZABLE transaction isolation. See the last paragraph of chapter "Index Locking Considerations" in the manual. (Or read it all for details.)

But indexes can certainly aggravate lock contention in various ways. Obviously, additional indexes incur additional cost for write operations (indexes have to be maintained). There are other corner cases that might impair performance with a similar effect. Longer transactions mean more lock contention, since locks are only released at the end of transactions.

For large inserts or updates it can pay to delete unrelated indexes and recreate afterwards (in the same transaction) for better performance - but at the cost of an exclusive lock on the table. Example:

• Best way to populate a new column in a large table?

Typically, though, if you only create indexes you actually need, the opposite is the case: shorter transactions, less lock contention.

Related:

• Deadlock best practices

Answer 2

But is the opposite possible as well, i.e. two or more queries from different transactions that will not get in each other's way without some index, but which given an additional index will incur additional waiting on a lock?

...

My question is not limited to a single RDS implementation, but I am most interested about MySQL and Postgres.

Well, in the general case the answer is "yes, it probably can" since you're talking about a broad set of databases.

---

For PostgreSQL specifically, adding indexes definitely hurts performance for writes:

• Each index needs updating whenever you insert a tuple
• Each index needs updating when you UPDATE a tuple if any column that is changed is part of one or more indexes.

PostgreSQL can skip index updates if you perform an UPDATE that doesn't touch any indexed columns. So adding an index might greatly hurt write performance.

However, it won't add any significant locking since PostgreSQL doesn't have to lock an index exclusively, it permits multiple concurrent reads and writes to the same index.