Database Management

Shared Locks

One type of lock is the shared lock. Shared locks exist when two transactions are granted read access. One transaction gets the shared lock on data and when the second transaction requests the same data it is also given a shared lock. Both transactions are in a read-only mode, updating the data is not allowed until the shared lock is released. There is no conflict with the shared lock because nothing is being updated. Shared locks last as long as they need to last; it depends on the level of the transaction that holds the lock. IBM describes that transactions using TRANSACTION_READ_COMMITTED isolation level release the lock when the transaction steps though the next row. TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ isolation level hold the lock until the transaction is committed. A SELECT can prevent updates if a commit is never issued. But TRANSACTION_READ_UNCOMMITTED isolation levels do not request any locks .

Shared locks gives data access more efficiency, and increases the lock managers overhead because the type of lock must be known before a lock can be granted, three types of lock operations exist: READ_LOCK, WRITE_LOCK, UNLOCK, and the schema is enhanced to allow a lock upgrade and downgrade.

There are two main problems that can occur in the use of locks, the transaction schedule may not be serializable (consistent results) and the schedule may create deadlocks (two transactions waiting for the other to unlock data). Both can be dealt with: serializability using the Two-phase locking and deadlock using a deadlock detection and prevention.