Hi Stefan,
With each row one "lock byte" is stored. If the
row is beeing changed by some transaction the
lock byte points to an ITL in the same data block.
If the row is not locked, the lock byte is 0x00.
Since the lock byte can have 255 values <> 0, we
can have up to 255 concurrent transactions (or
whatever MAXTRANS is set to) modifying the same=20
block at the same time.
When a transaction wants to change a block it
acqures an ITL in the block (eg. no. 0x03). If
many rows in the same block are changed by the
same transaction each lock byte is set to point
to that ITL (0x03). It means, that only one
ITL is needed, if the delete is made by only
one transactions.
The ITL identifies the transaction uniquely
with a pointer containing the Undo Segment=20
Number, the Undo slot and a sequence number.
Very simplyfied the commit flag in the undo
segment header is changed, when a transaction
commits.
Do you have other transactions running?
Check the column BLOCK in V$LOCK to see
which transaction is blocking for your
transaction.
Regards
Jesper Haure Norrevang
-----Oprindelig meddelelse-----
Fra: oracle-l-bounce@(protected) =
[mailto:oracle-l-bounce@(protected)
vegne af Stefan Jahnke
Sendt: 16. februar 2004 10:47
Til: 'oracle-l@(protected)'
Emne: AW: How do commits release row level locks?
Hi Arup
Your paper brings me to a possible solution of a problem we encountered
while doing mass deletes (had to be delete, not truncate). Do you think =
=3D
the
following might be possible:
a) Table created with blocks of size 8k, inittrans 10, maxtrans 255.
b) Table only contains one column, number(28) (it holds key values).
c) mass delete on table.
d) delete hangs indefinitely.
Is it possible, since so many rows fit into one block, and each row my
transaction wants to delete results in an itl entry (is that the case =
=3D
anyway
=3D3D> one entry per row? or is it one entry per transaction per =
block?), =3D
that
there aren't enough slots left in a block and the delete hangs with an
enqueue wait?
Any input appreciated,
Stefan
-----Urspr=3DFCngliche Nachricht-----
Von: Arup Nanda [mailto:orarup@(protected)]
Gesendet: Freitag, 13. Februar 2004 05:37
An: oracle-l@(protected)
Betreff: Re: How do commits release row level locks?
I wrote an article some time ago for DBAZine on this exact question. =3D
Pleas
check this out at http://www.dbazine.com/nanda3.html.
----- Original Message -----=3D20
From: "Ryan" <ryan.gaffuri@(protected)>
To: <oracle-l@(protected)>
Sent: Thursday, February 12, 2004 11:13 PM
Subject: How do commits release row level locks?
> Now Oracle does not track which rows are locked. How processes know =
=3D
when a
lock has been released? I can't find it in the docs. I'm assuming there =
=3D
is
something similiar to an SCN written to the block header of each row.
However, the SCN would not be sufficient, since it does not identify =3D
whether
the transaction is complete.
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
=3D20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
