Concurrency Control in Oracle

Users manipulate oracle table data via SQL or PL/SQL sentences. An Oracle transaction can be made up of a single SQL sentence or several SQL sentences. This gives rise to single query transactions and multiple query transactions i.e SQT and MQT.
            The technique employed by the oracle engine to protect table data when several people are accessing it is called concurrency control.
Oracle uses a method called locking to implement concurrency control when multiple users access a table to manipulate its data at the same time.
LOCKS
            Lock are mechanisms used to ensure data integrity while allowing maximum concurrent access to data. Oracle’s locking is fully automatic and requires no user intervention. The oracle engine automatically locks table data while executing sql statements. This type of locking is called implicit locking.
Types of LOCKS
            The type of lock to be placed on a resource depends on the operation being performed on that resource. Operations on tables can be distinctly grouped into two categories.

The two types of locks supported by oracle are
Shared locks

Exclusive Locks
Exclusive locks are placed on resources whenever write operations  are performed.
Only one exclusive lock can be placed on a resource 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.

            the dead lock occurs when two users have a lock , each on a separated object, and m each wants to acquire a lock on the other users object. When this happens the first user has to wait for the second user to release the lock, but the second user will not release until the lock on the first users object is freed. At this point both the users are at an impasse and cannot proceed with their business, in such a case, oracle detect the deadlock automatically and solves the problem by aborting one of the two transactions.
Levels Of locks
A table can be decomposed into rows and a row can be further decomposed into fields. Hence, if an automatic locking system is designed so as to be able to lock the fields of a record, it will be the most flexible locking system available.
Oracle provides the following three levels of locking

The oracle engine decides on the level to be used by the presence or absence of a where condition in the sql sentence.

Explicit Locking
The technique of lock taken on a table or its resources by a user is called explicit locking.
Who can explicitly lock?
Users can lock tables they own or any tables on which they have been granted table’s privileges such as select,insert,update,delete.
The select for update statement
It is used for acquiring exclusive row level locks in anticipation of performing updates on records . this clause is generally used to signal the oracle engine that data currently being used needs to be updated . it is often followed by one or more update statements with a where clause.
Ex:-      Client  A                                                        Client B


select * from vision where eno=200 for update;
commit;

select * from vision where eno=200 for update;

Client A                                                          Client B


select * from vision where eno=200 for update;

select * from vision where eno=200 for update nowait;

LOCK TABLE STATEMENT
 To manually override oracle’s default locking strategy by creating a data lock in a specific mode.
Syn:-    LOCK TABLE tablename [, tablename]….. IN
            { row share/row exclusive/share update/share/share row exclusive/exclusive}
            [nowait]

 

 

Client A                                                          Client B


1)lock table vision in exclusive mode nowait;
2)insert into vision
values(121,’rajesh’,3200);
3)select * from vision;

1) select * from vision;
2) insert into vision
values(123,’pcsv’,2300);

Ex:-   Client A                                                Client B


update vision set sal=sal+1000 where eno=200;
select * from vision;
delete from vision where eno=123;
select * from vision;

select * from vision;

Note:- After commit only client b will get the updated data or deleted data
Releasing Locks

 

Additional Concepts
HO   Executes a host operating system command without leaving SQL*Plus. 

 
Syn:- HO[ST] [command]
Ex:- 1) HO     à Shows ms-dos prompt in windows os shows $ in unix/linux
        2) ho calc.exe

 

For
More Explanation
&
Online Classes

Contact Us:
+919885348743