Even though transactions are usually processed in parallel, they must appear to be processed in serial. The execution of a set of transactions is said to be serializable if it is equivalent to a serial execution of the same transactions.
(no futher detail in this course)
(Concurrency control)
Locking guarantees that only serializable executions occur.
Before a transaction can read or write, it must have a lock. There are two kinds of multiple mode locks:
If two or more transactions have a lock on the same object, those locks must be shared locks (i.e. you cannot have a shared and exclusive lock on the same object simultaneously). In some database designs locks may be converted from one type to another. A lock may be upgraded from a shared lock to an exclusive lock only if no other transaction has a shared lock on that object. A lock may be downgraded from an exclusive lock to a shared lock at any time.
In basic two-phase locking, a transaction acquires all locks before its first unlocking operation. Thus transactions have two phases:
Two phase locking guarantees that transactions are serializable. (It can be so proven - details not presented here!)
If a transaction cannot acquire a lock, it is blocked (forced to wait) until it can acquire a lock. This may limit concurrency - this is the price to pay to guarantee serializability.
In strict 2PL (2 phase locking) a transaction does not release any of its exclusive locks until after a commit or abort. In rigorous 2PL a transaction does not release any of its locks (exclusive or shared) until after a commit or abort.
A DBMS has a lock manager subsystem to control locking. Actual locks are stored in a lock table, which tracks the data item being locked, the transaction doing the locking, and the state of the lock. The DBMS's concurrency control subsystem controls the lock requests based upon the concurrency method being used. (i.e. the concurrency control subsystem issues the requestions for lock acquisitions and releases, the lock manager subsystem implements those requests by updating the lock table).
Transactions will not attempt to place a lock on items they already have locked (except in the case of conversions), nor will they attempt to unlock items they have currently do not have a lock on.
Deadlocks may occur with two-phase locking. Example:
The DBMS must abort one of the deadlocked transactions. There are a number of ways of defeating deadlock, none of which will be discussed here.
DBMS objects exist in a hierarchy of granularity, or size
Locks can be applied at different granularities
The larger the data item, the fewer locks needed (coarse granularity). Conversely, the smaller the data item, the more locks needed (fine granularity). There is a performance trade-off between the two. Coarse granularity means that more transactions will be blocked because larger data items are locked (ex: file lock means that even though only one record is being written to, all of the others are locked out), finer granularity means that the system has (possibly significant) more locks to deal with. Locks are kept in a lock table, and the more locks there are, the larger the table.
The granularity to use depends on the database. Small databases that do single record access are best off with fine granularity; large databases that do large data accesses are better off with a coarser (data block or file) granularity.
Multiple level granularity mixes different types of granularity as they are appropriate to the transaction. (Undetailed).