4

I have a huge (poorly designed) InnoDB table with millions of records. When I query this guy the execution lasts several minutes. How can I ensure that during this time no other operations (queries, inserts or updates) are affected? The last thing I want is for deadlocks or timeouts for others while my query runs.

Here is the actual query..

SELECT html FROM cms_log where class_name ='main_pages' order by date_created desc;

Right now the field 'class_name' is not indexed but that is not something I can change at the moment.

This post suggested using

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

before running my query. But it is a rather old post and it only speaks of locks. Can someone confirm this is the way to go or give a better way to run the most harmless query possible? (also I don't care for dirty and phantom reads, I just don't want to impact other operations).

Community
  • 1
  • 1
AbuMariam
  • 3,282
  • 13
  • 49
  • 82
  • 1
    Can you set up a replica? Then you could query the replica without impacting the main database. – Dan Lowe Aug 10 '15 at 02:17
  • 3
    +If you don't mind pasting the query we could help you optimize it – FirebladeDan Aug 10 '15 at 02:18
  • @DanLowe makes a good point, which I'll expand on by nothing that additional indexes in a slave (where the same index does not exist on the master) is a supported, valid configuration, except of course for unique indexes, which would not make sense to have in one place but not the other... but the importance of appropriate indexing on the master can't be emphasized enough. – Michael - sqlbot Aug 11 '15 at 00:04

2 Answers2

1

So it looks like this is the way to go..

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Executing this before the query does seem to ensure no other operations get the dreaded "lock wait timeout exceeded" while this long query executes.

AbuMariam
  • 3,282
  • 13
  • 49
  • 82
0

I disagree with the link you posted. Setting the isolation level to READ UNCOMMITTED should not make your select queries faster / affect any other queries differently.

A pure select like yours in InnoDB should not be locking anything (aside from their own internal data structures).

In general, a SELECT can lock for read, write, or none. depending on the context. In this context, where you have not explicitly requested a lock, and you aren't updating anything, no row lock should be used.

This is assuming you're normally running READ COMMITTED or REPEATABLE READ isolation level.

Erix
  • 7,059
  • 2
  • 35
  • 61
  • I disagree with your disagreement. :) I have never worked out what the mechanism is, but I know for certain that I have encountered a confusing "lock wait timeout exceeded" in "thread 2" while "thread 1" was executing a long-running, non-locking select, from inside a transaction that should have held no locks. Changing "thread 1" to use `READ UNCOMMITTED` resolved this issue, which was a daily irritant up until then. InnoDB does seem to do *something* that causes other threads to wait on writes, when a long-running `SELECT` is still in progress, at other isolation levels. – Michael - sqlbot Aug 10 '15 at 23:59