Name : Uday J Shah , HOD (IT)
Contact No : 7600044051
Email : rupareleducation@gmail.com
Oracle Chapter : 6
What is locking?
Locking
is a mechanism to ensure data integrity while allowing maximum concurrent
access to data. It is used to implement concurrency control when multiple users
access table to manipulate its data at the same time.
A lock is used to access a database
concurrently for multiple users. This prevents data from being corrupted or
invalidated when multiple users try to write to the database. Any single user
can only modify those database records (that is, items in the database) to
which they have applied a lock that gives them exclusive access to the record
until the lock is released.
Locking Issue ::
There are many issue that while locking in Database. Locking is strategy that prevent data lose
and Data Corrupt. Locking issue describe as Under.
Lost Update
A lost update occurs when two different transactions are trying
to update the same column on the same row within a database at the same time.
Typically, one transaction updates a particular column in a particular row, while
another that began very shortly afterward did not see this update before
updating the same value itself. The result of the first transaction is then
"lost," as it is simply overwritten by the second transaction.
Pessimistic locking
Which was the default locking in Oracle. This means that the row is locked
in advance once one of its attribute is changed through a call to
setAttribute() method. If anyone else attempts to acquire a lock of the
same row during this process, then it will be forced to wait until the first
transaction has completed. This is achieved by using the familiar SELECT…FOR
UPDATE syntax. This is the safest locking mode because two transactions will
never make inconsistent change to the same row.
Optimistic Locking
Optimistic locking assumes that multiple transactions can complete without
affecting each other. Oracle recommends using optimistic locking for web
applications. instead of locking a row as soon as it is changed, under
optimistic locking, it waits until changed row is posted before attempting to
acquire a lock. An exception is not thrown until the conflicting transactions
attempt to post their changes to the database.
Blocks
Simply
put, a thread of control is blocked when it attempts to obtain a lock, but that
attempt is denied because some other thread of control holds a conflicting
lock. Once blocked, the thread of control is temporarily unable to make any
forward progress until the requested lock is obtained or the operation
requesting the lock is abandoned. Its Called Blocks
Deadlocks
A
deadlock occurs when two or more threads of control are blocked, each waiting
on a resource held by the other thread. When this happens, there is no
possibility of the threads ever making forward progress unless some outside
agent takes action to break the deadlock.
Lock
Escalation
Lock
escalation occurs when numerous locks are held at one level of
granularity, such as rows, and a database raises the locks to a higher level of
granularity, like table. For example, if a single user locks many rows in a
table, some databases automatically escalate the user's row locks to a single
table. The number of locks is reduced, but the restrictiveness of what is being
locked is increased.
DML Locks
A
DML lock, also called a data lock, guarantees
the integrity of data accessed concurrently by multiple users. For example, a
DML lock prevents two customers from buying the last copy of a book available
from an online bookseller. DML locks prevent destructive interference of
simultaneous conflicting DML or DDL operations.
DML
statements automatically acquire the following types of locks:
· Row Locks
· Table Locks
DDL Locks
The
two basic types of locks are DML (table) locks, and DDL (dictionary)
locks. A DDL lock protects the definition of a schema object while the
object is referenced in a DDL operation. Oracle automatically acquires a DDL
lock to prevent other DDL operations from referencing or altering the same
object. If a DDL lock is requested on an object that already has a DDL lock on
it, the lock request will wait. DDL lock contention is rare because they are
usually held only briefly.
There
are three types of DDL locks: exclusive, shared, and breakable parse locks.
What are
Latches?
Latches
are a close cousin to lock mechanisms in Oracle that provide concurrency
functions and protect data integrity for Oracle data structures.
Latches
are a type of locking device that are quickly acquired and released. They
prevent multiple processes from running the same application code in the
database more than once at the same time, thus making available a serialization
mechanism to provide data integrity for transaction management with Oracle
database environments.
Latches
are the lightweight serialization mechanisms that are held and quickly released
for shorter durations of time as opposed to locks which are held for longer
durations of time within Oracle. Just as one would lock a door with a
deadbolt heavy-duty lock to prevent entry into one's home, use a lock to
protect long running transactions. Conversely, when one just wants to close the
door for a short period of time, latch a transaction to keep it protected for a
briefer period of time than a lock would do.
Manual
Locking and User-Defined Locks
With
Oracle Database Lock Management services, you can define your own locks for a
specific application. For example, you might create a lock to serialize access
to a message log on the file system. Because a reserved user lock is the same
as an Oracle Database lock, it has all the Oracle Database lock functionality
including deadlock detection. User locks never conflict with Oracle Database
locks, because they are identified with the prefix UL.
The
Oracle Database Lock Management services are available through procedures in
the DBMS_LOCK package. You can include statements in PL/SQL blocks
