UPDATE and SELECT at the same time (locked)

Asked

Viewed 307 times

2

Monitoring the database (Mysql) through Monyog I realize that when some very large query is executed (SELECT) and, at the same time, an update query also runs, under the same table, the update is waiting for SELECT to finish.

The result of this is that the table is locked waiting (locked). And with this, all other query’s under this table (currently executed) do not run until finished, thus generating a major system crash.

How to solve this problem?

mysql optimizing

  • After performing a search, I understood that this is a standard behavior of the Myisam engine. That is, I should try to optimize my query’s even more so that they don’t have a high time. Correct?

  • 1

    Myisam is a disused engine, I recommend changing the engine to Innodb. If this is not possible, put EXPLAIN before your SELECT to see what is impacting the query :)

1 answer

1

Use SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; to enable the NOLOCK.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- sua query...
COMMIT;

To make this feature count for all queries, put the following line in your Mysql configuration:

[mysqld]
transaction-isolation = READ-UNCOMMITTED
  • 1

    Right Rodrigo. What will be the impact of this change in a medium-sized system? My concern is the following: An UPDATE will be performed in the table. The SELECT performed is a calculation under this table. And then, as it is?

  • 1

    I only know this feature in theory, I never had to use it. However, for a large system, I would consider splitting Mysql machines into slave and master machines in order to distribute the load between them.

  • I got it, Rodrigo. I added a comment to my reply. Thank you!

Browser other questions tagged mysql optimizing

You are not signed in. Login or sign up in order to post.