Granularity of Locks and Degrees of Consistency in a Shared Database. J.N. Gray, R.A. Lorie, G.R. Putzolu, I.L. Traiger 1977 (Presentation by Randy Ellis). Topics to be Discussed:. * Transaction definition * Database Object Hierarchy * Locking Types * Locking Compatibility
Granularity of Locksand Degrees of Consistencyin a Shared Database
J.N. Gray, R.A. Lorie, G.R. Putzolu, I.L. Traiger1977
(Presentation by Randy Ellis)
Topics to be Discussed:
An initial definition of “Transaction”:
The ability to have one transaction work with one set of resources while another transaction “Simultaneously” works with others
The amount of file structure, memory and processing time needed to keep those different transactions from affecting each other undesirably
The DBMS Structure Waterfall:
Shows resources that different transactions must “share”.
The “Simplified” DBMS Structure Waterfall:
Now this is something we can work with!
Still shows resources that different transactions must “share”.
Is a DIRECTED ACYCLIC GRAPH (DAG)
Each resource instance has a class type name and a unique ID/Name (e.g. Database:Accounting, Table:Salaries)
Each resource must be reached THROUGH A PARENT!
Transactions protect each other from changes by “Locking” the resources (generally)
Note: Two ways to reach a record!
Types of Database Locks:
Null(NL)No locks for the node
Exclusive/Write(X)Prevents other transactions from replacing changes that haven't been committed
And MAY prevent other transactions from reading changes that haven't been committed
Share/Read(S)Prevents other transactions from overwriting data it is currently looking at.
Intention Locks(I_)Set on ancestors to denote we "intend" to set the real thing on one of its descendents. Therefore intention locks have no use on leaf nodes.
We have to set sentinels higher up in the digraph to prevent conflict with transactions arriving at our node via other paths.
We could use real locks higher up but the use of "Intention" locks allows us to work with that node, and still avoid changes that might conflict with the lock further down -- this increases our concurrency.
The combination of intention locks on the ancestors and real locks on the descendents is equivalent to the use of real locks only on the desired node.
When a transaction attempts to create a new lock on an object that already has locks, the locking mechanism determines if the new lock is "compatible" with the existing one based on the types of the new and existing locks.
If compatible: The new lock will be set and the transaction will be allowed to proceed.
1) The transaction may be given an error (non-blocking)
to relay the user
2) OR The transaction may be suspended and
enqueued on the object until the
incompatible lock(s) are released
Note: If the locks are never removed the
locking mechanism will timeout and
check for deadlock - failing one of
the two transactions to correct the situation.
A more thorough definition of Transaction:
Some UNITS OF WORK may be a single statement, but others may span multiple statements that depend on each other (e.g. if one statement within the unit of work fails, so should the others.)
Therefore DBMS's give us the ability to bundle statements into logical groups called TRANSACTIONS, which have well defined starting and ending points controlled by the DBMS client so that it can guarantee the logical units of work move the database from one consistent state to another.
As work is done within a transaction the original values are preserved and clients can end a transaction by rolling back (undoing) all work performed since the transaction was begun OR by committing that work which makes it available to all future transactions (and abdicates the right to rollback that work).
Data that has been added, changed or deleted within a transaction but has not yet been committed can be called "dirty" data.
The transaction creates "locks" to signal other clients’ transactions that this transaction is changing the data or requires data it has read to remain unchanged (usually).
Transactions help to guarantee the database is VALID and RECOVERABLE by providing mechanisms that:
1) Guarantee data the transaction is working with will not change during the transaction.
2) Remember work the transaction performed so the database can be rolled back to the last consistent state (before the transaction began) if a “bad thing” happens while running the transaction.
Degrees of consistency (Isolation):
The "degree of consistency" desired by a client depends on how it needs to isolate its view of the database from other transactions -- hence it is also called the "isolation level“.
DBMS’s use different combinations of locks and transaction/audit log entries to effect their degree of consistency.
Sometimes it is not required that a transaction’s data be “perfectly” consistent. When this is the case, we can reduce overhead and increase concurrency by telling the DBMS to use a lower isolation level when beginning a transaction.
The Isolation level of the transaction then guides the DBMS when setting and respecting locks.
All Transactions must adhere to level 0 and create “well-formed” locks (set lock before touching data)
“The principle application of dependency definition is as a proof technique and for discussing schedules and recovery issues”
Transactions can work with the same data!
And - the order in which transactions set locks (which is controlled by the degree of consistency) affects how well a transaction can be recovered when “something bad” happens.
So- along with maintaining locks, a DBMS also needs to keep track of WHEN locks are established and data is changed. Most DBMS’s employ a “Transaction Log” to handle this.
When “something bad” happens. . .
Transactions make sure to follow an exact order, establish lock, update log, update record, commit, unlock – this “well-formed”, “two-phase” protocol guarantees changes are safely in the log if we crash.
Periodically, the DBMS suspends commits, does a quick flush of all committed changes, and records a “check point” in the log – so the whole log need not be replayed again.
If we need to recover – we clear all the dirty data from the db and cache and replay the log from the last checkpoint according to guidelines to the left:
Whenever multiple users have simultaneous access to the database, additional overhead (usually in the form of locking) must be added to avoid conflicts.
Locks can be granularized at different levels of objects within the database – those objects are organized into a acyclic hierarchy to promote consistency.
Locks come in different flavors. The protocol suggested in this paper uses Read and Write locks with an Intention modifier. A table specifying the compatibility rules between these locks was established.
A transaction is a logical grouping of all operations into a single unit of work. The DBMS automatically maintains locks and log entries for the transaction to keep it from conflicting with other transactions and enabling it to recover if failure is encountered.
Transactions are “Isolated” from each other based on a desired degree of consistency given by the client. Isolation controls how locks are established and respected.
The order of transactions differs at different isolation levels and this ordering can affect the recoverability of the database. Dependency identification rules were addressed and recovery plans were discussed.
Look at the readings book, page 189 it pretty much sums it all up nice and neat!