NodeJS Discussion

This is from 13 years ago at MySQL InnoDB Deadlocks and Duplicate key errors (1213, 1205, 1062)] and is hopefully no longer true, but this is the kind of thing I'm concerned about:

quote:

---

#1213 Deadlock found when trying to get lock error

​

InnoDB locks rows and starts transactions internally as needed.

​

From time to time, particularly when concurrent threads are hitting the same rows, you're going to experience a deadlock.

​

Deadlocks happen when two transactions wait on each other to acquire a lock. For example:

​

Tx 1: lock A, then B
Tx 2: lock B, then A

​

Because InnoDB starts transactions on the internally, you -are- going to experience deadlocks.

​

No way of escaping it.

​

Fortunately when deadlocks do happen with this error 1213, all you have to do is retry the query until it goes through.

---

Maybe what this says is only partially true, because it says that InnoDB "locks rows and starts transactions internally as needed," and it doesn't mention explicitly handling transactions with BEGIN/COMMIT.I've highlighted the key portion. My view is that random deadlocks out of the blue shouldn't happen except when database code is thrown together willy-nilly with little design effort. In my code for EvC deadlocks never happen, and if I change over to InnoDB and transactions while maintaining my code structure it seems to me that they should still never happen.But maybe when locks are at the row level instead of the table level they *can* happen in well designed code, but I can't come up with any examples where that might happen in an EvC context.I like that with deadlock detection enabled that deadlocks are automatically detected and the smallest transaction rolled back, but a casual search didn't reveal how to restart the transaction. And the statement, "Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock," is concerning.I also looked up how to detect deadlocks. One page suggested using SHOW ENGINE INNODB STATUS. When? After every COMMIT? After an operation like writing a message to the database. Before exiting?I could find no code example of deadlock detection followed by retry.--Percy

I don’t know InnoDB and my database experience is from years ago.However, that’s an error code, not a bug and retrying would solve it.There’s a similar issue from 3 years ago on StackOverflow but it involves a trigger and comments suggest that the use of a non-default isolation level might be part of the problem.