2PL

We need to make all executes are correct without knowing the entire schedule ahead of time, using locks to protect database objects could make it work. Two lock types: Shared lock and exclusive lock. Locks are requested by transactions from the lock manager. The lock manager grants or block requests based on what locks are currently held by other transactions. Transaction will release lock when they do not need them. The lock manager updates its internal lock-table and then gives locks to waiting transactions.

Two phase locking is pessimistic concurrency control protocol. The first phase is growing phase, each transaction requests the locks that it needs from the DBMS's lock manager. The second phase is shrinking phase, transaction enters this phase when it releases its first lock. It cannot acquire new locks in this phase. The problem is cascading aborts(why?), when one transaction aborts, another transaction must be rolled back. Some schedule is serializable but not allowed by 2PL. Strict 2PL means the transaction only releases exclusive-modes locks when it finishes. Rigorous will take all lock until finishes. A schedule is strict if a value written by a transaction is not read or overwritten by other transactions until that transaction finishes. There is no cascading aborts here.

But it is possible to have deadlock in 2PL. A deadlock is a cycle of transactions waiting for locks to be released by each other. Deadlock detection will create a waits-for graph. Edge from $T_i$ to $T_j$ means $T_i$ is waiting $T_j$. The system will periodically check for cycles in waits-for graph and then make a decision on how to break it. When the DBMS detects a deadlock, it will select a “victim” transaction to rollback to break the cycle. DBMS can also decide the rollback length, it could just roll back.

Deadlock prevention will prevent transaction waiting a transaction. Assign priorities based on timestamps, older means higher. These schemes guarantee no deadlocks because only one type of direction is allowed when waiting for a lock. When a transaction restarts, its (new) priority is its old timestamp.

• Wait-Die (“Old waits for Young”): If T1 has higher priority, T1 waits for T2. Otherwise T1 aborts
• Wound-Wait (“Young waits for Old”): If T1 has higher priority, T2 aborts. Otherwise T1 waits.

We could use a use a lock hierarchy that allow a transaction to take more coarse-grained locks in the system. Intention locks allow a higher level node to be locked in shared or exclusive mode without having to check all descendant nodes. If a node is in an intention mode, then explicit locking is being done at a lower level.

• Intention-Shared (IS): Indicates explicit locking at a lower level with shared locks.
• Intention-Exclusive (IX): Indicates explicit locking at a lower level with exclusive or shared locks.
• Shared+Intention-Exclusive (SIX): The subtree rooted at that node is locked explicitly in shared mode and explicit locking is being done at a lower level with exclusive-mode locks.