Database Systems with SQL - Full Course - Database Systems - Concurrency - Locks and Deadlocks, 2PL, and Snapshot Isolation

Locking The concurrency system manages concurrent transactions in a database by implementing isolation levels and trying to maintain good database performance. However, there is a tradeoff between isolation levels and performance, so locking is a technique for handling this. A lock permits or denies a transaction from reading or writing data. Database locks exist to prevent two or more database users from updating the same exact piece of data at the same exact time. A shared lock only permits a transaction to read data, but not write data. Concurrent transactions can have a shared lock on the same data. An exclusive lock permits a transaction to read and write data. If one transaction holds an exclusive lock, no concurrent transaction can have a shared or exclusive lock on the same data. Lock scope is the set of data that is reserved by a lock, which is typically a single row of data, so that other rows can be accessed by other transactions. Lock scope could also be a block or the whole table in the case where a transaction needs to access several rows of data. A lock scope could also include indexes. The concurrency system figures out when to grant or release locks by monitoring active transactions. Lock requests are handled by the lock manager, which tracks, grants, and releases locks. The concurrency system implements each transaction’s isolation level by requesting shared and exclusive locks as needed. The concurrency system also reduces the duration of transactions that are waiting for locked data by minimizing lock scope. Two-phase locking A serializable isolation level means that a transaction is independent from other transactions. There are 3 types of two-phase locking, which help prevent conflicts and ensure serializable transactions. Basic two-phase locking A transaction is said to follow the basic two-Phase Locking protocol if locking and unlocking can be done in two phases.  - Growing Phase: New locks on data items may be acquired but none can be released. - Shrinking Phase: Existing locks may be released but no new locks can be acquired. Strict two-phase locking - a transaction must hold all its exclusive locks till it commits/aborts. Most relational database concurrency systems implement this type Rigorous two-phase locking - a transaction holds both shared and exclusive locks until the transaction commits or rolls back. This type is easier to implement than strict, but not as efficient because shared locks are held longer. Deadlocks A deadlock is a situation in which two or more transactions are waiting for one another to give up locks A dependent transaction is waiting for data locked by another transaction. A cycle of dependent transactions indicates deadlock has occurred. Concurrency systems manage and prevent deadlock in several ways: Aggressive locking - each transaction starts with a request to all locks. If all locks are granted, then the transaction runs to completion. If not, the transaction waits until other transactions release locks. Data ordering - the data is ordered, and each concurrent transaction takes locks in order. Timeout – each transaction requests the lock rolls back if the lock waiting exceeds a fixed period of time set by the DBA. Cycle detection – If the concurrency system discovers a cycle of dependent transactions then it selects and rolls back the transaction that has the fewest locked rows or most recent start time, or whichever is easier to manage. Snapshot isolation is a popular alternative to two-phase locking. Snapshot isolation is an optimistic technique, which executes concurrent transactions without locks by creating a snapshot of the data. So the transactions never wait, but may sometimes restart after all operations are processed. It works this way: Make a snapshot, or private copy of all data read and written by the transaction Write updates to private copies of the data Determine if there are any transaction conflicts with an update Write the updates to the database, or roll back the transaction Serializable snapshot isolation is a serializable version of snapshot isolation which ensures serializable schedules when isolation level is set to SERIALIZABLE. Subscribe to Appficial for more programming videos coming soon. Also, don't forget to click LIKE and comment on the video if it helped you out!