Types of locks in database ?
Answers were Sorted based on User's Feedback
Answer / lakshminarayana reddy
2 types
1. Implicit locks: oracle automatically locks the rows
whenever user performs DML operations.
2. Explicit locks: provided by user.
2 tpyes a) Row level locks: used to lock selected rows
of table.It is imposed by "for update" clause in select.
b) Table level locks: used to lock compleate
table. 3 models 1) share mode
2) share update
3) exlcusive mode
Is This Answer Correct ? | 92 Yes | 26 No |
Answer / shaheed ul haq sadi
1) Shared
A shared lock reserves its object for reading only. It
prevents the object from changing while the lock remains.
More than one program can place a shared lock on the same
object. More than one object can read the record while it is
locked in shared mode.
2) Exclusive
An exclusive lock reserves its object for the use of a
single program. This lock is used when the program intends
to change the object.
You cannot place an exclusive lock where any other kind
of lock exists. After you place an exclusive lock, you
cannot place another lock on the same object.
3) Promotable/Update
A promotable (or update) lock establishes the intent to
update. You can only place it where no other promotable or
exclusive lock exists. You can place promotable locks on
records that already have shared locks. When the program is
about to change the locked object, you can promote the
promotable lock to an exclusive lock, but only if no other
locks, including shared locks, are on the record at the time
the lock would change from promotable to exclusive. If a
shared lock was on the record when the promotable lock was
set, you must drop the shared lock before the promotable
lock can be promoted to an exclusive lock.
Is This Answer Correct ? | 47 Yes | 9 No |
Answer / mahesh reddy g
lockmodes:
EXCLUSIVE
SHARE
ROW EXCLUSIVE
SHARE ROW EXCLUSIVE
ROW SHARE* | SHARE UPDATE*If NOWAIT is omitted Oracle
will wait until the table is available.
Several tables can be locked with a single command -
separate with commas
e.g. LOCK TABLE table1,table2,table3 IN ROW EXCLUSIVE MODE;
Default Locking Behaviour
A pure SELECT will not lock any rows.
INSERT, UPDATE or DELETE's - will place a ROW EXCLUSIVE
lock.
SELECT...FROM...FOR UPDATE NOWAIT - will place a ROW
EXCLUSIVE lock.
Multiple Locks on the same rows with LOCK TABLE
Even when a row is locked you can always perform a SELECT
(because SELECT does not lock any rows) in addition to
this, each type of lock will allow additional locks to be
granted as follows.
ROW SHARE = Allow ROW EXCLUSIVE or ROW SHARE or SHARE locks
to be granted to the locked rows.
ROW EXCLUSIVE = Allow ROW EXCLUSIVE or ROW SHARE locks to
be granted to the locked rows.
SHARE ROW EXCLUSIVE = Allow ROW SHARE locks to be granted
to the locked rows.
SHARE = Allow ROW SHARE or SHARE locks to be granted to the
locked rows.
EXCLUSIVE = Allow SELECT queries only
Although it is valid to place more than one lock on a row,
UPDATES and DELETE's may still cause a wait if a
conflicting row lock is held by another transaction.
* = Oracle 6 option included for compatibility
Is This Answer Correct ? | 30 Yes | 14 No |
Answer / narasimha rao
ddl locks
dml locks
oracle internal locks
oracle distributed locks
oracle parallell cache management locks
Is This Answer Correct ? | 10 Yes | 3 No |
Answer / naveen reddy mavuluri
shared locks, exclusive locks , intent locks like intent
shared, intent exclusive,
schema locks like schema modification,stability,
bulk update locks.
Is This Answer Correct ? | 15 Yes | 9 No |
Answer / mani
5 types of locks in database
intent
shared
schema
update
bulk update
Is This Answer Correct ? | 12 Yes | 12 No |
How do I save the results of sql query in a file?
Can we edit a view in sql?
Inline the values in PL/SQL, what does it mean.?
What is query syntax?
What is difference between joins and union?
how to delete an existing column in a table? : Sql dba
What makes a good primary key?
what is the difference between a web-garden and a web-farm? : Sql dba
What does the acronym acid stand for in database management?
How do you optimize a query?
function can return value ,procedure also return value through out parameter then what is the difference?
What is the syntax and use of the coalesce function?