CODEWITHSUNDEEP
mysqldatabase
Sharon Haim Pour
Sharon Haim Pour

Reputation: 6713

mysql - Long query execute time locks table

I have a mysql database which collects data from all kinds of resources.
In order to show some statistics from different tables, I run a long query which takes about a couple of minutes to execute.
The query locks some tables and therefore I can't write to these tables.
The tables are InnoDB engine. I tried several options including:
1. Increasing the lock timeout
2. Adding SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; and COMMIT; (link)
3. Adding indexes to the tables (made the query run faster, but I still get a lock timeout error)

How can I execute the long query without locking the tables?

Upvotes: 3

Views: 2499

Answers (1)

Russell Uhl
Russell Uhl

Reputation: 4531

From the comments in Any way to select without causing locking in MySQL? :

Setting "set session transaction isolation level read committed;" did not help.

Setting "set session transaction isolation level read uncommitted;" did not help.

Using lock tables on either side did not help.

Setting innodb_locks_unsafe_for_binlog=1 in my.cnf fixes the problem, but I guess the downside is that now I will not be able to use replication in this database.

If you don't need replication, then I would try that.

Upvotes: 1

Related Questions