How to implement Shared Exclusive locking in DBMS?

TL;DR

Shared exclusive locking aims to ensure serializability and recoverability in concurrent schedules. Shared locks permit reads by multiple transactions, while exclusive locks allow reads and writes but block other locks on the same item. The protocol uses a compatibility table to decide grant or deny requests, preventing conflicts such as read-write and write-write on the same data item.

Transcript

Hello friends, Welcome to Gate smashers In this video we're going to talk about concurrency control protocol concurrency control protocol basic aim is to achieve serializabity and achieving recover-ability and what are serializabity and recover-ability we had already discussed Conflict serializabity, view serializabity and in that we've discussed t... Read More

Key Insights

• Locking protocol aims for serializability and recoverability.
• Two locks exist: shared for reading, exclusive for reading and writing.
• Granting rules depend on a compatibility table.
• Shared can be granted to multiple transactions for the same item.
• Exclusive locks block other locks on the same item.
• Granting shared with exclusive later is denied to avoid conflicts.
• Two-stage behaviour: lock acquisition then release.
• Protocol prevents read-write and write-write conflicts on an item.

Questions & Answers

Q: What is the main purpose of the shared exclusive locking protocol in DBMS?

The main purpose of the shared exclusive locking protocol is to achieve serializability and recoverability in concurrent schedules. It ensures data item access is controlled so that multiple readers can access an item concurrently, but writers cannot create conflicts by performing reads and writes simultaneously with other transactions. By enforcing a compatibility-based grant system, the protocol maintains data consistency and isolation across transactions.

Q: How do shared locks differ from exclusive locks in this protocol?

Shared locks allow a transaction to read a data item, and multiple transactions can hold a shared lock on the same item at the same time. Exclusive locks, on the other hand, allow both reading and writing but are not granted if other transactions hold a shared or exclusive lock on the item. This distinction prevents read-write and write-write conflicts and helps maintain serializability.

Q: What happens if a transaction holding a shared lock requests an exclusive lock on the same item?

If a transaction holding a shared lock on an item requests an exclusive lock, the request is denied because granting an exclusive lock would prevent other transactions from reading the item concurrently. To preserve serializability and prevent conflicts, the system denies the exclusive request while the shared lock is held, and may serialize access or wait until the item is unlocked.

Q: Why are compatibility tables important in the shared exclusive locking protocol?

Compatibility tables are important because they codify which combinations of locks can be granted simultaneously. They define that shared locks can coexist, but exclusive locks cannot coexist with any other lock types on the same item. This explicit policy helps the concurrency control component decide whether to grant or deny a lock request, ensuring safe, conflict-free scheduling.

Q: Can two transactions read the same data item at the same time under this protocol?

Yes, two transactions can read the same data item at the same time because shared locks are compatible with other shared locks. This allows concurrent reads without blocking, which increases throughput for read-heavy workloads while preserving consistency since no writes occur during the shared lock period.

Q: What triggers the release of locks in the shared exclusive locking protocol?

Locks are released when the work on the data item is finished, i.e., when the transaction completes its read or write operations and completes its execution on that item. Releasing locks allows other transactions to acquire locks and proceed, helping to maintain progress and avoid deadlock when combined with proper scheduling.

Q: How does this protocol contribute to consistency and ACID properties?

By ensuring that only compatible lock requests are granted, the protocol prevents conflicting operations on the same data item, which upholds isolation and consistency. Together with proper logging and recovery protocols, the locking mechanism contributes to atomicity and durability, supporting the overall ACID properties in a concurrent environment.

Q: What are the two lock types used in the described protocol and their primary use cases?

The two lock types are shared locks and exclusive locks. Shared locks are used when transactions only need to read a data item, allowing multiple concurrent readers. Exclusive locks are used when a transaction needs to read and write, blocking all other reads and writes on the item to ensure data integrity and prevent conflicts during updates.

Summary & Key Takeaways

• Shared exclusive locking is the simplest locking protocol used to achieve serializability and recoverability in DBMS. It uses two lock types, shared for reading and exclusive for reading plus writing, with a compatibility table guiding grants and denials. The main goal is to prevent conflicting operations on the same data item.

• The protocol relies on a compatibility table to prevent undesirable schedules, ensuring that only compatible lock requests are granted. If a shared lock is held, another shared lock can be granted, but an exclusive lock request is denied to avoid read-write conflicts.

• The approach emphasizes that once work on a data item is complete, locks are released, allowing other transactions to proceed. This helps maintain transaction isolation and consistency across concurrent operations.