What is locking, advantages and types of locking?

Locking is a mechanism to ensure data integrity while allowing maximum concurrent access to data. It is used to implement concurrency control when multiple users access table to manipulate its data at the same time.

a. Avoids deadlock conditions
b. Avoids clashes in capturing the resources

Types of locks:
a. Read Operations: Select
b. Write Operations: Insert, Update and Delete

What is locking, advantages of locking and types of locking in oracle?  

Locking protect table when several users are accessing the same table. Locking is a concurrency control technique in oracle. It helps in data integrity while allowing maximum concurrency access to data. Oracle offers automatic locking whenever situation requires. This is called implicit locking.

Types of locking

Shared Lock
This type is placed on a record when the record is being viewed.

Exclusive lock
This is placed when Insert, Update or Delete command is performed. There can be only one exclusive lock on a record at a time.

Also read

Difference between locks, latches, enqueues and semaphores

Latches are used to protect Oracle data structures to be modified or run by more than one process. They are more restrictive than locks. Locking has a similar concept...............

What are transaction isolation levels supported by Oracle?

READ COMMITTED: If row locks are obtained by a certain transaction, then any other transaction that contains DML needs to wait until the row locks have been released by that particular transaction.............

Explain how to view existing locks on the database.

A number of data locks need to be monitored, in order to maintain a good performance level for all sessions, along with the time for which they last............

Explain how to lock and unlock a user account in Oracle.

SQL> ALTER USER user_name ACCOUNT LOCK; SQL> ALTER USER user_name ACCOUNT UNLOCK;.

<<Previous  Next>>

Write your comment - Share Knowledge and Experience