How does Two Phase Locking know when to release locks?



  • I have been doing some reading up on Two Phase Locking and understand that it involved 2 phases:

    1.  Growing phase: locks are acquired gradually and no locks are released.
    
    1. Shrinking phase: locks are released gradually and no locks are acquired.

    My question is, how does the Two Phase Locking (not "Strict Two Phase Locking") mechanism ever know when it can begin the shrinking phase? It is impossible to tell if a transaction needs to acquire any more locks unless the DB knows all the queries in the transaction (which goes against the whole point of the Two Phase Locking mechanism which is supposed to be able to ensure serializability without knowing all the queries in the transaction).

    Suppose I have a transaction as follows:

    start transaction;
    select * from test_tab where id=1;
    select * from test_tab where id=2;
    update test_tab set age=100 where id=3;
    commit;
    

    From the transaction above, the Two Phase Locking mechanism should not release any locks until the last update statement is done (since it needs to acquire a lock here, and it cannot acquire a lock in the shrinking phase). But how can the DB know if it can begin the shrinking phase unless the client reaches the end of the transaction and commits (since the DB will never know if the client will send an update query right before it commits)? Am I misunderstanding something?



  • The wikipedia article on two-phase locking says:

    Typically, without explicit knowledge in a transaction on end of phase 1, it is safely determined only when a transaction has completed processing and requested commit. In this case, all the locks can be released at once (phase 2).

    So I think you understand correctly about the theory.

    For what it's worth, since you tagged this question mysql, I don't think MySQL's InnoDB engine implements two-phase locking.

    Locks are acquired optimistically, as each SQL statement that needs locks is executed.

    Both read locks and write locks are usually not released until the termination of the transaction (COMMIT or ROLLBACK). https://dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html says:

    Both COMMIT and ROLLBACK release all InnoDB locks that were set during the current transaction.

    The above might support two-phase locking, but there are some exception cases of releasing locks before others are acquired. For example, in READ-COMMITTED isolation level, row locks on non-matching rows are released. The transaction may have other SQL statements that need to acquire locks after that. So InnoDB's locking implementation doesn't comply with the definition of two-phase locking.

    Another example is the auto-increment lock, which is acquired and released during each INSERT statement.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2