Sql-server – happening with locks in the database

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.

What if some other transaction updates a record so that it would match with my WHERE clause in the UPDATE statement, thereby violating the REPEATABLE READ.

That would be SERIALIZABLE READ, not REPEATABLE. REPEATABLE only guarantees that the rows that qualified for the WHERE clause the first time will not disappear or be modified. It doesn't say anything about other rows qualifying in a second read.