There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.
Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.
Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.
You also have special cases of SELECT where you can lock specific rows on demand.
Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:
- Your SQL UPDATEs a single row but generates an error
- The UPDATE causes a rollback of the one row
- The row has a lingering lock
Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)
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:
BEGIN;
SELECT something;
think about it for a while
UPDATE that something;
COMMIT;
The select needs to be written thus:
SELECT something FOR UPDATE;
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:
BEGIN; -- in one connection
UPDATE thing_1;
UPDATE thing_2;
COMMIT;
BEGIN; -- in another connection, at the "exact same time"
UPDATE thing_2;
UPDATE thing_1;
COMMIT;
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 simple UPDATEs
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:
BEGIN;
your statement
COMMIT;
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 issue COMMIT
. If you fail to do so, you will wonder why your system is hung. (Another common newbie bug.) My advice: "Never use =0
".
Best Answer
I apologize for the other answer you were given that just copied and pasted from the manual without being related to your question.
This is a common misconception- Innodb does not do full table locks to ensure isolation between transactions- however, it does apply row and gap locking by default to assure no phantom reads happen. Because your table is unindexed the only (inefficient) way to do gap locking is to setup individual lock in every row and every gap between ( for example if you try to insert a new row with id 0, it should get blocked too.
You have two options: create an index on id (probably a primary key is the right way) so only a single row is actually locked thanks to the new index, or change your default isolation level to read committed (
SET SESSION transaction_isolation='READ-COMMITTED';
).This is a common question here on dba.stackexchange so here you have a more detailed explanation I wrote on another similar question: MariaDB - "ERROR 1205 ... Lock wait timeout exceeded; ..." when doing multiple parallel UPDATEs affecting different rows
However, in your case I strongly recommend you to go the route of creating the index.