Database Locks

  • Locks are the mechanism used to prevent destructive interaction between the users accessing the same resources at the same time.
  • Locks provide a high degree of Concurrency.
  • Locks acquire at two different levels
    1. Row Level Lock
    2. Table Level Lock

Row Level Locks:

  • In the case of row level lock, a row is locked extensively so that other users cannot modify the row until the transaction is committed or roll backed.
  • Row level locks are acquired automatically by Oracle as a result of inserts, update, delete and select with for update clause statement
    For example Select * from EMP where empno = 1234 for update of ename

Table Level Lock:

  • A table level lock will protect table data thereby guaranteeing data integrity when multiple users are accessing data concurrently.
  • A table lock is held in several modes they are
    1. Share Lock
    2. Share Update Lock
    3. Exclusive Lock

Share Lock:

  • A Share lock locks the table allowing other user to only query but not insert, update or delete rows in a table
  • Multiple users can place share locks on the same table at the same time.
  • Command is
    Lock table table name in share mode

Share Update Lock:

  • Share update lock prevents other users to concurrently query, insert, update or even lock other rows in the same table. It prevents the other user from updating the same row which has been locked.
  • Command is
    Lock table table name in share update mode

Exclusive Lock:

  • It is most restrictive of table locks. When issued by one user it allows the other user to only query but not insert, update, delete rows in a table. It is almost similar to a share lock but only one user can place an exclusive lock on a table at a time, where as many users can place share lock on the same table at the same time.
  • Command is
    Lock table table name in exclusive mode [nowait]
          If a user has locked a table without a nowait clause, if the other user tries to prevent the above restrictions by trying to lock the table, then, he will be made to wait indefinitely until the former user issues a commit or rollback. This delay could be avoided by appending nowait clause.

Dead Lock:

      A deadlock occurs when two users have a lock each on a separate object and each wants to acquire a lock on the other user’s object. Oracle automatically detects deadlock and resolves the problem by aborting one of the transactions.

Create User:

      Create user user name identified by password

Three Standard Roles:

      The CONNECT Role gives users the ability to log in and perform basic functions – select, update, delete and insert operations.

      The RESOURCE Role gives users additional rights to create their own tables, sequences, triggers, procedures, data types, operators, indexes, index types and clusters.

      The DBA Role has the ability to grant all privileges to other users.
            Grant connect, resource, dba to user name

Exception:

  • An exception is raised when an error occurs. In this case normal execution stops and the control is immediately transferred to the exception handling part of our PL/SQL block.
  • Exceptions are of two types
    1. Predefined exceptions are automatically raised by the system
    2. User-defined exceptions user must raise the exception explicitly by using RAISE statement.