LOCK problems on RAC
Cristian Cudizio
With the Real Application Clusters option
On Linux Suse EL 9
it is 2 node cluster.
This morning in alert.log there is
GES: Potential blocker (pid=28970) on resource TX-0008002C-000126F4;
enqueue info in file /opt/oracle/admin/GEOP/bdump/
geop1_lmd0_16600.trc and DIAG trace file
and
iv'e seen on gv$lock a lot of locks of TYPE='TX' and LMODE=3 on same
table used as semaphore
to serialize some operations (is a table with one record). All locks
where held by session
making an update on the one record of semaphore table (according to gv
$session).
According to what i know it would be
one session with one V$lock record type 'TX' and lmode=6 and the
others with lmode=0 and REQUEST=6.
So, what does it mean that? why a lot of locks with lmode=3? I've
tested from sql*plus to do
an update on locked table and my session was waing and in v$lock it
was with lmode=3.
Excuse me for be confused but i hope someone can help me.
Bye
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com
Mladen Gogala
> one session with one V$lock record type 'TX' and lmode=6 and the others
> with lmode=0 and REQUEST=6.
> So, what does it mean that? why a lot of locks with lmode=3? I've tested
> from sql*plus to do
> an update on locked table and my session was waing and in v$lock it was
> with lmode=3.
Lock of the type TX is an exclusive transaction lock. It locks rows.
Lock of the type TM is a table lock. It is a shared, row exclusive lock
of type 3 (Shared-ROW-Exclusive. LMODE=0 means that the transaction isn't
holding any locks. REQUEST=6 means that the transaction is requesting an
exclusive lock. Concepts manual and reference manual (for V$LOCK) should
have more information.
Cristian Cudizio
wrote:
On May 8, 3:01 pm, Mladen Gogala <mgogala.SPAM_ME....@verizon.net>
wrote:
Ok,
on Reference Manual, under V$LOCK descriptions says:
<quote>
LMODE NUMBER Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
</quote>
and on Concept manual, on chapter 13
<quote>
*Row Exclusive Table Locks (RX)*
A row exclusive table lock !(also called a subexclusive table lock,
SX)!
generally indicates that the transaction holding the lock has made
one or more
updates to rows in the table. A row exclusive table lock is acquired
automatically
for a table modified by the following types of statements:
INSERT INTO table ... ;
UPDATE table ... ;
DELETE FROM table ... ;
LOCK TABLE table IN ROW EXCLUSIVE MODE;
A row exclusive table lock is slightly more restrictive than a row
share table lock.
Permitted Operations: A row exclusive table lock held by a transaction
allows other transactions to
query, insert, update, delete, or lock rows concurrently in the same
table. Therefore, row exclusive
table locks allow multiple transactions to obtain simultaneous row
exclusive and row share table locks
for the same table.
Prohibited Operations: A row exclusive table lock held by a
transaction prevents other transactions
from manually locking the table for exclusive reading or writing.
Therefore, other transactions
cannot concurrently lock the table using the following
statements:
</quote>
So, in my case it seems as more than one session has subexclusive
table lock, SX (lmode 3) on
the same record and one locking the others (a particular case of
deadlock).
Any ideas?
thanks
Mladen Gogala
> So, in my case it seems as more than one session has subexclusive table
> lock, SX (lmode 3) on
> the same record and one locking the others (a particular case of
> deadlock).
>
> Any ideas?
Different sessions can have RX lock on the same table. It simply makes
them "interested". Those sessions still have to wait for an exclusive
row lock (type=TX, mode=6).