Mysql – Can additional indices ever aggravate lock contention

indexlockingMySQLpostgresql

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.

Best Answer

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:

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

Related:

Related Question