Misplaced Pages

This is an old revision of this page, as edited by 2001:16a4:257:5f35:c00c:be8c:602:4553 (talk) at 18:43, 5 July 2024. The present address (URL) is a permanent link to this revision, which may differ significantly from the current revision.

This article needs additional citations for verification. Please help improve this article by adding citations to reliable sources. Unsourced material may be challenged and removed. Find sources: "Record locking" – news · newspapers · books · scholar · JSTOR (December 2009) (Learn how and when to remove this message)

Record locking is the technique of preventing simultaneous access to data in a database, to prevent inconsistent results.

The classic example is demonstrated by two bank clerks attempting to update the same bank account for two different transactions. Clerks 1 and 2 both retrieve (i.e., copy) the account's [[Record (database)|a transaction. Clerk 2 applies a different on the original , overwriting the transaction entered record no longer reflects the first transaction, as A simple way to prevent this is to [[File locking|lock record to be processed at a time, who need to edit records to edit a database also prevent inconsistencies created by unrestricted access, a single record can be 'when retrieved for editing or updating. Anyone attempting to retrieve editing is denied write access because of , depending on the implementation, they may be able to view it). Once the record is saved or edits are canceled, the lock is released. Records can never be saved so as to overwrite other changes, preserving data integrity.

In database management theory, locking is used to implement isolation among multiple database users. This is the "I" in the acronym ACID.

A thorough and authoritative description of locking was written by Jim Gray.

Granularity of locks

If the bank clerks (to follow the illustration above) are serving two customers, but their accounts are contained in one ledger, then the entire ledger, or one or more database tables, would need to be made available for editing to the clerks in order for each to complete a transaction, one at a time (file locking). While safe, this method can cause unnecessary waiting.

If the clerks can remove one page from the ledger, containing the account of the current customer (plus several other accounts), then multiple customers can be serviced concurrently, provided that each customer's account is found on a different page than the others. If two customers have accounts on the same page, then only one may be serviced at a time. This is analogous to a page level lock in a database.

A higher degree of granularity is achieved if each individual account may be taken by a clerk. This would allow any customer to be serviced without waiting for another customer who is accessing a different account. This is analogous to a record level lock and is normally the highest degree of locking granularity in a database management system.

In a SQL database, a record is typically called a "row".

The introduction of granular (subset) locks creates the possibility for a situation called deadlock. Deadlock is possible when incremental locking (locking one entity, then locking one or more additional entities) is used. To illustrate, if two bank customers asked two clerks to obtain their account information so they could transfer some money into other accounts, the two accounts would essentially be locked. Then, if the customers told their clerks that the money was to be transferred into each other's accounts, the clerks would search for the other accounts but find them to be "in use" and wait for them to be returned. Unknowingly, the two clerks are waiting for each other, and neither of them can complete their transaction until the other gives up and returns the account. Various techniques are used to avoid such problems.

Use of locksbetween the entities requesting the and no other entity is )

. Data reading, without editing facilities, does

Exclusive locks a single writing to

• ensure t shortest time possible;
• not hold the lock across system or function calls where on the processor to d
• ensure that if the entity

Shared locks differ from exclusive locks in that eks allow all holders to read the contents of the be changed until after the lock has been released by all holders. Exclusive locks cannot be obtained when a record is already locked (exclusively or shared) by another entity.

If lock requests for the same entity are queued, then once a shared lock is granted, any queued shared locks may also be granted. If an exclusive lock is found next on the queue, it must wait until all shared locks have been released. As with exclusive locks, these shared locks should be held for the least time possible.

See also

• Readers–writer lock

References

1. Gray, Jim & Reuter, Andreas (1993), Distributed Transaction Processing: Concepts and Techniques, Morgan Kaufmann, pp. 375–437, ISBN 1-55860-190-2

• Concurrency control
• Transaction processing