Binary Locks. A binary lock can have two states or values: locked and unlocked (or1 and 0, for simplicity). A distinct lock is associated with each database item X. If thevalue of the lock on X is 1, item X cannot be accessed by a database operation thatrequests the item. If the value of the lock on X is 0, the item can be accessed whenrequested, and the lock value is changed to 1. We refer to the current value (or state)of the lock associated with item X as lock(X).Two operations, lock_item and unlock_item, are used with binary locking. A transactionrequests access to an item X by first issuing a lock_item(X) operation. If LOCK(X) =1, the transaction is forced to wait. If LOCK(X) = 0, it is set to 1 (the transaction locksthe item) and the transaction is allowed to access item X. When the transaction isthrough using the item, it issues an unlock_item(X) operation, which sets LOCK(X)back to 0 (unlocks the item) so that X may be accessed by other transactions. Hence,a binary lock enforces mutual exclusion on the data item. A description of thelock_item(X) and unlock_item(X) operations is shown in Figure 22.1.22.1 Two-Phase Locking Techniques for Concurrency Control 779lock_item(X):B: if LOCK(X) = 0 (* item is unlocked *)then LOCK(X) ←1 (* lock the item *)elsebeginwait (until LOCK(X) = 0and the lock manager wakes up the transaction);go to Bend;unlock_item(X):LOCK(X) ← 0; (* unlock the item *)if any transactions are waitingthen wakeup one of the waiting transactions;Figure 22.1Lock and unlock oper-ations for binary locks.Notice that the lock_item and unlock_item operations must be implemented as indi-visible units (known as critical sections in operating systems); that is, no interleav-ing should be allowed once a lock or unlock operation is started until the operationterminates or the transaction waits. In Figure 22.1, the wait command within thelock_item(X) operation is usually implemented by putting the transaction in a wait-ing queue for item X until X is unlocked and the transaction can be granted accessto it. Other transactions that also want to access X are placed in the same queue.Hence, the wait command is considered to be outside the lock_item operation.It is quite simple to implement a binary lock; all that is needed is a binary-valuedvariable, LOCK, associated with each data item X in the database. In its simplestform, each lock can be a record with three fields: <Data_item_name, LOCK,Locking_transaction> plus a queue for transactions that are waiting to access the item.The system needs to maintain only these records for the items that are currently lockedin a lock table, which could be organized as a hash file on the item name. Items notin the lock table are considered to be unlocked. The DBMS has a lock manager sub-system to keep track of and control access to locks.If the simple binary locking scheme described here is used, every transaction mustobey the following rules:1. A transaction T must issue the operation lock_item(X) before anyread_item(X) or write_item(X) operations are performed in T.2. A transaction T must issue the operation unlock_item(X) after all read_item(X)and write_item(X) operations are completed in T.3. A transaction T will not issue a lock_item(X) operation if it already holds thelock on item X. 14. A transaction T will not issue an unlock_item(X) operation unless it alreadyholds the lock on item X.1This rule may be removed if we modify the lock_item (X) operation in Figure 22.1 so that if the item iscurrently locked by the requesting transaction, the lock is granted.780 Chapter 22 Concurrency Control TechniquesThese rules can be enforced by the lock manager module of the DBMS. Between thelock_item(X) and unlock_item(X) operations in transaction T, T is said to hold thelock on item X. At most one transaction can hold the lock on a particular item.Thus no two transactions can access the same item concurrentl

