Depending on the context, it could mean several things:

• Oracle Database locking: How row locks, table locks, transactions, deadlocks, and LOCK TABLE work. Oracle automatically locks rows during INSERT, UPDATE, DELETE, and MERGE to maintain data consistency. Locks are typically released when you COMMIT or ROLLBACK.
• Locking a table manually: Using SQL such as:
• LOCK TABLE employees IN EXCLUSIVE MODE;
• This explicitly locks a table until the current transaction is committed or rolled back.
• Account lock: Locking or unlocking an Oracle database user account, for example:
• ALTER USER scott ACCOUNT LOCK;
• ALTER USER scott ACCOUNT UNLOCK;
• Vendor lock-in: Being dependent on Oracle products, making it difficult to migrate to another database platform.

What is a Lock in Oracle?

A lock in Oracle is a mechanism used to control concurrent access to database data. It prevents multiple users from making conflicting changes to the same data at the same time, ensuring data integrity and consistency.

For example:

• User A updates an employee’s salary.
• While User A’s transaction is not yet committed, User B cannot update the same employee record.
• Once User A commits or rolls back the transaction, the lock is released.

Advantages of Locks

• Prevents data inconsistency.
• Maintains database integrity.
• Supports multi-user access safely.
• Avoids conflicting updates.

Types of Locks in Oracle

Oracle mainly uses two types of locks:

1. Shared Lock (S Lock)

• Allows multiple users to read the same data simultaneously.
• Does not allow another user to modify the locked data.
• Used mainly during queries and read operations.

Example

LOCK TABLE employee IN SHARE MODE;

Features

• Multiple users can read.
• No updates allowed until the lock is released.
• Suitable for read-only operations.

2. Exclusive Lock (X Lock)

• Allows only one user to modify the data.
• Other users cannot modify the same data until the transaction ends.
• Oracle automatically places an exclusive lock during INSERT, UPDATE, and DELETE operations.

Example

UPDATE employee
SET salary = 50000
WHERE emp_id = 101;

The row remains locked until:

COMMIT;

or

ROLLBACK;

Features

• Only one transaction can modify the data.
• Prevents other users from updating the same rows.
• Ensures data consistency.

Oracle Lock Levels

1. Row-Level Lock

• Locks only the specific row being modified.
• Other rows remain available.
• Automatically applied for INSERT, UPDATE, and DELETE.

Example:

UPDATE employee
SET salary = 45000
WHERE emp_id = 105;

Only the row with emp_id = 105 is locked.

2. Table-Level Lock

• Locks the entire table.
• Used when the whole table needs protection.

Example:

LOCK TABLE employee IN EXCLUSIVE MODE;

Automatic Locks

Oracle automatically places locks during:

• INSERT
• UPDATE
• DELETE

The lock is released after:

COMMIT;

or

ROLLBACK;

Summary Table

Interview/Exam Definition (2 Marks)

Lock: A lock in Oracle is a mechanism that controls simultaneous access to database objects, ensuring data integrity and consistency by preventing conflicting operations.

Exam Points to Remember

• A lock prevents conflicts between multiple users.
• Oracle automatically applies locks during INSERT, UPDATE, and DELETE operations.
• Locks are released using COMMIT or ROLLBACK.
• The two primary lock types are Shared Lock and Exclusive Lock.
• Oracle also supports Row-Level and Table-Level locking for finer control over concurrent access.