Because databases may suffer contention from multiple concurrent attempts to create, read, update and delete data they implement locking strategies. Among these, are binary, shared and exclusive locks. Shared locks are suitable for read operations. “A binary lock has only two states: locked (1) or unlocked (0)” (Coronel et al, 2011, p430).

As the name implies, multiple shared locks can be allowed at the same time. Therefore they are appropriate for read operations. Exclusive locks, on the other hand, are suitable for write operations as there is only one exclusive lock allowed at one time, and possessing it allows a process to execute data changes whereas not having it should prohibit a process from doing so.

Since supporting both shared and exclusive locks requires more than a binary state such a mechanism is generally inadequate for them. Assuming such a system will have an unlocked state as well, it is evident that at least three lock states will be required. The advantages to storage and performance that keeping lock state in a single bit provide may make some sense, but with the high-performance computing platforms available today the odds would seem to be against profiting from doing so in the database engine itself. However, the code that interfaces with the user and the database could profit from such locks, especially if it is running in such a distributed environment as web infrastructure (Nielsen, 1999). In addition, the possibility that read-only, or shared, locks may be irrelevant in many environments would make binary locks adequate.

Shared locks are of critical importance if transactions lack true atomicity. That is to say, if process A were allowed to read data with the intent to modify it, then B were allowed to modify it and write it back integrity would be in a very precarious position. Without shared locking processes are unaware of one and other's reads. Mandatory exclusive locking could ameliorate this however; if no process were allowed to read data with the intent to write it without an exclusive lock, shared locks might be obsolete. But Pennarun may not be the only application developer to opine of mandatory locks “stay far, far away, for total insanity lies in wait” (2010). The behavior he describes sounds as bad as no locking at all.

Exclusive locks are the real solution to data integrity, whether at a middleware layer, at the database layer or in the operating system itself. Since, by their very nature they are bottlenecks it behooves designers of every layer to implement them against the smallest entity possible. This, however, must be balanced against the fact that in a dynamic environment many processes might contend to write to a record. Therefore, attribute level exclusive locking could be a Very Bad Thing in databases with derived attributes or if ever implemented incorrectly against records with foreign keys. If, for example, price is derived from base_cost and discount, a change to either in a database with attribute level locking would require locking both. So some logic will be necessary to follow these dependencies. Likewise, modifying a foreign key might require locking it in (at least) two tables, depending on the database's underlying storage mechanism. Therefore record-level locking seems simpler and safer; but the same sort of dependencies may exist between records. In addition, exclusively locking anything larger than a record could well lead to unacceptable contention in a heavily utilized data-store. This is where the concepts of optimistic and pessimistic strategies may come in to play. Pessimistic locking operates under the assumption “that users are highly likely to corrupt each others' data” while optimistic locking holds that “such data collisions will occur rarely” (Johnson, 2003). Balancing the safety of pessimistic locking with the performance of optimistic locking could help us use shared and exclusive locking so as to balance performance and dependability.

Coronel, C., Morris, S. & Rob, P. (2011) Database Systems, 9^th ed. Congage Learning: Course Technology

Johnson, R. (2003) 'Common Data Access Issues', Expert One-on-One J2EE Design and Development [Online]. Available from: http://learning.infocollections.com/ebook%202/Computer/Programming/Java/Expert_One-on-One_J2EE_Design_and_Development/6266final/LiB0080.html

Nielsen, H.F. (1999) Editing The Web – Detecting the Lost Update Problem Using Unreserved Checkout [Online]. Available from: http://www.w3.org/1999/04/Editing/ (Accessed: 13 August, 2011)

Pennarun, A. (2010) Everything you never wanted to know about file locking [Online]. Available from: http://apenwarr.ca/log/?m=201012#13 (Accessed: 13 August, 2011)