Locking fundamentalsFundamental tool of concurrency controlObtain lock before accessing an itemWait if a conflicting lock is held

Shared lock: conflicts with exclusive locksExclusive lock: conflicts with all other kinds of locks

Concurrency control manager maintains the lock table(which type of lock,on which table,usernm)

Concurrency control techq• Concurrency control and locking is the mechanism used by DBMSs for the sharing

of data. • Atomicity, consistency, and isolation are achieved through concurrency control

and locking. • When many people may be reading the same data item at the same time, it is

usually necessary to ensure that only one application at a time can change a data item. Locking is a way to do this.

• Because of locking, all changes to a particular data item will be made in the correct order in a transaction.

• Different types of locks: 1.page locking 2. class/table locking 3. instance/row locking

• Page locking In this, all the data on a specific page are locked. A page is a common unit of storage in computer systems and is used by all types of DBMSs.

• Class or table locking means that all instances of either a class or table are locked, It represents all instances of a class, regardless of the page where they are stored.

• Instance /row locking locks a single relational tuple in an RDBMS or a single object in an ODBMS

Locking granularity

Database

Table

Page

Row

Column

Index

Locking concept• For any db , if more than one user, deals with locking

conflicts when two or more users try to change the same row in the database.

• The most common way in which access to items is controlled is by “locks.”

• Lock manager is the part of a DBMS that records, for each item I, whether one or more transactions are reading or writing any part of I.

• If so, the manager will prohibit another transaction from gaining access to I, provided the type of access (read or write) could cause a conflict, such as the duplicate selling of an airline seat.

Understanding locks and transactions• Locks prevent multiple users from changing the same

data at the same time. • if one or more rows in a table can be changed, the user

executing the DML statement must obtain a lock on the row or rows

• a lock gives the user exclusive control over the data until the user has committed or rolled back the transaction that is changing the data.

• In Oracle 10g, a transaction can lock one row, multiple rows, or an entire table.

• One can manually lock rows & Oracle automatically locks the rows needed at the lowest possible level to ensure data integrity

• Hence conflicts with other transactions that may need to access other rows in the table are minimized

LOCK TABLE statement• Allows a user to explicitly acquire a shared or exclusive table lock on the

specified table. The table lock lasts until the end of the current transaction.

• Explicitly locking a table is useful for:- avoiding the overhead of multiple row locks on a table (in other words, user-initiated

lock escalation) avoiding deadlocks You cannot lock system tables with this statement.

• Syntax• LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE• Once a table is locked in either mode, a transaction does not acquire any

subsequent row-level locks on a table. For example, if a transaction locks the table in share mode in order to read data, a particular statement might need to lock a particular row in exclusive mode in order to update the row.

Packaged Applications and Locking(ex. of locking)

• The HR department recently purchased a benefits management package that interfaced well with our existing employee management tables; however, once they started using the application, other users that accessed the employee tables started complaining of severe slowdowns in updates to the employee information.

• Reviewing the CPU and I/O usage of the instance did not reveal any problems; it wasn’t until I looked at the locking information that I(author) noticed a table lock on the employees table whenever the benefits management features were being used!

• The benefits management application was written to work on a number of database platforms, and the least capable of those platforms did not support row locking.

• As a result, no one could make changes to the employees table whenever an employee’s benefits were being changed, and everyone had to wait for the benefits changes to complete.

example

• If multiple users require a lock on a row or rows in a table, the first user to request the lock obtains it, and the remaining users are enqueued using a first-in, first-out (FIFO) method. At a SQLprompt, a DML statement (INSERT, UPDATE, DELETE, or MERGE) that is waiting for a lock on a resource appears to hang, unless the NOWAIT keyword is used in a LOCK statement.

Locking in oracle• You can explicitly obtain locks on individual rows by using the SELECT … FOR

UPDATE statement, as you can see in the following example: SQL> select * from hr.employees where manager_id = 100 for update;• This query not only shows the rows that satisfy the query conditions, it also locks

the selected rows and prevents other transactions from locking or updating these rows until a COMMIT or a ROLLBACK occurs.

• NOWAIT Mode Using NOWAIT in a LOCK TABLE statement returns control to the user immediately if any locks already exist on the requested resource, as you can see in the following example:

• SQL> lock table hr.employees in share row exclusive mode nowait; • lock table hr.employees • ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified • This is especially useful in a PL/SQL application if an alternate execution path can

be followed if the requested resource is not yet available. NOWAIT can also be used in the SELECT … FOR UPDATE statement.

Locks

