Inserting new data results in a deadlock
I have a python script that executes 3 queries in 2 transactions on a MySQL database. Today I added an index to one of the used tables to resolve speed issues reported by the slow query log.
The problem is that the script fails quite fast since this change. It results in a deadlock:
This has happend before, but more like a few times per month, not 3 times in an hour (since the added index)
DatabaseError: (1213, 'Deadlock found when trying to get lock; try restarting transaction')
I have a few questions about this.
- How can this index (which does make the search part of the query much faster) be of any negative influence on performance?
- Does the index add extra load or locktime when you add data to the table?
I'll do some research on this, but maybe you can help:
- Because the two queries that deadlock are in different transactions. Does putting them in one transaction (for sequential excecution) change anything?
I'm a bit in the dark on this, so any help or suggestions are welcome.