Oracle Manages Data Concurrency and Consistency

The risk of handling the queries from different users in multi user environment on a single table with in a unique data base needs to be designed and organized.

In this diagram, we can find a sample table in a data base. There are many queries sent by multi users at same instant of time.
There are three kinds of queries here
1) Data Readers (select queries).
2) Data writer or updater (Insert, Delete, Update).
3) Data reader and writer (select with insert).

Here comes concurrency into picture, as we can see several requests on same table at same instant of time.
Data reader’s concurrency
When user hits the data base by opening a connection (beginning a transaction) and run
Insert/Update/Delete statements, they seem to do data manipulation.
But be sure that those records which are considered for these statements in this transaction are locked.
So when this transaction is not committed or still in process, and if another user who is data reader (select query) reads the entire table data (select * from..) shall get data from these records whose data is old or the manipulated data will not reflect.
So if this transaction is committed, then the manipulated data is saved and readers (select queries) who read data after the transaction is committed can find the updated data.

If the transaction is not committed, reading the un committed data is ridiculous because user may roll back the transaction.
So if readers read uncommitted data, it is called as dirty data.

Data writer’s concurrency.
Let us consider update query is executed by a user in a specific transaction, which is not yet committed.
If another user in another transaction tries to update the same record, will be blocked.
This is because that specific record which is already manipulated and is locked in another transaction.
So unless that transaction is either rollbacked or committed, it cannot be manipulated in this transaction.
To check it.


Open sql plus say this as window 1 for reference
1) Log in into your data base.
2) Create table “test” as
Create table test
(
id number,
name varchar2(10)
);
3) Insert a row into this table saying
Insert into test values(1,’Srinivas’);
4) Commit this transaction (commit) in window 1.

5) Open another sql plus window say as window 2 for reference and log into same data base.

6) Run an update query saying
Update test set name=’Srini’ where id=1;

7) Don’t commit this transaction in window 2. (So this record is locked in this transaction).
8) Now go back to the previous window (window 1). Run an update query as
Update test set name=’VSSS’ where id=1;

You can observe this window 1 is hanged or some thing like blocked.
It is because the record which we are trying to update is locked in another transaction (window 2).
So, now go back to window 2 and say either commit or roll back. Automatically the query which is blocked in window 1 transaction will be executed, since the record which it is trying to manipulate is unlocked.

This is called serializable transaction.