• Some Rollback Experiments 
      select * from WAREHOUSE;
update WAREHOUSE set Manager="Michele Bennett" where Manager="Dan Bennett";
update WAREHOUSE set SquareFeet=1000;
rollback;
select * from WAREHOUSE;
	 
    • T1.0  start transaction;
T1.1  select * from warehouse;
T1.2  update WAREHOUSE set Manager="Michele Bennett" where Manager="Dan Bennett";
T1.3  rollback

T2.0 start transaction
T2.1 select * from WAREHOUSE;
T2.2 update WAREHOUSE set SquareFeet=999 where Manager="Dan Bennett";
T2.3 commit;

Case 1
T1.0
T2.0
T1.1
T2.1
T1.2
T2.2 (wait a while)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update WAREHOUSE set SquareFeet=999 where Manager="Dan Bennett";
    • Play with this some.
  • The default behavior for sessions in an INNODB table are controlled by the transaction isolation level.
    • Set transaction syntax
    • syntax = {REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE}
    • REPEATABLE READ is the default.
      • At the first table read (non locking select), a snapshot is created.
      • All further selects are performed on this snapshot.
      • If a locking statement is executed (Locking Read, Update, Delete)
      • INNODB either locks a single index value or a range of index values.
      • This prevents insertion by other sessions.
    • Select statements can be done with FOR UPDATE | LOCK IN SHARE MODE
      • LOCK In SHARE MODE allows other reads.
      • FOR UPDATE blocks other sessions from reads.
      • The locks are released after a commit or a rollback.
  • In general, there is a problem with locking/unlocking/locking/unlocking
    • It can lead to a non-serializable schedule.
  • So they implement a general two-phase locking rule
    • During the growing phase attempt to acquire all of the locks needed to complete the task.
    • During the shrinking phase perform the task and release the locks.
    • NEVER release a lock until you have acquired all of the locks needed.
    • NEVER acquire a lock after you have released a lock.
  • Deadlock is a condition where two (or more ) processes are waiting for a lock to be released.
  • Three different strategies
    • Deadlock prevention
    • Deadlock avoidance (DBMS: Timeout)
    • Deadlock detection with recovery
  • INNODB
    • Detects deadlock
    • Kills the transaction with the smaller rollback.