1

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.

2
  • What do "1", "3", etc refer to?
    – Rick James
    Commented Dec 17, 2022 at 18:40
  • It's primary key of a table. Commented Dec 18, 2022 at 6:34

1 Answer 1

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.

2
  • Don't open the transaction means that the transaction will be submitted automatically. If a SQL wants to lock multiple rows of data, the essence is to operate step by step.For example, in the above example, if there is a lock conflict between two SQL statements, can it also cause a deadlock? Commented Dec 18, 2022 at 12:26
  • With autocommit=ON, each single statement is implicitly a transaction -- as if it were wrapped in BEGIN and COMMIT. That is, every DML statement is part of a transaction, either explicitly or implicitly. The "step by step" is at a lower level. See discussions of "ACID", especially the A (for Atomic) aspects.
    – Rick James
    Commented Dec 18, 2022 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.