Re: How do commits release row level locks?

From: Scott <oraracdba_at_yahoo.com>
Date: Thu, 12 Feb 2004 20:55:28 -0800 (PST)
Message-ID: <20040213045528.18098.qmail@web41502.mail.yahoo.com>

Ryan, I am not sure what you mean by the SCN would not be sufficient, since it does not identify whether a transaction is complete? Yes it does. If you don’t have a SCN then the transaction is not complete.

The Reader’s Digest version is that Oracle tracks the row level lock at the row level. Every row has a minimum of 3 bytes of overhead.

1st byte is the delete byte
2nd byte is the lock byte
3rd byte is # columns
Beyond this is Length byte for each column and the columns and any chaining information if the row is greater than 255 columns.

The 2nd byte is either hex 00 (no lock) or 01 thru ff. The 01 thru ff is the transaction slot ID. The number of transaction slots is base on inittrans which maxes out at 255 or ff. The transaction slot has a ITL(interested Transaction list ie. The txn slot) TXID UBA(undo block address) FLAG LCK and SCN/FSC column.

ITL is the the transaction slot number
TXID is undosegment.slot.wrap
UBA is undo datablock address.seqno.recordno LCK tells me the number of rows locked by the transaction
FLAG is C- Commited, U- commit upper bound T – Active CSC, B - Rollback of this UBA gives before image of the ITL
SCN/FSC – if this is an SCN then the transaction is committed if this is an FSC then I have free space credit (bytes). Fsc overlaps the SCN Wrap when present.

An example of what might happen with an update

0x03 0x0003.016.000007f8 0x08000b74.0177.01 --U- 1 fsc 0x0000.4140d1fc
0x04 0x0002.014.00000903 0x080004a4.02ea.06 --U- 1 fsc 0x0000.4201ed8f

This means these rows are currently awaiting the redo for the block
cleanout to be written but the cleanout itself has been done though in an
abbreviated form. They appear to be cleaned out with Upper bound commit SCN
but the SCN appears to have a 0 wrap number. Also the lock count for the ITLs
seem to indicate that the rows are still locked.

	So when a fast cleanout is done :
		- the ITL entry's flag is set to U
		- the Lck count is left as it is
		- the fsc is left in place (in this case 0 because
no space
			has been freed)
		- the commit SCN wrap number is not reported

Examining the rows themselves also shows that the lock bytes show they
are still locked by the ITL.

The next update (ie NOT selects) to hit this block will then produce
the REDO for the block cleanout and it will cleanup the ITL entries and the
lock bytes on the rows.

Ryan <ryan.gaffuri_at_cox.net> wrote:
> Now Oracle does not track which rows are locked. How
> processes know when a lock has been released? I
> can't find it in the docs. I'm assuming there 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 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
>

Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online. http://taxes.yahoo.com/filing.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 Thu Feb 12 2004 - 22:55:28 CST