Database Glossary – Power Builder

Concurrency

Several clients sharing a resource

Contention

Multiple users try to access a resource at the same time resulting in waits or event deadlocks.

Deadlocks

A deadlock occurs when two or more transactions are both in a wait state, with each one waiting for the other to release a lock before it proceeds.

Deadlocks are automatically detected and resolved by the DBMS. The DBMS rolls back one of the transaction usually the one with the least amount of CPU time.

Exclusive lock

A DBMS always places exclusive locks whenever a transaction performs an update operation (insert, update, delete)

A transaction cannot obtain an exclusive lock on a page or row while other transaction has any lock (shared or exclusive)

A DBMS holds an exclusive lock for the duration of a transition (until a COMMIT or ROLLBACK)

Lock Granularity

Identifies the lowest level at which a DBMS provides locking. Some DBMS offer locking at the row level while others do so at the physical database page level

Optimistic concurrency

The updated rows are checked to determine if another user has changed them since they were originally read. If the data has been updated by someone else, the transaction is rolled back and a message informs the user accordingly.

This technique assumes that the likelihood that two users will attempt to select and update the same information at the same time is low. In such cases, it also accepts that one user may lose his or her updates.

Pessimistic concurrency

Is implemented using a database management system’s locking facility to acquire and leave locks on the rows being read. A user is guaranteed data consistency because no other user can update the data while it is locked.

Pessimistic concurrency is used when the likelihood that other users will update the same data is high and absolute isolation between users is required. This type of control comes at the expense of concurrency. The can be significant with DBMS using page locking.

Shared Lock

Some DBMS place shared locks on pages while they are being read after a transaction issues a SELECT statement, and then releases them after reading each page or row.

A transaction cannot obtain a shared lock on a page while another transaction has an exclusive lock on it. However, multiple transactions can obtain shared locks on the same page or row.