1Z0-908: MySQL 8.0 Database AdministratorDemo

By Oracle · Question Mode

QuestionQ34

Performance

Examine the following entries from the general query log:

Question Image

Every UPDATE statement refers to an existing row.

What describes the outcome of this sequence of statements?

  • A Connection 24 experiences a lock wait timeout.
  • B Connection 25 experiences a lock wait timeout.
  • C A deadlock occurs immediately.
  • D All statements execute without error.
  • E A deadlock occurs after innodb_lock_wait_timeout seconds.
Explanation

The transactions acquire exclusive locks on different rows in opposite order: one holds the t1 row while requesting the t2 row, and the other holds the t2 row while requesting the t1 row. This circular wait is an InnoDB deadlock. With deadlock detection enabled by default, InnoDB detects it immediately and rolls back a victim transaction; it does not wait for innodb_lock_wait_timeout.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!