Locks are automatically acquired during the processing of a request and released when the request is terminated.
Levels of Locking
Locks may be applied at three levels:
Database Locks: Apply to all tables and views in the database.
Table Locks: Apply to all rows in the table or view.
Row Hash Locks: Apply to a group of one or more rows in a table.
Types of Locks
The four types of locks are described below.
Exclusive
Exclusive locks are applied to databases or tables, never to rows. They are the most restrictive type of lock. With an exclusive lock, no other user can access the database or table. Exclusive locks are used when a Data Definition Language (DDL) command is executed (i.e., CREATE TABLE). An exclusive lock on a database or table prevents other users from obtaining any lock on the locked object.
Write
Write locks enable users to modify data while maintaining data consistency. While the data has a write lock on it, other users can only obtain an access lock. During this time, all other locks are held in a queue until the write lock is released.
Read
Read locks are used to ensure consistency during read operations. Several users may hold concurrent read locks on the same data, during which time no data modification is permitted. Read locks prevent other users from obtaining the following locks on the locked data:
Exclusive locks
Write locks
Access
Access locks can be specified by users unconcerned about data consistency. The use of an access lock allows for reading data while modifications are in process. Access locks are designed for decision support on tables that are updated only by small, single-row changes. Access locks are sometimes called "stale read" locks, because you may get "stale data" that has not been updated. Access locks prevent other users from obtaining the following locks on the locked data:
Exclusive locks
