Examine these entries from the general query log:
All UPDATE statements reference existing rows.
Which describes the outcome of the sequence of statements?
1z0-908 Exam QuestionsBrowse all questions from this exam
1z0-908 Exam - Question 12
Show AnswerHide Answer
Correct Answer: E
There are two connections, 24 and 25, both making updates on the same rows in tables t1 and t2. Both connections start transactions and update rows with the same IDs without committing those transactions. This causes mutual blocking as both transactions are waiting for the other to release the locks. Since no commits occur, this situation leads to a deadlock after the innodb_lock_wait_timeout period. Therefore, after the innodb_lock_wait_timeout seconds, a deadlock will occur.
Discussion
3 commentsDhanushkaOption: D
Jul 16, 2023Auto-commit not been disabled hence it should be able to execute all
marklvOption: D
Sep 29, 2023D is right
marklv
Dec 11, 2023correction, 24 and 25 hit the same id, an started a transaction. B and C are correct except b is NOT correct if 24 commits before timeout. So C happens. And C happens because the updates happen BEFORE 24 commits.
marklv
Jul 15, 2024I see I didn't say the answer. No commit is ever executed, so the transactions are in limbo. So E is the correct answer. Eventually a timeout will occur. Auto commit does not matter when you begin a transaction. Both connections hit the same row, and are not committed, so there are lock.
FelipeKOption: C
Oct 25, 2023root@localhost[world]> update t2 set val=42 where id =3805; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_lock_wait_timeout | 900 | +--------------------------+-------+