| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> SV: How do commits release row level locks?
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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] P=E5
vegne af Stefan Jahnke
Sendt: 16. februar 2004 10:47
Til: 'oracle-l_at_freelists.org'
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:
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_at_hotmail.com]
Gesendet: Freitag, 13. Februar 2004 05:37
An: oracle-l_at_freelists.org
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.
> 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_at_freelists.org
> 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
> -----------------------------------------------------------------
>
-- 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_at_freelists.org 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_at_freelists.org 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 -----------------------------------------------------------------Received on Mon Feb 16 2004 - 05:28:57 CST
 |
 |