locks with updt nowait

18
Locks • Users manipulate oracle table data via sql/pl-sql stmt • An oracle transaction can be made up of single sql stmt or several sentences • The oracle table is global resource, as it is shared by several users • There is a need to ensure that integrity of data should be maintained each time when data is accessed • The simultaneous access to table data has to be granted without causing damage to the data

Upload: avnis

Post on 27-Jun-2015

420 views

Category:

Technology


2 download

TRANSCRIPT

Page 1: Locks with updt nowait

Locks• Users manipulate oracle table data via sql/pl-sql

stmt• An oracle transaction can be made up of single

sql stmt or several sentences• The oracle table is global resource, as it is shared

by several users• There is a need to ensure that integrity of data

should be maintained each time when data is accessed

• The simultaneous access to table data has to be granted without causing damage to the data

Page 2: Locks with updt nowait

Concurrency control

• This techq employed by Oracle engine to protect table data when several people are accessing it is called as concurrency control

• Oracle uses a method of locking to implement concurrency control when multiple users access a table for manipulation of data at the same time

Page 3: Locks with updt nowait

locking• Types of locks– Type of lock to be applied– Level of lock to be applied– Read Operation :- select stmt– Write Operation:- Insert,Update,Delete stmt– Since read doesn’t make any changes in a table and

are meant only viewing purpose– Read can be performed on the table without any

danger to the table’s data– Hence oracle engine places shared lock on a table

when its data is being viewed

Page 4: Locks with updt nowait

locking• Write operations cause a change in the table

data i.e. any insert,update or delete stmt affects table data directly and hence write operation can affect the data integrity

• Simultaneous write operation will cause loss of data consistency in the table

• Hence oracle engine places exclusive lock on a table on specific table’s resources when data is being written to a table

Page 5: Locks with updt nowait

Rules of locking• Data being changed can not be READ• Writers wait for other writers , if they attempt to

update the same rows at the same time• Two types of locks:-– Shared locks: shared locks are placed on resources

whenever a READ operation(select) is performed– Multiple shared locks can be simultaneously set on a

resource– Exclusive locks: they are placed on resources at a time

i.e. the first user who acquires an exclusive lock will continue to have the sole ownership of the resource and no other user can acquire an exclusive lock on that resource

Page 6: Locks with updt nowait

Levels of locks• Oracle provides locking– Row level– Page level– Table level– The oracle engine decides on the level of lock to be

used by the presence or absence of a where condition– If the where clause evaluates to one row in the

table,a row level lock is used– if the where clause evaluates to a set of data, a page

level lock is used– If there is no where clause, a table level lock(query

accesses entire table) is used

Page 7: Locks with updt nowait

Implicit lock & explicit lock• If client A fires an update cmd on a record in

prod_master then oracle will implicitly lock the record so that no further data manipulation can be done by any other user till the lock is released. It can be done by commit/rollback

• In the mean time, client B tries to view the same record, the oracle displays old set of data , this leads to wrong information

• Client A must explicitly lock the record , no other user can access the record even for viewing purpose will client A’s transaction is completed. This is explicit locking

Page 8: Locks with updt nowait

Explicit locking• Users can make explicit locking on which they

have been granted table privileges• Rows/tables can be explicitly locked by using

either select .. for update …stmt or lock table stmt

• The techq of lock taken or its resources by a user is called explicit locking

Page 9: Locks with updt nowait

Locking

Page 10: Locks with updt nowait
Page 11: Locks with updt nowait
Page 12: Locks with updt nowait
Page 13: Locks with updt nowait
Page 14: Locks with updt nowait
Page 15: Locks with updt nowait

Why and how Choosing a Locking Strategy• A transaction explicitly acquires the specified table

locks when a LOCK TABLE statement is executed. • A LOCK TABLE statement manually overrides default

locking. • When a LOCK TABLE statement is issued on a view, the

underlying base tables are locked.• The following statement acquires exclusive table locks

for the EMP_TAB and DEPT_TAB tables on behalf of the containing transaction:

• LOCK TABLE Emp_tab, Dept_tab IN EXCLUSIVE MODE NOWAIT;

• You can specify several tables or views to lock in the same mode; however, only a single lock mode can be specified for each LOCK TABLE statement.

Page 16: Locks with updt nowait

Locking..

• When a table is locked, all rows of the table are locked. No other user can modify the table.

• You can also indicate if you do or do not want to wait to acquire the lock. If you specify the NOWAIT option, then you only acquire the table lock if it is immediately available.

Page 17: Locks with updt nowait

Locking with Nowait

If NOWAIT is omitted, then the transaction does not proceed until the requested table lock is acquired. If the wait for a table lock is excessive, then you might want to cancel the lock operation and retry at a later time; you can code this logic into your applications.

Page 18: Locks with updt nowait

Using Pragma…

• You can use the pragma EXCEPTION_INIT to associate exception names with other Oracle error codes that you can anticipate. To handle unexpected Oracle errors, you can use the OTHERS handler. Within this handler, you can call the functions SQLCODE and SQLERRM to return the Oracle error code and message text. Once you know the error code, you can use it with pragma EXCEPTION_INIT and write a handler specifically for that error.