Definition:
In Oracle PL/SQL, a LOCK is a mechanism that prevents destructive interaction between two simultaneous transactions or sessions trying to access the same database object. A LOCK can be achieved in two ways: Implicit locking or Explicit Locking. The session remains in a waiting state until one of the sessions is either committed or rolled back.
Oracle server implicitly creates a deadlock situation if a transaction is done on the same table in different sessions. This default locking mechanism is called implicit or automatic locking.
A lock is held until the transaction is complete; this is referred to as data concurrency.
A key reason for locking is to ensure that all valid processes are always able to access the original data as it was at the time the query was initiated. This is referred to as read consistency.
With Explicit Locking, a table or partition can be locked using the LOCK TABLE statement in one of the specified modes. The available lock modes are ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE,EXCLUSIVE, NOWAIT and WAIT. Note that it is preferable to do Explicit Locking rather than relying on the implicit locking done by default by the Oracle server.
In addition to the type of locking (Implicit or Explicit), Oracle also provides two different levels of locking: Row Level Locking and Table Level Locking.
With table-level locking, the entire table is locked against any kind of update or insert actions from another process. Once a given process has locked a table, that process is the only one that can change rows in the table.
With row-level locking, any specified row or rows in a table can be locked (any unlocked rows are still available for updates or deletes). The locked rows can be updated only by the process that initiated the locking.
Example Syntax:
In Oracle PL/SQL, a LOCK is a mechanism that prevents destructive interaction between two simultaneous transactions or sessions trying to access the same database object. A LOCK can be achieved in two ways: Implicit locking or Explicit Locking. The session remains in a waiting state until one of the sessions is either committed or rolled back.
Oracle server implicitly creates a deadlock situation if a transaction is done on the same table in different sessions. This default locking mechanism is called implicit or automatic locking.
A lock is held until the transaction is complete; this is referred to as data concurrency.
A key reason for locking is to ensure that all valid processes are always able to access the original data as it was at the time the query was initiated. This is referred to as read consistency.
With Explicit Locking, a table or partition can be locked using the LOCK TABLE statement in one of the specified modes. The available lock modes are ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE,EXCLUSIVE, NOWAIT and WAIT. Note that it is preferable to do Explicit Locking rather than relying on the implicit locking done by default by the Oracle server.
In addition to the type of locking (Implicit or Explicit), Oracle also provides two different levels of locking: Row Level Locking and Table Level Locking.
With table-level locking, the entire table is locked against any kind of update or insert actions from another process. Once a given process has locked a table, that process is the only one that can change rows in the table.
With row-level locking, any specified row or rows in a table can be locked (any unlocked rows are still available for updates or deletes). The locked rows can be updated only by the process that initiated the locking.
Example Syntax:
LOCK TABLE [TABLE NAME] IN [LOCK MODE] [WAIT | NOWAIT]
Example Usage:
LOCK TABLE EMPLOYEE IN EXCLUSIVE MODE
No comments:
Post a Comment