---

Using timeout to detect deadlock

---

• Timeout and deadlock
  • Fact:

    When a transaction T is involved in a deadlock, the transaction T will not finish

    ---

  • Simple way to detect if a transaction is a candidate to be involved in a deadlock:

    Set a timer for each operation When the timer expires, the transaction is assumed to be involved in a deadlock

    ---

  • Note:

    This method is quite relaible because most operations (read/write) does not take very long !!!

  ---

  ---

  ---

  ---

• Deadlock used in existing systems
  • MySQL uses timeout to detect deadlocks

    ---

  • Example:

    Open 2 windows and run sqlroot and log in as cheung: use companyDB Employee: +--------+---------+----------+ | fname | lname | salary | +--------+---------+----------+ | John | Smith | 20900.00 | | Frankl | Wong | 50000.00 | | Alicia | Zelaya | 25000.00 | | Jennif | Wallace | 43000.00 | | Ramesh | Narayan | 38000.00 | | Joyce | English | 25000.00 | | Ahmad | Jabbar | 25000.00 | | James | Borg | 55000.00 | +--------+---------+----------+ Experiment: User 1 User 2 Don't do anything... START TRANSACTION; UPDATE employee SET salary=salary + 0 WHERE lname='Smith'; >> 1 row updated START TRANSACTION; UPDATE employee SET salary=salary + 0 WHERE lname='Smith'; >> Update HANGS !!! (WAIT about 1 min TIME) ERROR 1205: Lock wait timeout exceeded; try restarting transaction Don't do anything...