Structured Query Language and Relational Queries
View All Tags
Two transactions touch only one product, and in the same order, and they still deadlock with ERROR 1213. The hidden lock comes from the foreign key: inserting a child row takes a shared lock on its parent. When both then try to update the parent, neither can go on. The fix is to swap two lines.
"Lock the row if it's there, and insert it if it isn't" sounds like the careful way to do it. On MySQL, two sessions doing that at once deadlock on the INSERT, with ERROR 1213. Asking for a row that doesn't exist locks a gap, and gap locks don't block each other, until both sessions try to insert.
EXPLAIN UPDATE gave me one line: EXPLAIN: -> <not executable by iterator executor>. It isn't an error, and it isn't useful either. In MySQL 9.7, the default output format can't describe this kind of statement, and the older format can.
Connecting to MySQL failed with ERROR 1040 (HY000): Too many connections. The server is up, and there's no room for you. Find out who's holding the connections before you raise the limit, because a higher limit often just delays the same problem.
SET innodb_rollback_on_timeout = ON failed with ERROR 1238 (HY000): Variable 'innodb_rollback_on_timeout' is a read only variable. Some settings can only be chosen when the server starts. With Docker Compose, that takes one line and a restart.
KILL failed with ERROR 1094 (HY000): Unknown thread id: 99999. No connection has that number. Either it's already gone, or the number came from the wrong column, and MySQL has two kinds of ID that are easy to mix up.
KILL failed with ERROR 1095 (HY000): You are not owner of thread 183. An ordinary account can only stop its own connections, so this one needs another account's help, or a privilege that shouldn't go to an application.
A plain SELECT printed ERROR 2013 (HY000): Lost connection to MySQL server during query, then No connection. Trying to reconnect..., and then showed a result as if nothing had happened. Something had happened: the open transaction was gone, and my session was a new one.
A statement that had been waiting ended suddenly with ERROR 1317 (70100): Query execution was interrupted. MySQL didn't give up by itself: another session stopped it with KILL QUERY. The statement had no effect, and the connection is fine.
I ran ROLLBACK, and MySQL answered Query OK, and my UPDATE was still there. A CREATE TABLE in between had quietly committed it. MySQL doesn't warn you, and the ROLLBACK has nothing to report.
