| Transaction processing systems usually allow multiple transactions to run concurrently. By allowing multiple transactions to run concurrently will improve the performance of the system in terms of increased throughout or improved response time, but this allows causes several complications with consistency of the data. Ensuring consistency in spite of concurrent execution of transaction require extra work, which is performed by the concurrency controller system of DBMS. Show
We’ll be covering the following topics in this tutorial:
What is Lock?A lock is a variable associated with a data item that describes the status of the item with respect to possible operations that can be applied to it. Generally, there is one lock for each data item in the database. Locks are used as a means of synchronizing the access by concurrent transactions to the database item. Types of LocksSeveral types of locks are used in concurrency control. To introduce locking concepts gradually, we first discuss binary locks, which are simple but restrictive and so are not used in practice. We then discuss shared/exclusive locks, which provide more general locking capabilities and are used in practical database locking schemes. Binary LocksA binary lock can have two states or values: locked and unlocked. A distinct lock is associated with each database item A. If the value of the lock on A is 1, item A cannot be accessed by a database operation that requests the item. If the value of the lock on A is 0 then item can be accessed when requested. We refer to the current value of the lock associated with item A as LOCK (A). There are two operations, lock item and unlock item are used with binary locking A transaction requests access to an item A by first issuing a lock item (A) operation. If LOCK (A) = 1, the transaction is forced to wait. If LOCK (A) = 0 it is set to 1 (the transaction locks the item) and the transaction is allowed to access item A. When the transaction is through using the item, it issues an unlock item (A) operation, which sets LOCK (A) to 0 (unlocks the item) so that A may be accessed by other transactions. Hence binary lock enforces mutual exclusiol1 on the data item. Rules of Binary LocksIf the simple binary locking scheme described here is used, every transaction must obey the following rules: 1. A transaction must issue the operation lock_item (A) before any read_item (A) or write, item operations are performed in T. 2. A transaction T must issue the operation unlock_item (A) after all read_item (A) and write_item (A) operations are completed in T. 3. A transaction T will not issue a lock _item (A) operation if it already holds the lock on Item A. 4. A transaction T will not issue an unlock _item (A) operation unless it already holds the lock on item A. 5. The lock manager module of the DBMS can enforce these rules. Between the Lock_item (A) and unlock_item (A) operations in transaction T, is said to hold the lock on item A. At most one transaction can hold the lock on a particular item. Thus no two transactions can access the’ same item concurrently. Disadvantages of Binary LocksAs discussed earlier, binary locking scheme is too restrictive for database items, because at most one transaction can hold a lock on a given item. So, binary locking system cannot be used for practical purpose. Share/Exclusive (for Read/Write) LocksWe should allow several transactions to access the same item A if they all access A’ for reading purposes only. However, if a transaction is to write an item A, it must have exclusive access to A. For this purpose, a different type of lock called a multiple-mode lock is used. In this scheme there are shared/exclusive or read/write locks are used. Locking operationsThere are three locking operations called read_lock(A), write_lock(A) and unlock(A) represented as lock-S(A), lock-X(A), unlock(A) (Here, S indicates shared lock, X indicates exclusive lock)can be performed on a data item. A lock associated with an item A, LOCK (A), now has three possible states: “read-locked”, “write-locked,” or “unlocked.” A read-locked item is also called share-locked item because other transactions are allowed to read the item, whereas a write-locked item is caused exclusive-locked, because a single transaction exclusively holds the lock on the item. Compatibility of LocksSuppose that there are A and B two different locking modes. If a transaction T1 requests a lock of mode on item Q on which transaction T2 currently hold a lock of mode B. If transaction can be granted lock, in spite of the presence of the mode B lock, then we say mode A is compatible with mode B. Such a function is shown in one matrix as shown below:
The graphs shows that if two transactions only read the same data object they do not conf1ict, but if one transaction writes a data object and another either read or write the same data object, then they conflict with each other. A transaction requests a shared lock on data item Q by executing the lock-S(Q) instruction. Similarly, an exclusive lock is requested through the lock- X(Q) instruction. A data item Q can be unlocked via the unlock(Q) instruction. To access a data item, transaction T1 must first lock that item. If the data item is already locked by another transaction in an incompatible mode, the concurrency control manager will not grant the lock until all incompatible locks held by other transactions have been released. Thus, T1 is made to wait until all incompatible locks held by other transactions have been released Example: As an illustration consider the simplified banking system. Let A and B be two accounts that are accessed by transactions T1 and T2. Transaction T1 transfers Rs.50 from account B to account a and is defined as:
Transaction T2 displays the total amount of money in accounts A and B that is, the sum A+B and is defined as
Suppose that the values of accounts A and Bare Rs.100 and Rs.200, respectively. If these two transactions are executed serially, either in the order T1 and T2 or the order T2, T1 then transaction T2 will display the value Rs.300. If however, these transactions are executed concurrently, as shown in Schedule 1. In this case, transaction T2 displays Rs.250, which is incorrect. The reason for this mistake is that the transaction TI unlocked data item B too early, as a result of which T2 shows an inconsistent state. Solution of Inconsistency ProblemSuppose now that unlocking is delayed to the end of the transaction. The transaction T3 corresponds to T I with unlocking delayed and is defined as
Unfortunately, the use of locking can lead to an undesirable situation. Consider the partial schedule 2, T3 is holding an exclusive mode lock on B and T4 is requesting a shared mode lock on B i.e.T4 is waiting forT3 to unlock B. Similarly, T4 is holding a shared mode lock on A and T3 is requesting an exclusive mode lock on A, thus T3 is waiting for T4 to unlock A. Thus we have arrived at a state where neither of these transactions can ever proceed with its normal execution. This situation is called deadlock.
Conclusion Thus we can say that the solution of inconsistency leads to deadlock problem. If we do not use locking or unlock data items as soon as possible after reading or writing them, we may get inconsistent states. On the other hand, if we do not unlock a data item before requesting a lock on another data item deadlocks may occur. There are ways to avoid deadlock in some situations. Deadlocks are definitely preferable to inconsistent states, since they can be handled by rolling back of transactions, where as inconsistent states may lead to real world problems that cannot be handled by the database system. What is a binary lock?Binary Locks − A lock on a data item can be in two states; it is either locked or unlocked. Shared/exclusive − This type of locking mechanism differentiates the locks based on their uses. If a lock is acquired on a data item to perform a write operation, it is an exclusive lock.
What type of lock forbid any other users to access the data in any way?When a statement modifies data, its transaction holds an exclusive lock on data that prevents other transactions from accessing the data. This lock remains in place until the transaction holding the lock issues a commit or rollback. They can be owned by only one transaction at a time.
What are the various types of locks used for concurrency control?There are two types of locks in Lock based protocols. These are: Binary Locks - These can only be in one of two states, locked or unlocked. Shared/Exclusive Locks - Shared locks are acquired when only read operation is to be performed.
What is the lowest level to use the locking protocol in the database?The locking in a database can be done at 4 levels, which start with the database at the highest level and down via table and page to the row at the lowest level.
|
