Shared versus Exclusive Oracle locks

In any Oracle database, you will see two types of locks: shared and exclusive.
The most common types of locks are

1. Shared locks that are issued with SQL SELECT statements
2. Exclusive locks that are issued with DELETE and UPDATE statements

Exclusive locks are issued for the duration of all SQL UPDATE or DELETE statements to ensure that all changes are single-threaded through the database. As you may know, Oracle must obtain exclusive control of the segment header block (the first block in a table) whenever a row is inserted or deleted. To understand the difference between shared and exclusive locks, assume that a task wants to lock a row for update. The task will need to wait until all shared locks are released for this row before it can issue the exclusive lock. In short, the exclusive lock will wait if any other tasks hold a shared lock against the target row.

The usual size of a lock is managed internally by Oracle, and the lock will be held by the database until a COMMIT, END, or ABORT message releases the lock. Oracle's locking schemes use a coexistence method. For example, many clients may have shared locks against the same resource, but shared locks cannot coexist with exclusive locks.

Oracle locking is performed automatically and requires no user action. Implicit locking occurs for SQL statements as necessary, depending on the action requested. Oracle’s sophisticated lock manager automatically locks table data at the row level. By locking table data at the row level, contention for the same data is minimized. Oracle's lock manager maintains several different types of row locks, depending on what type of operation established the lock. In general, there are two types of locks: exclusive locks and share locks. Only one exclusive lock can be obtained on a resource (such as a row or a table); however, many share locks can be obtained on a single resource. Both exclusive and share locks always allow queries on the locked resource, but prohibit other activity on the resource (such as updates and deletes).

Under some circumstances, a user may want to override default locking. Oracle allows manual override of automatic locking features at both the row level (by first querying for the rows that will be updated in a subsequent statement) and the table level.