AW: How do commits release row level locks?
- From: Stefan Jahnke <Stefan.Jahnke@xxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Feb 2004 13:04:17 +0100
Hi Jesper
Thanks for the in-depth information. I checked the locks and my =
transaction
was the only one locking that object. Since this is a controlled
environment, that should result in my transaction being the only one =
making
entries to the itl of any blocks of that table. Now I start wondering =
why
that transaction hung in the first place (Undo and the like was not the
problem).
I will do antother run to find out what else the problem might have =
been.
Any other ideas are welcome.
Stefan
-----Urspr=FCngliche Nachricht-----
Von: Jesper Haure Norrevang [mailto:jhn.aida@xxxxxx]
Gesendet: Montag, 16. Februar 2004 12:29
An: oracle-l@xxxxxxxxxxxxx
Betreff: 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=3D20
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=3D20
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@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] P=3DE5
vegne af Stefan Jahnke
Sendt: 16. februar 2004 10:47
Til: 'oracle-l@xxxxxxxxxxxxx'
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
=3D3D
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
=3D3D
anyway
=3D3D3D> one entry per row? or is it one entry per transaction per =3D
block?), =3D3D
that
there aren't enough slots left in a block and the delete hangs with an
enqueue wait?
Any input appreciated,
Stefan
-----Urspr=3D3DFCngliche Nachricht-----
Von: Arup Nanda [mailto:orarup@xxxxxxxxxxx]
Gesendet: Freitag, 13. Februar 2004 05:37
An: oracle-l@xxxxxxxxxxxxx
Betreff: Re: How do commits release row level locks?
I wrote an article some time ago for DBAZine on this exact question. =
=3D3D
Pleas
check this out at http://www.dbazine.com/nanda3.html.
----- Original Message -----=3D3D20
From: "Ryan" <ryan.gaffuri@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
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
=3D3D
when a
lock has been released? I can't find it in the docs. I'm assuming there =
=3D
=3D3D
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 =
=3D3D
whether
the transaction is complete.
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
=3D3D20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
=20
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