small subset of the items may have locks on them at any one time, the lock manager can store the current locks in a lock table which consists of records (<item>,<lock type>,<transaction>

The meaning of record (I,L,T) is that transaction T has a lock of type L on item I.

Example of locks Lets consider two transaction T1 and T2. Each

accesses an item A, which we assume has an integer value, and adds one to A.

Read A;A:=A+1;Write A;-----------------------------------------------------------T1: Read A A:=A+1 Write AT2: Read A A:=A+1 Write A-----------------------------------------------------------

Example of locks contd…

• The most common solution to this problem is to provide a lock on A.

• Before reading A, a transaction T must lock A, which prevents another transaction from accessing A until T is finished with A.

• Furthermore, the need for T to set a lock on A prevents T from accessing A if some other transaction is already using A.

• T must wait until the other transaction unlocks A, which it should do only after finishing with A.

Concurrent access with transactionAgent A Agent B … begin transaction read account 754 ($314.60) … update account 754 ($264.60) commit

… Begin transaction wait wait … wait read account 754 ($264.60) … update account 754 ($214.60) commit

Deadlock(mutual waiting)Transaction A Time Transaction B

XLock SR1 T1

T2 XLock SR2

XLock SR2 (wait) T3

T4 XLock SR1 (wait)

deadlocks

• A deadlocks involves a chain of transactions that are cyclically waiting for each other to release a lock.

• The DBMS detects deadlock with a transaction dependency graph. It resolves the impasse by sacrificing one of the transactions in cycle.

Deadlocks and transaction dependency graph

T1 T2

…begin transaction…get shared lock on account 754 tuplelock grantedread account 754 ($314.60)

get exclusive lock on account 754 tuplewait

waitwaitwait

…begin transaction…

get shared lock on account 754tuplelock grantedread account 754 ($314.60)

get exclusive lock on account 754tuplewaitwait

Detecting and Resolving Lock Conflicts

• Although locks are a common and sometimes unavoidable occurrence in many databases, they are usually resolved by waiting in the queue.

• In some cases, you may need to resolve the lock problem manually (for example, if a user makes an update at 4: 59 P.M. and does not perform a COMMIT before leaving for the day).

Detecting Lock Conflicts

• Detecting locks in Oracle 10g to see who is locking what resource.

• Example: Execute the following statement:• SQL> lock table hr.employees, hr.departments in

exclusive mode; Table(s) Locked.

• SCOTT has an EXCLUSIVE lock on both the EMPLOYEES and DEPARTMENTS table.

• You can drill down on the locked object by clicking one of the links in the Object Name column; similarly, you can review other information about SCOTT’s session by clicking one of the links in the Session ID column.

Deadlock in oracle• A deadlock is a special type of lock conflict in which two or more

users are waiting for a resource locked by the other users. • As a result, neither transaction can complete without some kind of

intervention:-• the session that first detects a deadlock rolls back the statement

waiting on the resource with the error message ORA-00060: Deadlock detected while waiting for resource.

• After the error message is issued at 11:45, the second UPDATE for Session 1 does not succeed;

• however, the second UPDATE for Session 2 completes, and the user in Session 2 can now submit another DML statement or issue a COMMIT or ROLLBACK.

• The user in Session 1 will have to re-issue the second UPDATE.•

Choosing a locking strategy• Lock table manually overrides default locking• When a lock table issued on a view,the underlying tables are locked• Lock table emp_tab,dept_tab in EXCLUSIVE MODE NOWAIT;• Lock table emp_tab in ROW SHARE MODE;• Lock table emp_tab in ROW EXCLUSIVE MODE;• They offer highest degree of concurrency; you might use them

– Your transaction needs to prevent another transaction from acquiring or exclusive table lock for a table before table is updated in your transaction

– No other transaction can update the table until transaction commits/rolls back

– Your transaction needs to prevent from being altered/dropped before the table can be modified

– To lock with share mode:- Lock table in share mode;

Choosing a locking strategy Practically• Lock table dept_tab in share mode;• Update emp_tab set sal=sal*.1 where deptno in(select deptno from dept where

loc=‘mum’);• Update budget_tab set total=tal*.1 where deptno in(select deptno from dept

where loc=‘mum’;• Commit;• To lock table in share row exclusive mode :-

– Lock table emp_tab in SHARE ROW EXCLUSIVE MODE;– SHARE ROW EXCLUSIVE MODE this can be used if transaction level read

consistency for specified table and the ability to update the locked table is required

– If other table acquire explicit row locks (using select.. For update) which might make update and insert stmt in locking transaction wait and might cause deadlocks

– In exclusive mode:- lock table <table name) in EXCLUSIVE MODE;

Serial equivalent order on conflict• Three ways to ensure a serial-equivalent order on conflicts:

– Option 1, execute transactions serially.• “single shot” transactions

– Option 2, pessimistic concurrency control: block T until transactions with conflicting operations are done.

• use locks for mutual exclusion• two-phase locking (2PL) required for strict isolation

– Option 3, optimistic concurrency control: proceed as if no conflicts will occur, and recover if constraints are violated.

• Repair the damage by rolling back (aborting) one of the conflicting transactions.

– Option 4, hybrid timestamp ordering using versions.

Pessimistic concurrency control• Pessimistic concurrency control uses locking to prevent illegal conflict

orderings.• avoid/reduce expensive rollbacks

– Well-formed: acquire lock before accessing each data item.• Concurrent transactions T and S race for locks on

conflicting data items (say x and y)....• Locks are often implicit, e.g., on first access to a data

object/page.– No acquires after release: hold all locks at least until all

needed locks have been acquired (2PL).• growing phase vs. shrinking phase

– Problem: possible deadlock.• prevention vs. detection and recovery

What is 2PC(Two phase Commit)?

•Concurrency control in the distributed db environment is important because multistate, multiprocess operations are likely to create data inconsistencies.•For ex. a TP component of a DDBMS must ensure that all parts of transaction are completed at all sites before a final COMMIT is issued to record the transaction.•Suppose each transaction operation was committed by each client(distributed processing), but one of them must not commit the transaction’s results•Such a scenario would create an inconsistent state,because of integrity problems,because uncommitted data can not be committed.• The solution for the same is given by two phase commit protocol

Two phase commit• Distributed db allow transaction to be done at

all sites. • A final commit must not be issued until all

sites have committed their parts of transaction.

• Two PC guarantees that if a portion of a transaction operation can not be committed,all changes made at other sites participating in the transaction will be undone to maintain a consistent db state

2PC• Each Distributed processing(dp) site maintains its own transaction log• The two pc protocol requires that the transaction entry log for each DP be written

before the db fragment is actually updated• The Do-Undo-Redo protocol is used by dp to rollback/roll forward transaction with

the help of system’s transaction log entries• It defines three types of operations :-

– DO performs the operation and records the before and after values in the transaction log

– UNDO reverses an operation ,using the log entries written by DO portion of the sequence

– REDO redoes an operation,using the log entries written by the DO portion of the sequence

To ensure that DO,UNDO,REDO can survive a system crash while they are being executed,a write ahead log protocol is used.

It forces log entry to be written to permanent storage before the actual operation takes place.

Two PC• This protocol defines the operations between two types of nodes:- co-

ordinator and one or more subordinates• The participating nodes agree on a coordinator. The coordinator role is

assigned to the node that initiates the transaction• Phase 1:Preparation

– The coordinator sends a PREPARE to COMMIT mesg to all subordinates

– The subordinates receive the mesg,write the transaction log,using write ahead protocol and send an acknowledgement(YES/prepared to commit and NO/nor prepared)mesg to coordinator

– The coordinator makes sure that all nodes are ready to commit,or it aborts the action

• If all nodes are prepared to commit,the transaction goes to phase2. If one/more nodes reply NO,the co-ordinator broadcasts an ABORT mesg to all subordinates

TWO PC• Phase 2: the Final Commit

– The coordinator broadcasts a COMMIT mesg to all subordinates and waits for the replies

– Each subordinate receives the COMMIT mesg,then updates the db using DO protocol

– The subordinates reply with a COMMITTED or NOT COMMITTED mesg to the coordinator

If one/more subordinates did not commit, the coordinator sends an ABORT mesg,thereby forcing them to UNDO all changes

The objective of 2pc is to ensure that all nodes commit their part of the transaction otherwise the transaction is aborted

If one node fails to commit,the info necessary to recover the db is in the transaction log,the db is recovered with DO-UNDO-REDO protocol

2PL• 2PL defines how transactions acquire and

relinquish(release) locks. • Two phase locking guarantees serializability,but it

doesn’t provide deadlocks. The two phases are:-– A growing phase– A shrinking phaseA growing phase ,in which a transaction acquires all required

locks without unlocking any data. Once all locks have been acquired, the transaction is in its locked point

A shrinking phase,in which a transaction releases all locks and can not obtain any new lock

Two phase locking Rules• Two transactions can not have a conflicting locks• No unlock operation can precede a lock operation in

the same transaction• No data are affected until all locks are obtained- that

is,until the transaction is in its locked point• Diagram-rob n colonel pg.413• The transaction acquires all locks it needs until it

reaches its locked point• Two phase locking increases the transaction

processing cost and may not cause additional undesirable effects. One such effect is deadlock.

Why 2PL?• If transactions are well-formed, then an arc from T to S in the

schedule graph indicates that T beat S to some lock.• Neither could access the shared item x without holding

its lock.• Read the arc as “T holds a resource needed by S”.

• 2PL guarantees that the “winning” transaction T holds all its locks at some point during its execution.

Thus 2PL guarantees that T “won the race” for all the locks...

...or else a deadlock would have resulted.

T: R(A) W(A) R(C) W(C)S: R(A) R(C)

T SA

T SC

Why 2PL?• Consider our two transactions T and S:

– T: transfer $100 from A to C: R(A) W(A) R(C) W(C)– S: compute total balance for A and C: R(A) R(C)

• Non-two-phased locking might not prevent the illegal schedules.

T: R(A) W(A) R(C) W(C) S: R(A) R(C)

T SA

T: R(A) W(A) R(C)W(C)S: R(A) R(C)

T SC

A

C

More on 2PL(from Ramakrishnan)• The strict 2PL protocol allows only conflict serializable

schedules• Two schedules are said to be conflict equivalent if they

involve the same set of actions of the same transactions• Two actions conflict if they operate on the same data object

and at least one of them is write• If two schedules are conflict equivalent ,it is easy to see that

they have the same effect on the db• A schedule is conflict serializable if it is conflict equivalent to

some serial schedule. Every conflict serializable schedule is serializable,if we assume set of items in the db does not grow/shrink, values can be modified but items can’t added/deleted

2PL(from Ramakrishan)• Strict 2PL allows only conflict serializable schedules, is seen

from following two results:– A schedule S is conflict serializable if and only if its

precedence graph is acyclic– Strict 2PL ensures that the precedence graph for any

schedule that it allows is acyclic– Variant of strict 2PL called 2PL relaxes 2nd rule to allow

transactions to release locks before the end i.e. before commit or abort action

– 2PL 2nd rule is replaced by following rule :-• A transaction can not request additional locks once it

releases any lockhence every transaction has growing phase in which it acquires

locks,followed by shrinking phase in which it releases locks

Concurrency control by time stamps• Pg415 colonel• The time stamping approach to scheduling concurrent transactions

assigns a global,unique time stamp to each transaction• Time stamp value produces an explicit order in which transactions are

submitted to DBMS.• The stamps must have two properties: uniqueness & monotonicity• Uniqueness ensures that no equal time stamp values can exist• Monotonicity ensures that the time stamp values always increase(Obvious!)

• The DBMS executes conflicting operations in the time stamp order,thereby ensuring serializability of transactions

• If two transactions conflict,one is stopped,rolled back,rescheduled & assigned a new time stamp value

• Disadvantage is:- each value stored in the database requires two additional time stamp fields. One for the last time the field was read and one for last update. Time stamping increases memory needs and the db’s processing overhead

• It needs a lot of system resources because many transactions may have to be stopped ,rescheduled and restamped

Time stamp based concurrency control• In lock based concurrency control,conflicting actions of different

transactions are ordered by the order in which locks are obtained• In optimistic concurrency control,a timestamp ordering is imposed on

transactions and validations checks that all conflicting actions occurred in the same order

• The optimistic approach does not require locking or time stamping techq. using an optimistic approach,each transaction moves thru two or three phases:-they are read, validation and write

• Read phase: reads the db,executes the needed computations,and makes the updates to private copy of db values. All update operations of the transaction are recorded in a temp.update file,which is not accessed by remaining transactions

• Validation phase:the transaction is validated to ensure that the changes made will not affect the integrity and consistency of the db.If validation test is +ve, the transaction goes to write phase else if it is –ve,then the transaction is restarted and changes are discarded

• During write phase: the changes are permanently applied to the database

Concurrency control without locking• Types of concurrency control without locking are:

– Optimistic – Pessimistic• The optimistic approach is acceptable for most read or query db systems that require

few update transactions• Deadlocks is difficult to avoid,therefore it is necessary to employ db recovery techq.to

restore the db to a consistent state• The management of deadlocks in terms of prevention and detection constitutes an

imp.db function• dealing with deadlocks(Detection):- in practice,db systems periodically check for

deadlocks. When a transaction Ti is suspended because a lock that it requests can not be granted,it must wait until all transactions Tj that currently hold conflicting locks release them.

• The locks manager maintains a structure called as waits-for graph to detect deadlock cycles

• The nodes correspond to active transactions, and there is an arc from Ti to Tj iff Ti is waiting for Tj to release a lock

• Alternative to maintaining a waits-for graph is to identify deadlocks through a timeout mechanism:- if a transaction has been waiting too long for a lock,we assume it is in a deadlock cycle and abort it

Optimistic and pessimistic approach• Locking protocols take a Pessimistic approach to conflicts between

transactions and use either a transaction abort or blocking to resolve conflicts

• Optimistic approach’s basic idea is that most transactions do not conflict with other transactions and the idea is to be permissive as possible in allowing transactions to execute.

• Transactions proceed in 3 phases:-– Read – the transaction executes , reading values from db and writing

to a private workspace– Validation- if the transaction decides it wants to commit,the dbms

checks with other transactions whether it has been conflicted. If it has, the transaction is aborted, the private workspace is cleared and it is restarted

– Write- if validation determines that there no possible conflicts,the changes to data objects made by the transaction in its private workspace are copied into the database.

Optimistic contd…(validation based protocol)

• Each transaction Ti is assigned a timestamp TS(Ti) at the beginning of validation phase,the validation criteria checks whether the timestamp ordering of transaction is equivalent serial order

• Known as optimistic concurrency control since transaction executes fully in the hope that all will go well during validation

• For every pair of transaction Ti and Tj such that TS(Ti)<TS(Tj), one of the following validation must hold:-

Ti completes(all 3phases) before Tj beginsTi completes before Tj starts its Write phase, and Ti does not write

any db object read by TjTi completes its Read phase before Tj completes its Read phase,

and Ti doesn’t write any db object that is either read or written by Tj

To validate Tj,we must check to see that out of these coonditions holds with respect to each committed transaction Ti such that TS(Ti)<TS(Tj). Each of these conditions ensure that Tj’s modifications are not visible to Ti.

Deadlock prevention• If there is a high level of conflict of locks, prevention based schemes could

perform better• We can prevent them by giving each transaction a priority and ensuring

lower priority transactions are not allowed to wait for higher priority transactions(vice a versa)

• One way to assign priorities is to give each transaction a timestamp when it starts up.

• The lower the timestamp, higher is the transaction’s priority i.e. oldest transaction has the highest priority(Bakery algo in OS)

• If transaction Ti requests a lock and transaction Tj holds a conflicting lock,the lock manager can use one of below policies:- Wait-die : if Ti has higher priority,it is allowed to wait,otherwise,it is aborted Wound-wait: if Ti has higher priority, abort Tj ;otherwise , Ti waits In Wait-die scheme ,lower priority transactions can never wait for higher priority

trnasactions In wound-wait scheme, higher priority transactions never wait for lower priority

transactions. In either case, no deadlocks cycle develops

Deadlock Recovery• When deadlock is detected :

– Some transaction will have to rolled back (made a victim) to break deadlock. Select that transaction as victim that will incur minimum cost.

– Rollback -- determine how far to roll back transaction• Total rollback: Abort the transaction and then restart

it.• More effective to roll back transaction only as far as

necessary to break deadlock.– Starvation happens if same transaction is always chosen as

victim. Include the number of rollbacks in the cost factor to avoid starvation

Conservative 2PL• A variant of 2PL,called conservative 2PL,which also prevents deadlock.

under this type, a transaction obtains all the locks it will never need when it begins, or blocks waiting for these locks to become available.

• This scheme ensures that there will be no deadlocks and a transaction that already holds some locks will not blocks waiting for other locks

• If lock contention(conflict) is heavy,conservative 2PL can reduce the time that locks are held on average, because transactions that holds locks are never blocked

• If lock contention is low, locks are held longer under Conservative 2PL.• From pract’s point of view,it is hard to know exactly what locks are

needed ahead of time and it may set more no.of locks than necessary• It also has higher overhead for setting locks because a transaction has to

release all locks and try to obtain them all over again if it needs• Hence this techq.is not used in practice

Thomas Write Rule• When Ti attempts to write data item Q, if TS(Ti) < W-timestamp(Q), then Ti

is attempting to write an obsolete value of {Q}. TS-Time stamp• if TS(T)<W-time stamp(O),the current write action has been made

obsolete(out of date) by the most recent write of O,which follows the current write according to time stamp ordering

• T’s write action as if it had occurred immediately before the most recent write of O and was never read by anyone

• Hence, rather than rolling back Ti as the timestamp ordering protocol would have done, this {write} operation can be ignored.

• Otherwise this protocol is the same as the timestamp ordering protocol.

• Thomas' Write Rule allows greater potential concurrency