Lock (database)

From Wikipedia, the free encyclopedia

This article does not cite any references or sources. (December 2007) Please help improve this article by adding citations to reliable sources. Unverifiable material may be challenged and removed.

This article or section needs copy editing for grammar, style, cohesion, tone or spelling. You can assist by editing it now. A how-to guide is available. (December 2007)

A lock is used when multiple users need to access a database concurrently. This prevents data from being corrupted or invalidated when multiple users try to write to the database. Any single user can only modify those database records (that is, items in the database) to which they have applied a lock that gives them exclusive access to the record until the lock is released.

There are two mechanisms for locking data in a database: pessimistic locking, and optimistic locking. Pessimistic locking is where a record or page is locked immediately when the lock is requested, while an optimistic lock is where a record or page is only locked when the changes made to that record are updated. The latter situation is only appropriate when there is less chance of someone needing to access the record while it is locked; otherwise it cannot be certain that the update will succeed because the attempt to update the record will fail if another user updates the record first. With pessimistic locking it is guaranteed that the record will be updated.

A complaint about very old versions of MS SQL Server concerns an apparent lack of row-level locking capabilities. Until version 6.5 (released in 1996), all locking was done at the page level (a 2K unit of data). The problem with a page-level locking mechanism surfaces when multiple records exist on the same page and multiple processes must access information on the same page. If the page is locked by a process, other processes must wait to access the data. These situations may result deadlocks.

The degree of locking can be controlled by isolation level.