2 Concurrency Control TechniquesThese rules can be enforced by the lock manager module of the DBMS. Between thelock_item(X) and unlock_item(X) operations in transaction T, T is said to hold thelock on item X. At most one transaction can hold the lock on a particular item.Thus no two transactions can access the same item concurrently.Shared/Exclusive (or Read/Write) Locks. The preceding binary lockingscheme is too restrictive for database items because at most, one transaction canhold a lock on a given item. We should allow several transactions to access the sameitem X if they all access X for reading purposes only. This is because read operationson the same item by different transactions are not conflicting (see Section 21.4.1).However, if a transaction is to write an item X, it must have exclusive access to X. Forthis purpose, a different type of lock called a multiple-mode lock is used. In thisscheme—called shared/exclusive or read/write locks—there are three lockingoperations: read_lock(X), write_lock(X), and unlock(X). A lock associated with anitem X, LOCK(X), now has three possible states: read-locked, write-locked, orunlocked. A read-locked item is also called share-locked because other transactionsare allowed to read the item, whereas a write-locked item is called exclusive-lockedbecause a single transaction exclusively holds the lock on the item.One method for implementing the preceding operations on a read/write lock is tokeep track of the number of transactions that hold a shared (read) lock on an itemin the lock table. Each record in the lock table will have four fields: <Data_item_name,LOCK, No_of_reads, Locking_transaction(s)>. Again, to save space, the system needs tomaintain lock records only for locked items in the lock table. The value (state) ofLOCK is either read-locked or write-locked, suitably coded (if we assume no recordsare kept in the lock table for unlocked items). If LOCK(X)=write-locked, the value oflocking_transaction(s) is a single transaction that holds the exclusive (write) lockon X. If LOCK(X)=read-locked, the value of locking transaction(s) is a list of one ormore transactions that hold the shared (read) lock on X. The three operationsread_lock(X), write_lock(X), and unlock(X) are described in Figure 22.2. 2 As before,each of the three locking operations should be considered indivisible; no interleav-ing should be allowed once one of the operations is started until either the opera-tion terminates by granting the lock or the transaction is placed in a waiting queuefor the item.When we use the shared/exclusive locking scheme, the system must enforce the fol-lowing rules:1. A transaction T must issue the operation read_lock(X) or write_lock(X) beforeany read_item(X) operation is performed in T.2. A transaction T must issue the operation write_lock(X) before anywrite_item(X) operation is performed in T.2These algorithms do not allow upgrading or downgrading of locks, as described later in this section. Thereader can extend the algorithms to allow these additional operations.read_lock(X):B: if LOCK(X) = “unlocked”then begin LOCK(X) ← “read-locked”;no_of_reads(X) ← 1endelse if LOCK(X) = “read-locked”then no_of_reads(X) ← no_of_reads(X) + 1else beginwait (until LOCK(X) = “unlocked”and the lock manager wakes up the transaction);go to Bend;write_lock(X):B: if LOCK(X) = “unlocked”then LOCK(X) ← “write-locked”else beginwait (until LOCK(X) = “unlocked”and the lock manager wakes up the transaction);go to Bend;unlock (X):if LOCK(X) = “write-locked”then begin LOCK(X) ← “unlocked”;wakeup one of the waiting transactions, if anyendelse it LOCK(X) = “read-locked”then beginno_of_reads(X) ← no_of_reads(X) −1;if no_of_reads(X) = 0then begin LOCK(X) = “unlocked”;wakeup one of the waiting transactions, if anyendend;22.1 Two-Phase Locking Techniques for Concurrency Control 781Figure 22.2Locking and unlockingoperations for two-mode (read-write orshared-exclusive)locks.3. A transaction T must issue the operation unlock(X) after all read_item(X) andwrite_item(X) operations are completed in T.34. A transaction T will not issue a read_lock(X) operation if it already holds aread (shared) lock or a write (exclusive) lock on item X. This rule may berelaxed, as we discuss shortly.3This rule may be relaxed to allow a transaction to unlock an item, then lock it again later.782 Chapter 22 Concurrency Control Techniques5. A transaction T will not issue a write_lock(X) operation if it already holds aread (shared) lock or write (exclusive) lock on item X. This rule may also berelaxed, as we discuss shortly.6. A transaction T will not issue an unlock(X) operation unless it already holdsa read (shared) lock or a write (exclusive) lock on item X

Which lock is a default locking mechanism in oracle?

The process of locking records to prevent multiple people from making changes to the same record in the database is known as _______________. A. concurrency control B. backend processing C. record control D. record locking

We need to implement locks to deal with concurrency. Locks that are placed by user command are known as ______ locks. (HINT: WRITE ANSWER IN CAPITAL LETTERS)

List the various levels of locking?