Database

Unit Objective:

The objective of the unit to cover various concurrency and locking methods of database using practical implementation. 

6.1 Concurrency Control

In a single-user database, the user can modify data in the database without concern for other users modifying the same data at the same time. However, in a multiuser database, the statements within multiple simultaneous transactions can update the same data. Transactions executing at the same time need to produce meaningful and consistent results. Therefore, control of data concurrency and data consistency is vital in a multiuser database.

• Data concurrency means that many users can access data at the same time.
• Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.
• Data integrity means the data and structures must reflect all changes made to them in the correct sequence.

6.2 Locking

Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource. Using locks it will provide,

•              Prevent multiple sessions from changing the same data at the same time

•              Are automatically obtained at the lowest possible level for a given statement

•              Do not escalate, lock escalation which occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). If a user locks many rows in a table, then some databases automatically escalate the row locks to a single table. The number of locks decreases, but the restrictiveness of what is locked increases.

Resources include two general types of objects:

·                     User objects, such as tables and rows (structures and data)

·                     System objects not visible to users, such as shared data structures in the memory and data dictionary rows.

6.3 Lock Modes

The Lock table statement syntax is 

LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE 

In general there is two modes of locking in a  multi user database:

Exclusive Lock Mode

The locked data can only be displayed or edited by a single user i.e the owner of the object. A request for another exclusive lock or for a shared lock is rejected, if it is roll back then it will accept.

Share Lock Mode

When a statement reads data without making any modifications, its transaction obtains a shared lock on the data. But a transaction that tries to update the data will be prevented from doing so until the shared lock is released. More than one user can access the locked data at the same time in display mode. A request for another shared lock is accepted, even if it comes from another user. An exclusive lock is rejected.

Locking Mode

Using SYS user created table lockacc and insertd the below given values:

create and insert lockacc table

grant privilege to scott and hr

Exclusive mode- practical

dml operation using exclusive mode for owner of the object

If the table is locked exclusive mode, the owner of the object can do DML operation. Here SYS user is locked the table exclusive mode and he is allow to update the table.

fig 6-12

In Figure 6-12,  the SYS user checks how many session are currently available using V$SESSION and then locking the table using exclusive mode. In the second screen scott user can access the locked table, but he is unable to do DML operation in the exclusive lock mode.

fig 6-18

In Figure 6-18, the user SYS lock the table in exclusive mode and the same table another session the user HR is trying to lock in share mode. This is difference between share and exclusive, exclusive mode not allow any other lock mode if it is already applied, but in share mode it is possible.

Share Mode - Practical

Fig 6-16

Fig 6.16 the first session is by HR user and second screen session is SYS user, both can lock the table using share mode.

VIEW LOCK TABLE

Display list of locked object

UNLOCK THE TABLE

Unlocking table follow the steps given below

step1 : Using SYS user, find out object_id for lockacc table using dba_objects data dictionary view. After that find out SID (session id) of the object using V$lock, how many session lock occurred.

STEP 1 - UNLOCK TABLE

STEP 2: Using ALTER SYSTEM statement kill the session, then lock will be released.  Killing session you can't kill current session of SYS USER, but you can kill another user scott (the same object is locked by scott user). i.e error message 

STEP 2 UNLOCK TABLE

step 3: if you kill another user scott session in SYS user session it will display the message.

step 3 - unlock table

UNLOCK TABLE TO ACTIVE

Once you kill the session, connect the session and again grant privilege to scott user.   Then only user can access and execute the table.

unlock to active

6.4  Oracle Automatic Locking Categories

Oracle Database locks are divided into the following categories.

Automatic Oracle Locking categories

Automatic Lock category modes

6.5 DML Lock

DML lock obtained for Insert, Update and Delete operation of the table. It cannot be used for Drop table and create index statement. For example, if three users are modifying data in same table, then three entries would be required. If three users are modifying data in two different tables, then six entries would be required.  Exclusive mode of locking table cannot be used in DML lock.

• Readers never wait for writers
• Writers never wait for readers unless the reader uses the select…for update statement
• Writers wait for other writers only if they are attempting to update the same row

The screen was scott user and bottom is HR user session, both are accessing the same data of SYS user lockacc table. 

6-7 Reader never wait for writer

Enqueue Mechanism

The Oracle locking methodology is based on the concept of an enqueue. To enqueue a lock request means to place that request on the queue for the lock. The enqueue mechanism keeps track of sessions waiting for locks, the lock mode requested, and the order in which locks were requested.

When several sessions need to update the same row at the same time, the first session to update the row will acquire the exclusive row lock, and each session will acquire a shared table lock (to prevent DDL operations on the table). The enqueue mechanism keeps track of which transaction holds the row lock, and an ordered list of which ones wait for it. This ordered list is processed in FIFO order, meaning that the first to request the lock, will be the first to be serviced once that lock becomes available.

The value of the ENQUEUE_RESOURCES initialization parameter sets the number of resources that can be concurrently locked within the database.

6.6 DDL Lock

A data dictionary (DDL) lock prevent objects from being altered or dropped before procedure compilation is complete.

An exclusive DDL lock prevents other session from obtaining a DDL or DML lock.

For example, a DROP TABLE operation is not allowed to drop a table while an ALTER TABLE operation is adding a column to it, and vice versa. However, a query against the table is not blocked.

A share DDL lock for a resource prevents destructive interference with conflicting DDL operations, but allows data concurrency for similar DDL operations.

select * from dba_ddl_locks;

6.7 System Lock

prevent multiple processes from running the same application code in the database more than once at the same time. long wait latches are held for long periods of time.  Short wait latches are such that processes dependent on them do not have a long period to wait before acquiring the latch

6.8 Dead Lock

Unit Summary

Unit 6- Exercise

Worker Table

create table worker(worker_id number(5) primary key, wname varchar2(15), status varchar2(25),salary number(6), exp number(4));

insert into worker values(11,'MJED','Security_officer',4500,25);

insert into worker values(12,'AHLAM','Asst_security',3500,18);

insert into worker values(13,'Laila','Asst_security',3500,16);

insert into worker values(14,'Hamood','Junior_security',2000,8);

1. Lock the worker table by hr and sys user. 

2. Display the locked information. 

3. Unlock the worker table. 

4) Users Hiba and Nawaf are locking the worker table with appropriate lock mode. 

5) Nawaf want to display the record where worker_id is 11 and at the same time another session Hiba also deleting the record of worker_id is 14.

6) Login as Sys user and display locked session id information.