patternsqlMinor
Can additional indices ever aggravate lock contention?
Viewed 0 times
indicescontentioncanaggravateeveradditionallock
Problem
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.
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.
Solution
Speaking for Postgres.
In default
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
And there are corner cases with
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:
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
Context
StackExchange Database Administrators Q#174251, answer score: 4
Revisions (0)
No revisions yet.