π Lecture 40 β Database Views: Concepts, Types, and Updates
π Overview: This lecture delves into the fundamental concept of views in databases, their importance for data focus, customization, security, and logical data independence. It also covers different types of views, how to create and update them, and their practical use cases.
ποΈ Topics Covered
The lecture begins with an introduction to views and how they facilitate user focus, data independence, and security. It discusses how to define views using subsets of tables, joins of multiple tables, and functions. Different types of viewsβmaterialized, simple, complex, and dynamicβare explained with examples. Finally, the lecture addresses how to update views, including limitations, and the use of the WITH CHECK OPTION.
π Lecture Summary
Views
Views are virtual tables created to simplify user interaction by focusing on the specific data relevant to users and hiding unnecessary details. They enhance security by limiting usersβ access to only the data defined in the view, not the underlying base tables. Views can include filtered records, sorted orders, grouped sets, and computed totals. They are dynamically generated results from queries, possibly joining multiple tables. Views improve usability by allowing customization of displayed fields, their order, column widths, and filtering criteria.
π Definition β View: A dynamically generated "result" table put together based on query parameters, representing a subset or join of one or more tables.
π‘ Why this matters: Views separate the physical and conceptual schemas, providing logical data independence so that applications are shielded from changes in the underlying database schema. They also act as security mechanisms by restricting data access.
Types of Views
There are several types of views, each serving different purposes:
- Materialized Views: These are snapshots or replicas of data at a specific point, updated in batches (refreshes) from master tables, used for performance optimization.
- Simple Views: Created from a single table to simplify data manipulation and enforce security.
- Complex Views: Comprised of multiple tables, views, sequences, or other database objects, used for complex data representation.
- Dynamic Views: These do not store data but dynamically fetch current data each time accessed; often include joins, nested views, or computed columns.
π Definition β Materialized View: A copy of data updated periodically in batch from a master source.
π Example:
CREATE VIEW st_view1 AS
SELECT stName, stFname, prName
FROM student
WHERE prName = 'MCS';
This dynamic view retrieves student data only for the 'MCS' program each time it is queried.
Views Using Other Views and Functions
Views can be created using other views or SQL functions, treating them like base tables.
π Examples:
-- View using another view
CREATE VIEW CLASSLOC2 AS
SELECT COURSE#, ROOM
FROM CLASSLOC;
-- View using function
CREATE VIEW CLASSCOUNT(COURSE#, TOTCOUNT) AS
SELECT COURSE#, COUNT(*)
FROM ENROLL
GROUP BY COURSE#;
View Characteristics
Views can have computed attributes and support nesting (views built on other views).
π Example of nested view:
CREATE VIEW enr_view AS SELECT * FROM enroll;
CREATE VIEW enr_view1 AS
SELECT stId, crcode, smrks, mterm, smrks + mterm AS sessional FROM enr_view;
Updates on Views
Views created from a single relation can be updated similarly to tables. However, views involving multiple relations or containing computed attributes cannot be updated directly through the view. The WITH CHECK OPTION enforces that any update or insertion through the view must satisfy the viewβs defining condition.
π Example with WITH CHECK OPTION:
CREATE VIEW st_view2 AS
SELECT stName, stFname, prName
FROM student
WHERE prName = 'BCS'
WITH CHECK OPTION;
UPDATE st_view2 SET prName = 'MCS' WHERE stFname = 'Loving';
-- This update will fail because it tries to set prName outside the view condition 'BCS'.
Deleting Views
Views can be removed using:
DROP VIEW view_name;
This command functions like dropping a table but removes only the view definition, not the underlying data.
β Key Takeaways
- Views provide a virtual, customizable, and secure window into database tables.
- They enhance logical data independence by masking schema changes.
- Views come in several types: materialized, simple, complex, and dynamic, each with specific characteristics and use cases.
- Updates through views are straightforward only for single-relation views; multi-relation or computed views have restrictions.
- The WITH CHECK OPTION ensures updates via a view maintain the viewβs integrity constraints.
π§ Quick Revision Questions
- What is a view in a database, and how does it improve security?
- Describe the difference between a materialized view and a dynamic view.
- How does the WITH CHECK OPTION affect updates on a view?
- Why might a view created from multiple tables not be updatable?
- How do nested views work, and can you give an example?
π Lecture 41 β Indexes and Materialized Views
π Overview: This lecture introduces the concepts of indexes and their classification within database systems, focusing primarily on materialized views as a form of indexed views that help optimize complex queries. Understanding these concepts is crucial for improving database performance and ensuring data integrity in transactional systems.
ποΈ Topics Covered
This lecture begins by revisiting previous discussion on views and updating multiple tables through views. It then explains materialized views in detail, their uses in data warehousing, distributed and mobile computing environments, and how they differ from regular views and indexes. Finally, the lecture covers fundamental concepts of transaction management including the ACID properties that ensure data integrity and consistency.
π Lecture Summary
Updating Multiple Tables
The lecture starts by explaining how updates on multiple views are handled - they must be done one at a time during insertions or modifications. An example using SQL CREATE VIEW demonstrates a join-based view combining student and program data. Insert operations on this view affect underlying tables, with attributes not included in the view definition defaulting to NULL or requiring explicit values to avoid NULLs.
π Definition β View: A virtual table representing data from one or more tables, which does not store data itself but displays results of queries.
π Example:
CREATE VIEW st_pr_view1 (a1, a2, a3, a4) AS (SELECT stId, stName, program.prName, prcredits FROM student, program WHERE student.prName = program.prName);
INSERT INTO st_pr_view1 (a3, a4) VALUES ('MSE', 110);
This inserts into the program table via the view; attributes not part of the view remain NULL if not supplied.
Materialized Views
Materialized views are physical pre-computed tables storing aggregated or joined data, unlike regular virtual views which execute queries each time. They address performance issues with complex joins and large aggregations by storing results via clustered indexes, automatically reflecting base table updates just like ordinary indexes.
π‘ Why this matters: Materialized views drastically improve query performance by avoiding repetitive computation, especially in data warehouses, distributed systems, and mobile computing environments.
Materialized views function as summary or aggregate tables, used to store sums, averages, joins, and filtered query results. The optimizer can transparently redirect queries to materialized views to boost efficiency. They also enable data replication and synchronization across distributed sites and support local mobile data access with periodic refreshes.
Materialized views resemble indexes as they consume storage, require refreshes when base data changes, improve SQL execution speed via query rewrites, and are transparent to users and applications. However, unlike indexes, materialized views can be accessed directly with SELECT, and sometimes INSERT, UPDATE, or DELETE commands.
π Definition β Materialized View: A schema object storing the results of a query physically, enabling fast access and refreshable updates for optimization purposes.
Transaction Management
A transaction is defined as an indivisible unit of work composed of multiple operations that must all succeed or all fail to maintain data integrity. An example given is transferring money between bank accounts β debiting one account and crediting another β both steps must succeed together or not at all.
The lecture describes the ACID properties essential for every transaction:
- Atomicity: The entire transaction is indivisible; it either fully completes or not at all.
- Consistency: Transactions move data from one consistent state to another, restoring data if errors occur.
- Isolation: Transactions appear to execute serially without interfering with each other, even when run concurrently.
- Durability: Once committed, changes are permanent and survive subsequent failures.
A transaction completes either by a commit (successful execution) or a rollback (aborting due to an error), ensuring reliability and correctness.
π Definition β Transaction: An indivisible unit of work consisting of one or more operations that must be executed completely or not at all.
β Key Takeaways
- Views allow virtual representation of data, but updates on multiple views must be handled carefully, attribute coverage affects NULL values.
- Materialized views physically store computed results to enhance query performance and reduce computation overhead.
- Materialized views enable optimization in data warehouses, distributed systems, and mobile computing by caching costly query results.
- Transactions ensure data integrity by adhering to ACID properties: Atomicity, Consistency, Isolation, and Durability.
- Proper transaction management guarantees consistent database states even in the presence of failures, critical for reliable systems.
π§ Quick Revision Questions
- What is the main difference between a materialized view and a regular view?
- Why are clustered indexes important for materialized views?
- How do materialized views improve query performance in data warehousing environments?
- Define the ACID properties of a transaction and explain why each is important.
- What happens to attributes not included in a view definition during insert operations on that view?
π Lecture 42 β Transaction Management in Database Systems
π Overview: This lecture explores the critical concept of transaction management in database systems, highlighting the need for concurrency control to maintain data consistency and integrity during concurrent transaction executions. Understanding these principles is vital for ensuring reliable, efficient database operations in multi-user environments.
ποΈ Topics Covered
The lecture begins with the concept of a transaction, its atomicity, consistency, isolation, and durability properties (ACID). It then discusses transactions and schedules, and the importance of concurrent execution along with the challenges it poses. The principle of serializability is introduced as a correctness criterion. The lecture further explains locking protocols, emphasizing Strict Two-Phase Locking (Strict 2PL), and concludes by addressing the problem of deadlocks, including their prevention and detection techniques.
π Lecture Summary
The Concept of a Transaction
A transaction is defined as a single execution of a user program within a DBMS, involving a series of read and write operations on database objects. Unlike ordinary program runs, transactions in DBMS are interleaved for performance but are managed to appear as if they run serially. Key properties a DBMS must enforce to ensure data integrity are:
- Atomicity: a transaction's actions are all-or-nothing.
- Consistency: transactions preserve database consistency.
- Isolation: concurrent transactions appear isolated to each other.
- Durability: committed transactions persist despite crashes.
π Definition β Transaction: Any one execution of a user program in a DBMS involving a sequence of reads and writes of database objects.
π‘ Why this matters: These ACID properties enable reliable multi-user database access ensuring correctness despite concurrency and system failures.
Transactions and Schedules
A transaction can be modeled as a partially ordered list of actions, typically reads (R) and writes (W) on database objects. A schedule is a sequence of actions from multiple transactions, maintaining the order of actions within each transaction. For example, RT(O) denotes transaction T reading object O. Schedules represent actual or possible executions, capturing interleaving of transaction operations.
Concurrent Execution of Transactions
Concurrency improves performance by overlapping I/O and CPU processes and allows short transactions to finish promptly without waiting for long ones. However, interleaving must be controlled to preserve isolation and consistency.
Serializability
A serializable schedule is one whose outcome is indistinguishable from some serial execution of the same transactions, thus preserving consistency. Multiple serial orders may exist, but all yield consistent database states. Only schedules equivalent to a serial order are permitted to maintain correctness.
π Definition β Serializable Schedule: A schedule whose effect on the database is identical to some serial execution of the transactions.
Lock-Based Concurrency Control
To guarantee serializability and recoverability, DBMSs generally enforce locking protocols. Locks prevent conflicting operations from occurring concurrently.
Strict Two-Phase Locking (Strict 2PL)
Strict 2PL requires each transaction to acquire:
- A shared lock before reading an object.
- An exclusive lock before writing an object.
No other transactions can hold conflicting locks simultaneously. All locks are released only at transaction completion, ensuring serializability.
π Definition β Strict Two-Phase Locking: A locking protocol where all locks are acquired before releasing any, and locks are released only when the transaction commits or aborts.
π‘ Why this matters: Strict 2PL allows safe interleaving, preventing inconsistent database states due to concurrent conflicting operations.
Deadlocks
A deadlock occurs when transactions mutually wait for each other to release locks, creating a cycle that halts all progress.
π Definition β Deadlock: A cycle of transactions each waiting for locks held by others, preventing any from proceeding.
Deadlock Prevention uses priority-based policies with timestamps to avoid circular waits:
- Wait-die: Older transactions wait; younger are aborted.
- Wound-wait: Older abort younger; younger wait.
Deadlock Detection involves maintaining a waits-for graph that tracks transactions waiting on each other. If a cycle is detected, the DBMS resolves it by aborting a transaction.
Conservative 2PL prevents deadlocks by acquiring all needed locks at the start, blocking the transaction if not all locks are available.
π‘ Why this matters: Managing deadlocks is crucial for maintaining system throughput and preventing indefinite transaction blocking.
β Key Takeaways
- Transactions encapsulate database operations with ACID properties critical for data integrity.
- Schedules show how transactions' actions are interleaved during concurrent execution.
- Serializability ensures correctness by making concurrent execution equivalent to some serial order.
- Strict 2PL locking protocol enforces serializability by carefully acquiring and releasing locks.
- Deadlocks must be prevented or detected and resolved efficiently to maintain system performance.
π§ Quick Revision Questions
- What are the four ACID properties of a transaction, and why are they important?
- How does the DBMS model transactions and schedules in terms of read and write actions?
- Define serializability in the context of transaction schedules.
- Explain the two rules of Strict Two-Phase Locking and its role in concurrency control.
- What is a deadlock, and how do the wait-die and wound-wait schemes prevent deadlocks?
π Lecture 43 β Crash Recovery and Concurrency Control in DBMS
π Overview: This lecture focuses on two critical aspects of database management systems: crash recovery techniques using incremental logs with deferred and immediate updates, and an introduction to concurrency control. Understanding these concepts is vital for ensuring database consistency and reliability despite failures or simultaneous user access.
ποΈ Topics Covered
The lecture first explains the incremental log with deferred updates approach to crash recovery, detailing the write sequence, recovery procedure, and use of checkpoints. It then covers the incremental log with immediate updates approach, emphasizing log structure, recovery steps including undo actions, and checkpoints. The lecture concludes with an introduction to concurrency control, highlighting the necessity of concurrent access and the problems it may cause, focusing on the lost update problem.
π Lecture Summary
Incremental Log with Deferred Updates
The deferred updates technique logs only the new values after a write operation but delays updating the database until commit time. On a write operation like "X = X + 10" where X changes from 23 to 33, the log entry is <Tn, X, 33>. Only write operations generate log entries; calculations happen in RAM. After a transaction commits, the database buffer is updated and log entries are flushed to disk. A crash during this interval can cause loss of recent changes, which the recovery manager handles by redoing committed transactions based on log entries.
π Definition β Checkpoint: A special log record that marks a recovery reference point, where modified buffers and log records are written to disk, helping the recovery manager efficiently limit the extent of log scanning after a crash.
The recovery manager (RM) examines the log at restart, redoing all operations of committed transactions and ignoring those that are aborted or incomplete. Using checkpoints improves efficiency by limiting recovery to transactions active after the last checkpoint.
Incremental Log with Immediate Updates
Unlike deferred updates, the immediate updates approach writes to the database buffer as soon as a write operation occurs and logs both old and new values: <Tr, object, old_value, new_value>. This allows changes to be undone if a transaction aborts. The sequence includes logging the write, updating buffers, flushing logs to disk, and eventually updating the database.
During recovery, committed transactions are redone in forward order. Aborted or incomplete transactions are undone by applying the old values in reverse order. Checkpoints are also used to enhance recovery efficiency.
π Definition β Undo: The process of restoring old values of objects for aborted transactions by reversing logged write operations to eliminate their effects.
Concurrency Control
Concurrency control (CC) manages simultaneous data access by multiple users to allow efficient resource usage and data sharing without waiting for exclusive access. Its goal is to prevent inconsistencies caused by uncontrolled concurrent access.
However, if not properly managed, concurrent access can lead to problems like the Lost Update Problem, where concurrent modifications overwrite each otherβs changes, causing data loss.
π Definition β Lost Update Problem: Occurs when two transactions read the same data and update it concurrently, with one transactionβs update overwriting the otherβs changes.
Example: Transaction TA reads BAL = 1000, subtracts 50 and writes 950; Transaction TB reads BAL = 1000, adds 10 and writes 1010. TBβs update overwrites TAβs change, losing the subtraction effect.
β Key Takeaways
- In deferred updates, log entries store only new values and database updates occur at commit, requiring redo of committed transactions after a crash.
- Immediate updates log both old and new values, enabling undo of aborted transactions during recovery.
- Checkpoints are essential in both approaches to limit log scanning and speed up recovery.
- Concurrency control allows multiple users to access data simultaneously but requires mechanisms to prevent inconsistencies.
- The lost update problem is a major concurrency control challenge where concurrent writes cause one update to overwrite another's.
π§ Quick Revision Questions
- What are the main differences between deferred and immediate update logging in crash recovery?
- How does the recovery manager handle transactions after a system crash in the deferred update approach?
- Explain the role and importance of checkpoints in database recovery.
- What is the lost update problem in concurrency control, and why does it occur?
- How does the immediate update approach enable undo operations during recovery?
π Lecture 44 β Concurrency Control, Serializability and Locking
π Overview: This lecture covers key issues in concurrency control (CC) in database systems, focusing on the problems that arise when multiple transactions access data simultaneously. It explains the concepts of serial and interleaved schedules, the theory of serializability, and introduces the mechanism of locking as a method to control concurrency and maintain database consistency.
ποΈ Topics Covered
The lecture begins with a discussion of two major concurrency control problems: the uncommitted update problem and the inconsistent analysis problem. It then explains serial execution and the nature of serial and interleaved schedules including their impact on consistency. The idea of conflicting operations and serializability is introduced as a foundation for concurrency control. Finally, the lecture introduces locking, including types of locks, lock compatibility, and how locks help manage concurrent operations.
π Lecture Summary
Concurrency Control Problems
Two major problems due to concurrent access of data are discussed. The uncommitted update problem occurs when a transaction reads a value modified by another transaction that later aborts, causing inconsistency, as demonstrated by transaction TA updating a balance, TB reading this uncommitted value, and TB's changes becoming invalid after TA's rollback.
The inconsistent analysis problem arises when one transaction reads multiple related objects and another concurrently modifies some of these objects. For example, transaction TA calculating interest on account balances reads some balances, but another transaction TB simultaneously transfers amounts between accounts, causing TA to compute interest on wrongly summed values.
These problems highlight the need for a concurrency control mechanism that maintains database consistency during concurrent access.
Serial Execution and Schedules
Serial execution involves running transactions strictly one after the other, with no interleaving of operations. A schedule or history is the order of operations from transactions as executed.
π Definition β Serial Schedule: A schedule where all operations of one transaction are executed before operations of another.
Different serial schedules for the same set of transactions can produce different final database states but always consistent ones, unlike interleaved executions.
Interleaved schedules mix operations from multiple transactions to improve resource utilization and avoid delays, but must be carefully controlled to prevent concurrency issues.
π‘ Why this matters: Serial schedules guarantee consistency but are inefficient; interleaving improves performance but risks inconsistency, requiring careful control mechanisms.
Conflicting Operations and Serializability
Concurrent problems arise when different transactions access the same data object and at least one performs a write operation. Such operations are called conflicting operations, and the transactions are conflicting transactions.
π Definition β Serializable Schedule: An interleaved schedule is serializable if its outcome is equivalent to some serial schedule, meaning conflicting operations appear in the order of a serial execution.
Serializability ensures database consistency while allowing interleaved execution. The concurrency control mechanism aims to produce serializable schedules by ordering conflicting operations appropriately. Non-conflicting operations can be interleaved freely without causing problems.
Locking
Locking is introduced as a major method to implement serializability. Before performing any operation, a transaction locks the object and releases the lock after the operation completes.
Locks are managed by a lock manager. When an object is locked, other transactions requesting incompatible locks must wait, entering a wait state until the lock is released.
There are two types of locks:
- Shared Lock (Read Lock): Allows a transaction to read an object.
- Exclusive Lock (Write Lock): Allows a transaction to write an object.
π Definition β Lock Compatibility:
| Transaction A | Read Lock | Write Lock |
|---|---|---|
| Transaction B Read Lock | Yes | No |
| Transaction B Write Lock | No | No |
Two shared locks can coexist (multiple transactions can read simultaneously), but an exclusive lock conflicts with both shared and exclusive locks held by others.
Therefore, locking controls concurrent access by ensuring that conflicting operations do not produce inconsistent results.
β Key Takeaways
- Uncommitted update and inconsistent analysis are major concurrency problems leading to inconsistent database states.
- Serial schedules execute transactions one after another, guaranteeing consistency but are inefficient.
- Interleaved schedules improve resource utilization but may cause concurrency problems if uncontrolled.
- Conflicting operations occur when different transactions access the same object with at least one write; controlling these is critical.
- Serializability theory ensures interleaved schedules produce results equivalent to some serial schedule, maintaining consistency.
- Locking is a fundamental concurrency control technique that uses shared and exclusive locks to manage access and prevent conflicts.
π§ Quick Revision Questions
- What is the uncommitted update problem and why does it cause inconsistency?
- How does an inconsistent analysis problem occur during concurrent transactions?
- Define a serial schedule and explain why it guarantees database consistency.
- What makes two operations conflicting operations in concurrency control?
- Explain the difference between shared lock and exclusive lock, and describe their compatibility.
π Lecture 45 β Deadlock Handling, Two Phase Locking, Levels of Locking, and Timestamping
π Overview: This lecture covers critical concepts in transaction management, including deadlock handling, two phase locking, levels of locking, and timestamping in database systems. Understanding these topics is essential to ensure data consistency, avoid transaction stalls, and maintain system performance.
ποΈ Topics Covered
The lecture begins with the locking idea and different lock modes that control transaction isolation. It then defines deadlocks, explains how deadlocks can be detected using wait-for graphs, and discusses techniques for deadlock prevention and resolution. The two phase locking (2PL) protocol is introduced to manage transaction locks in phases. The lecture also discusses levels of locking granularity, balancing concurrency with locking overhead. Finally, it explains timestamping as an alternative to locking and describes its rules and potential rollback issues.
π Lecture Summary
Locking Idea
Transaction isolation is achieved by locking data accessed during a transaction, primarily using two modes: optimistic and pessimistic locking.
- In pessimistic locking, a transaction locks data to prevent others from updating it until it completes, ensuring exclusive access.
- In optimistic locking, transactions access data without locking, allowing concurrent changes; conflicts are detected later.
Locks can be shared locks (allowing multiple readers), update locks (allowing only shared locks by others), or exclusive locks (no other locks allowed). Different transaction isolation levels apply different locking schemes, e.g., read uncommitted uses no locks while repeatable read uses shared locks.
π Definition β Shared Lock: A lock that allows multiple transactions to read data simultaneously but prevents updates.
π Definition β Exclusive Lock: A lock that prevents other transactions from acquiring any lock on the locked data.
Deadlock
A deadlock occurs when two or more transactions hold locks on resources the others want, causing a cycle of waiting that never resolves. This can lead to infinite waiting and system crashes if not handled properly.
Deadlocks happen because a transaction waits indefinitely for another to release a lock while that other transaction is also waiting.
π Definition β Deadlock: A situation where transactions wait indefinitely for locks held by each other, causing a system halt.
Deadlock Handling
Approaches to handle deadlocks include:
- Deadlock prevention: Restricting resource allocation to prevent cycles.
- Deadlock detection and resolution: Detecting cycles using a wait-for graph and then resolving them.
Wait-for graph: A directed graph where nodes are transactions and edges represent waiting for a lock held by another transaction. A cycle indicates deadlock.
π‘ Why this matters: Effective deadlock handling ensures transactions proceed without indefinite waiting, preserving consistency and throughput.
Two Phase Locking
Two Phase Locking (2PL) divides each transaction into two phases:
- Growing phase: Transaction acquires all the locks it needs, without releasing any.
- Shrinking phase: Transaction releases locks and cannot acquire new ones.
This ensures serializability but may hold locks longer, increasing contention. Guidelines to minimize deadlock include accessing resources in a consistent order and using nested transactions.
π Definition β Two Phase Locking (2PL): A concurrency control protocol where locks are acquired during a growing phase and only released during a shrinking phase.
Levels of Locking
Locks can be applied at different granularity levels: attribute, record, file, group of files, or entire database.
- Finer granularity (e.g., row-level) allows more concurrency but increases overhead.
- Coarser granularity (e.g., table-level) reduces overhead but decreases concurrency due to broader locks.
A balance between concurrency and overhead is critical for performance.
π Definition β Granularity: The size of the data unit on which locking is applied, affecting concurrency and overhead.
Deadlock Resolution
When deadlock is detected, the system:
- Chooses a victim transaction (often the shortest-lived).
- Rollbacks the victim transaction, undoing its changes and releasing locks.
- May restart the transaction depending on system settings.
This breaks the deadlock cycle and allows other transactions to proceed.
Timestamping
An alternative to locking that defers all physical updates until commit time and does not block transactions.
- Transactions are assigned timestamps to order their operations.
- Execution order respects the timestamp order, enforcing serializability based on timestamps.
Problems arise if a transaction tries to read or write data updated by a younger transaction, leading to rollback.
π Definition β Timestamping: A concurrency control method that orders transactions by timestamp rather than locks, deferring updates until commit.
Timestamping rules:
- If transaction T reads data written by a younger transaction β Abort T.
- If T writes data read or written by a younger transaction β Abort T.
Aborting T can cause cascading rollbacks where dependent transactions must also abort.
β Key Takeaways
- Locking mechanisms (shared, update, exclusive locks) enforce transaction isolation and consistency.
- Deadlocks occur due to circular waiting; detection via wait-for graphs and resolution by rollback are essential safeguards.
- Two Phase Locking ensures serializability but can cause long-held locks; careful design reduces deadlock risk.
- Lock granularity balances system concurrency against locking overhead and performance.
- Timestamping offers an alternative to locks, using timestamps to order transactions but risks costly rollbacks.
π§ Quick Revision Questions
- What are the main differences between pessimistic and optimistic locking?
- How does a wait-for graph help detect deadlocks?
- Describe the two phases in Two Phase Locking (2PL).
- Why is lock granularity important, and how does it affect concurrency?
- What conditions cause a transaction to abort under the timestamping protocol?