Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a large (160 million rows, 50GB) table in MySQL 5.1 using MyISAM.

If I were to construct a view defined by a fairly complicated query, would a SELECT on the view lock the underlying table?

share|improve this question
add comment

2 Answers

Keep in mind that SELECTs and UPDATEs are mutually exclusive for MyISAM. That's is to say...

  • UPDATEs, DELETEs, and INSERTs require a full table lock. SELECTs are blocked.
  • SELECTs prevent subsequent UPDATEs, DELETEs, and INSERTs from acquiring a full table lock.

Using either SELECT or UPDATE automatically mitigate MyISAM locking behavior, anyway. So, the answer to your actual question would be yes since we are dealing with MyISAM.

I would recommend you use LOCK TABLES on the view. Why ?

According to the MySQL Documentation

For view locking, LOCK TABLES adds all base tables used in the view to the set of tables to be locked and locks them automatically. If you lock a table explicitly with LOCK TABLES, any tables used in triggers are also locked implicitly, as described in Section 13.3.5.2, “LOCK TABLES and Triggers”.

Based on this, you should explicitly run LOCK TABLES on the view. The locks are performed on all the underlying tables then. That way, all the tables are locked as a group.

SUGGESTIONS

  • Make the all the underlying tables InnoDB if you want and if you can.
  • INSERTs to the underlying MyISAM tables can still happen if concurrent_insert is set to 2.
share|improve this answer
    
I was rather hoping that in fact it wouldn't lock the underlying tables. –  Charlie Martin Feb 10 at 20:46
    
As well as as concurrent_insert, INSERT DELAYED is useful if you can run asynchronous. Keep in mind that none of these options will work if you use IODKU syntax. –  Tom Desp Feb 10 at 22:15
add comment

I'm making the assumption that you are hoping that it won't, but yes, the view will lock the underlying tables in the same way that the query inside the view would lock the tables.

However... this is a case where defining the view with the TEMPTABLE view algorithm could be beneficial.

Views in MySQL can be processed with one of two algorithms... MERGE -- which merges the view definition into the query that references the view, and can take advantage of indexes on the underlying tables that are indirectly referenced by the outer query... and TEMPTABLE -- which materializes the result from the view into a anonymous temporary table, which is accessed by the outer query but is blind to anything in the outer query that might have been useful in optimal processing of the view.

If you don't select an algorithm when you define a view, the optimizer will try to use MERGE if the view definition is compatible with MERGE and otherwise will use TEMPTABLE. Usually, MERGE is a far more desirable algorithm when it's available, because of the way it preserves the usefulness of indexes, but:

A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.

http://dev.mysql.com/doc/refman/5.1/en/view-algorithms.html

Presumably, though not stated, this could theoretically also result in not blocking other clients using the underlying table(s) (other than those using the view) for as long... but, by design, the views do lock the MyISAM tables against which they are defined.

share|improve this answer
    
I was indeed. Unfortunately creating the temp table on this table takes upwards of an hour so I suspect there are other annoyances to this approach. –  Charlie Martin Feb 10 at 20:51
    
Other than my preferred plan, which would be "use InnoDB," depending on what you're needing to accomplish, you might find it useful to set up a slave server to run these long-running selects. MySQL also does support using different storage engines for the same table for master and slave. Or, if really you need to access the view from the master and the actual amount of data returned from the view isn't massive, you could create the view on the master, let the definition replicate, then create a federated table on the master that would directly access the view on the slave (which does work). –  Michael - sqlbot Feb 10 at 22:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.