The lock_time
in the slow query log is actually the amount of time the query spent waiting to acquire the lock it needs to run. For example, UPDATES
queries need a write lock.
The locking also depends on the storage engine you are using in the table. When writing, InnoDB will use row-level locking and only lock the rows that are being changed. MyISAM will lock the entire table until the update/insert/delete is complete.
The Locking of the entire table for MyISAM is a big reason SELECT
queries will have a lock_time in your slow query log.
I see this query in your SHOW INNODB STATUS\G
CREATE TABLE 1_temp_foo AS
SELECT SQL_NO_CACHE
a.*
FROM
crm_companies AS a
LEFT JOIN users b ON a.zipcode = b.uid
LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id
LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id
LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number
ORDER BY a.country, a.name1
This query gives me the creeps because it combines three things you may not have thought of:
- InnoDB is involved based on your initial premise :
Using: MySQL 5.1.41 and InnoDB Tables
- MyISAM is also involved. Why is MyISAM involved? ALL INTERNAL TEMP TABLES ARE MyISAM !!! The resulting join is a MyISAM table that must be converted into InnoDB when the temp table has been populated. What is the default lock level for MyISAM tables? Table Level Locking.
- DDL is involved since a newly created table must be brought into existence. That new table would not be manifested until the temp table is populated, converted to InnoDB, and finally renamed
1_temp_foo
.
There is another side effect worth noting. When you do
CREATE TABLE tblname AS SELECT ...
The resulting table has no indexes.
I have something you might find helpful to bypass the locking issue. It involves making the table first as a separate query, then populating it. There are two options for making your temp table:
OPTION #1 : Try creating the table with the same layout
CREATE TABLE 1_temp_foo LIKE crm_companies;
This will create the table 1_temp_foo
to have the exact same indexes and storage engine as the original table crm_companies
.
OPTION #2 : Try creating the table with the same storage engine only, but no indexes.
CREATE TABLE 1_temp_foo SELECT * FROM crm_companies WHERE 1=2;
ALTER TABLE 1_temp_foo ENGINE=InnoDB;
After creating the table (whichever way you choose), you can now populate the table like this:
INSERT INTO 1_temp_foo
SELECT SQL_NO_CACHE a.*
FROM
crm_companies AS a
LEFT JOIN users b ON a.zipcode = b.uid
LEFT JOIN calc_base_materials c ON a.zipcode = c.material_id
LEFT JOIN calc_base_material_langtext d ON a.zipcode = d.material_id
LEFT JOIN crm_people e ON a.zipcode = e.telephone1_number
ORDER BY a.country, a.name
;
Now, this query should produced row-level locks for the sake of having data available for repeatable reads. In other words, this is a transactional query.
CAVEAT
OPTION #2 has advantages over OPTION #1
- Advantage #1 : If crm_companies has any foreign key constraints, OPTION #1 is not really possible. You would have to choose OPTION #2 for the sake of simplicity.
- Advantage #2 : Since OPTION #2 creates a table with no user-defined indexes, the table should load faster than if the table were made via OPTION #1.
Best Answer
I can't talk to postgresql directly. However, most rdbms in this situation would process the requests in the order they were received. That means a query can only execute if there is no other query that was submitted earlier and is either holding or waiting for a lock that is not compatible with the newly requested lock.
The reason is, that otherwise a query requesting an exclusive lock might never get executed ("starvation") if there is a high enough number/frequency of read requests coming in that keep sneaking by the waiting query.