Database Locks

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 holding the lock 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       SQL> select * from emp where sal > 3000 for update of comm;

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
    SQL> 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
    SQL> 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
    SQL> Lock table table name in exclusive mode;

NOWAIT:

  • If one user locked the table without nowait then another user trying to lock the same table then he has to wait until the user who has initially locked the table issues a commit or rollback statement. This delay could be avoided by appending a nowait clause in the lock table command.

    Ex:
    SQL> lock table emp in exclusive mode nowait.

DEADLOCK:

      A deadlock occurs when two users have a lock each on separate object, and they want to acquire a lock on the each other’s object. When this happens, the first user has to wait for the second user to release the lock, but the second user will not release it until the lock on the first user’s object is freed. In such a case, oracle detects the deadlock automatically and solves the problem by aborting one of the two 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.

Related posts:

Leave a Reply