Database Locks

Locks are used in a database management system (DBMS) to attempt to ensure that no conflicts arise during the processing of data (transactions). Locks are represented by a variable for each piece of data that indicates its current status which informs the DBMS the types of processes (if any) that can be carried out. Taking a simple EMPLOYEE database as our example with the following fields: EMP_CODE (PK), EMP_FNAME, EMP_INITIAL and EMP_LNAME.

A binary lock, as its name suggests, can have two states, either on or off, usually represented by a ‘0’ for unlocked and available (may be used in processes) or a ‘1’ for locked and unavailable (may not be used in processes). If one user was editing the EMP_LNAME field in a particular record then this record’s lock value variable would be set to ‘1’ (locked to other uses and other requests must wait) once the user had taken ownership of the process and this would not be released until the process was complete, when the record’s lock value variable would be set to ‘0’ (available for other uses). In my experience binary locks are too simplistic to be of use in practice as they do not take into account the context of the use. For example, this lock would not allow someone to view a record if it was in the process of being edited when this usage could not cause conflict and would normally be an acceptable process.

Shared/exclusive locks (also known as read/write locks) are used much more in practice (along with other lock based techniques to improve functionality and performance) as transactions other than the one instigating the lock are allowed access, depending on how the data is locked.

In a shared (read) lock, other transactions are allowed to read the item in question. This would mean that if one user was reading the EMP_LNAME field in a particular record then this record’s lock value variable would be set to a value that indicated ‘shared’ (may be read) once the user had taken ownership of the process and this would not be released until the process was complete, when the record’s lock value variable would be set to a value that indicated ‘available’ (available for other uses).

In an exclusive (write) lock, other transactions are prevented from writing to the locked data. This would mean that if one user was writing to the EMP_LNAME field in a particular record then this record’s lock value variable would be set to a value that indicated ‘exclusive’ (locked to other write based requests) once the user had taken ownership of the process and this would not be released until the process was complete, when the record’s lock value variable would be set to a value that indicated ‘available’ (available for other uses).

References

Coronel, Morris & Rob (2009) Database Systems: Design, Implementation, and Management (9th Edition). Cengage Learning.

Software Concepts International (n.d.) DBMS Locking Part I [Online]. ]. Available at http://sciinc.com/remotedba/techinfo/articles/pi3a5.asp (Accessed 09 May 2010).