MySQL does not open a transaction, is there a deadlock between different SQLs?

Question

2 concurrent do not start transactions.

Concurrent A locks 1-3-5 sequentially, of course, the actual locking process must be one by one.Concurrent B locks 5-8-1 sequentially, of course, the actual locking process must be one by one.(1-3-5 and 5-8-1 is primary key)

Assume that when concurrent A locks to 1-3, concurrent B locks to 5-8.Next, concurrent A lock 5 and wait, and concurrent B lock 1 and wait.Is there any possibility of this causing a deadlock?

If so, why doesn't MySQL automatically give a concurrent exit lock to avoid deadlock?

Assuming that the transaction is opened, this mechanism is definitely not allowed, because the result has been returned in the transaction.If don't open the transaction, it can in principle.

Answer 1

You can get a deadlock; you cannot always avoid it. But you can recover.

Thread A:

BEGIN;  -- Yes, you must start a transaction
lock row with id=1
lock row with id=5
...
COMMIT;

Thread B:

BEGIN;
lock row with id=5
lock row with id=1
...
COMMIT;

This is the classic example of a deadlock.

If Thread A grabs row 1 and reaches for row 5 after thread B has already grabbed row 5. Each is now waiting for the other row.

InnoDB is smart enough to see that there is no way for this to unravel things, so it picks one transaction to kill. In doing so it undoes anything that was already done by that transaction. Meanwhile, the other Thread can proceed.

So, if you check for errors after each statement (even COMMIT), you catch the deadlock. At that point, usually, the 'right' thing to do is to simply start the transaction over (in that Thread that had the deadlock.)

Transactions (between BEGIN and COMMIT) are necessary to "lock rows". So, I don't understand what you mean by locking without transactions.