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.

Read operations : select statements
Write Operations: insert,update,delete statements

The two types of locks supported by oracle are
Shared locks

Shared locks are placed on resource whenever a read operation is performed .
Multiple shared locks can be simultaneously set on a resource.

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.

Dead Locks N

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

Row level
Page level
Table level

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

If the where clause evaluates to only 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 is used.

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

The transaction is committed successfully using the commit verb
A rollback is performed
A rollback to a savepoint will release locks set after the specified savepoint.

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

Concurrency Control in Oracle

For Online Classes Contact Us: +919885348743

For
Online Classes

Contact Us: +919885348743