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
A single statement like that works the same with MyISAM or InnoDB, with a transaction or with autocommit=ON. It blocks enough to do the query, thereby blocking the other connection. When finished, the other connection proceeds. In all cases, the column is soon decremented by 11.
A third user may see the value decremented by 0 or 4 or 7 or 11. The "very exact time" is not really possible because, at some point in the execution of each statement, a single-threaded lock is checked/set/whatever. That is, they will be serialized, just so fast that you can't see it.
InnoDB locks only rows, not tables. (OK, DDL statement do bolder locks.)
What gets more interesting is a transaction that modifies two things, or that takes a noticeable amount of time:
Intention Case: Single item but taking time:
The select needs to be written thus:
This tells other connections "I intend to update the row; please don't mess me up". (I bring up this example, because a lot of newbies miss this subtlety.)
Deadlock case: Messing with 2 things:
This is the classic example of a deadlock -- each grabs one thing and then reaches for the other thing. Clearly it can't be made to work. One transaction is killed; the other completes. Hence, you must check for errors, so you can discover it.
The normal reaction to a deadlock is to replay the entire failed transaction. By then, the other connection will not be interfering, and it should proceed without trouble. (OK, yet another connection could create another deadlock.)
Delay Case: If the two connections grab multiple things in the same order, then one can be delayed until the other finishes. To keep this from "waiting forever", there is a default 50-second
innodb_lock_wait_timeout
. Your pair of simpleUPDATEs
is actually an example of this case. One will finish promptly; the other is stalled until the first finishes.Note how a Deadlock can (in some cases) be turned into a Delay by consistently ordering the things you touch.
autocommit=1: With this setting and without calling
BEGIN
, each statement is effectively:autocommit=0: This is trouble waiting to happen. When you perform a write query, a
BEGIN
is implicitly generated. However, it is your responsibility to eventually issueCOMMIT
. If you fail to do so, you will wonder why your system is hung. (Another common newbie bug.) My advice: "Never use=0
".