The prevailing idea states that no more than one user in a database can modify the same row in the same table at the same time.

This is logically unacceptable, and the database prevents this by disallowing multiple users from working on the same row simultaneously, a mechanism known as locking.

Of course, a SELECT operation can still occur even if that specific row is locked by another user, because it will display the values from the Undo Information.

As long as that field has not been released through a commit or rollback, and the fact that there are two types of locks (Exclusive and Shared Locks),

at the moment of executing a DML operation on a specific field, two types of locks can be enabled: an Exclusive Lock at the field or row level to prevent any other user or, in other words, any other session from accessing the same field, and the other type, Shared Lock, for the table to prevent any DDL operation at the table level such as altering the table structure or deleting it, which is done automatically via Oracle.

If the Exclusive Lock is meant to prevent access to the field or table during its engagement by another session, only one session will perform an Exclusive Lock at the row or table level,

whereas the Shared Lock allows multiple sessions to share a Shared Lock on the same table to prevent any other session from performing an Exclusive Lock on the table.

In general, Oracle supports locking automatically and it can also be done manually. If you cannot perform an Exclusive Lock on a table, it is in fact in a Shared Lock state.

Simply put, we can say that when modifying some fields in a certain table, an Exclusive Lock is applied to those fields so that no other session can write to these fields

until the first session completes its operation; knowing that read operations are only available even in the case of an Exclusive Lock because reading is done from the Undo Data,

and also during the modification of specific fields, a Shared Lock is applied to the table to prevent an Exclusive Lock on the table which allows the user to perform DDL operations such as delete operations

and change the table structure.

Thus, to perform DDL operations on a certain table, an Exclusive Lock must be applied at the table level for any other session dealing with the table, and we cannot apply an Exclusive Lock at the table level

until all DML operations on the table are completed or, in other words, until the Exclusive Lock at the row level is completed, as well as the Shared Lock at the table level.

The goal of the Shared Lock is to prevent DDL operations on the table or, in other words, to prevent an Exclusive Lock at the table level.

Let’s assume now that a database user opened a session via SQL*PLUS and created a table named TEST and added a single record to the table.

CREATE TABLE TEST (NO NUMBER(5));

INSERT INTO TEST VALUES(1);

SELECT * FROM TEST; 

Then the same user opened another session and performed a query on the table TEST.

SELECT * FROM TEST; 

Notice that the addition to the table did not appear from this session because this operation has not been committed yet.

Now, if this data were committed from the first session through the Commit command, and I repeated the query from the second session, then the data would appear.

This is because at the moment of the Commit, the data is committed and the lock is released.

Notice that if a modification was made from the first session and the data was not committed, then if, through the second session, I performed a query, the query would return the old value from the Undo Data.

Now, if we made a modification to a field in the table TEST from the first session and did not commit the modification.

UPDATE TEST SET NO=2 WHERE NO=1; 

Then, through the second session, if we performed a modification to the structure of the table using the ALTER command, this would fall under DDL operations.

ALTER TABLE TEST ADD (NAME VARCHAR2(40)); 

Do you know why you couldn't do that?

The message indicates that the table is busy with another operation, and this is correct because the modification operation has not yet been committed from the first session, as the Exclusive Lock is still in place on the table field, preventing any DDL operations until the Exclusive Lock is released at the field level, and also the Shared Lock at the table level.

This is because DDL operations require an Exclusive Lock at the table level, and the Shared Lock prevents that.

Now, if you were to commit the data or roll back from the first session and then attempt the ALTER operation, it would succeed.

Of course, all the Lock operations we previously discussed happen automatically through Oracle.

However, a database administrator may manually apply a Lock on the table when needed to prevent users from modifying certain tables at times.