Database - Lock

Lock is a database mechanism to enforce data consistency in a multiuser database environment with then multiple concurrent transactions.

In general, multiuser databases use some form of data locking to solve the problems associated with:

• data concurrency,
• data consistency.
• and integrity.

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource.

This helps maintain the integrity of the data by ensuring that only one process at a time can modify the same data.

Such locks can be applied on:

• a row level,
• a page (a basic data block),
• a extent (multiple array of pages)
• or even an entire table.

In basic file system files or folders, only one lock at a time can be set, restricting the usage to one process only. Databases, on the other hand, can set and hold multiple locks at the same time on the different levels of the physical data structure.

The number, nature of locks and time the lock holds a data block can have a huge impact on the database performances. Bad locking can lead to disastrous performance response (usually the result of poor SQL requests, or inadequate database physical structure)

Most DBMS systems use :

• shared
• and exclusive

locks.

Exclusive locks mean that no other lock can acquire the current data object as long as the exclusive lock lasts. DBMSs usually set exclusive locks when the database needs to change data, as during an UPDATE or DELETE operation.

Shared locks can take ownership one from the other of the current data structure. Shared locks are usually used while the database is reading data (during a SELECT operation).

Databases can also be locked for other reasons, like access restrictions for given levels of user. Some DBAs also lock databases for routine maintenance, which prevents changes being made during the maintenance.

The isolation level of the data server enforces default locking behaviour. Changing the isolation level will affect how shared or exclusive locks must be set on the data for the entire database system. Default isolation is generally 1, where data can not be read while it is modified, forbidding the return of “ghost data” to end users.

At some point intensive or inappropriate exclusive locking can lead to a deadlock situation between two locks, where none of the locks can be released because they try to acquire resources mutually from each other.

• database