LOCKS IN ORACLE Users manipulate Oracle table data

LOCKS IN ORACLE • Users manipulate Oracle table data via SQL or PL/SQL sentences. • An Oracle transaction can be made up of a single SQL. sentence or several SQL sentences. • The purpose of a DML lock, also called a data lock, is to guarantee the integrity of data being accessed concurrently by multiple users. 1

DML Locks • DML locks are locks placed by Oracle to protect data in tables and indexes. • Whenever a DML statement seeks to modify data in a table, Oracle automatically places a row-level lock on the rows in the table that are being modified. 2

Oracle's Default Locking Strategy - Implicit Locking: • Since the Oracle engine has a Fully automatic locking strategy, it has to decide on two issues: – Type of Lock to be applied – Level of Lock to be applied 3

Types of Locks: • The type of lock to be placed on a resource depends on the operation being performed on that resource. • Operations on tables can be distinctly grouped into two categories: • Read Operations : SELECT statements • Write Operations: INSERT, UPDATE, DELETE statements 4

Shared locks • Shared locks are placed on resources whenever a Read operation (SELECT) is performed. • Multiple shared locks can be simultaneously set on a resource. 5

Exclusive locks • Exclusive locks are placed on resources whenever Write operations (INSERT, UPDATE and DELETE) are performed. • Only one exclusive lock can be placed on a resource at a time For example, the first user who acquires an exclusive lock will continue to have the sole ownership of the resource, and no other user can acquire an exclusive lock on that resource. 6

Levels of Locks: Oracle provides the following three levels of locking: • Row level. • Page level • Table level 7

The Oracle engine decides on the level to be used by the presence or absence of a where condition in the SQL sentence. • if the WHERE clause evaluates to only one row in the table, a row level lock is used. • If the WHERE clause evaluates to a set of data, a page level lock is used. • If there is no WHERE clause, (i. e. the query accesses the entire table, ) a table level lock is used. 8

LOCK TABLE statement The LOCK TABLE statement allows you to explicitly acquire a shared or exclusive table lock on the specified table. The table lock lasts until the end of the current transaction. To lock a table, you must either be the database owner or the table owner. Explicitly locking a table is useful to 1) Avoid the overhead of multiple row locks on a table (in other words, user-initiated lock escalation) 2) Avoid deadlocks 9

LOCK TABLE table-Name IN { SHARE | EXCLUSIVE } MODE After a table is locked in either mode, a transaction does not acquire any subsequent row-level locks on a table. 10

Examples To lock the entire Flights table in share mode to avoid a large number of row locks, LOCK TABLE Flights IN SHARE MODE; SELECT * FROM Flights WHERE orig_airport > 'OOO'; 11

• You have a transaction with multiple UPDATE statements. • Since each of the individual statements acquires only a few row-level locks, the transaction will not automatically upgrade the locks to a table-level lock. • However, collectively the UPDATE statements acquire and release a large number of locks, which might result in deadlocks. • For this type of transaction, you can acquire an exclusive table-level lock at the beginning of the transaction. 12

LOCK TABLE Flight. Availability IN EXCLUSIVE MODE; UPDATE Flight. Availability SET economy_seats_taken = (economy_seats_taken + 2) WHERE flight_id = 'AA 1265' AND flight_date = DATE('2004 -03 -31'); UPDATE Flight. Availability SET economy_seats_taken = (economy_seats_taken + 2) WHERE flight_id = 'AA 1265' AND flight_date = DATE('2004 -04 -12'); UPDATE Flight. Availability SET economy_seats_taken = (economy_seats_taken + 2) 13 WHERE flight_id = 'AA 1265' AND flight_date = DATE('2004 -04 -15');