Re: LOCK problems on RAC

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: 8 May 2007 06:55:14 -0700
Message-ID: <1178632514.243918.161310@o5g2000hsb.googlegroups.com>

On May 8, 3:01 pm, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> On Tue, 08 May 2007 03:19:23 -0700, Cristian Cudizio wrote:
> > 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.
>
> --http://www.mladen-gogala.com

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

Bye
Cristian Cudizio

http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com Received on Tue May 08 2007 - 08:55:14 CDT