Re: Oracle block locking?

Frank,

Two possibilities come to mind.

1. Check your alert log for any ORA-55 errors. This would indicate that you were having DML contention. If there is then you should probably increase the value of DML_LOCKS parameter. DML locks are a library structure required by oracle to manage locks and lock conversions. You can check the current, max, and largest used values by querying v$resource_limit with a where resource_name = 'DML_LOCKS'
2. You may be running out of space for transaction entries in your blocks. Watch your v$session to see if you are having row waits with no entry in the ROW_WAIT_ROW# column. To fix this you need to rebuild your tables with a larger INITRANS and a sufficient PCTFREE. Every block has a space for transaction entries and as part of taking a row level lock oracle records an entry in this area of the block. The main cause of problems is if you have built your tables with a PCT free of 0 as the transaction entries are stored in this area and you will only have room for a single entry and thus any row locks will in effect be block locks. If your PCT is already generous then perhaps the INITRANS setting, which sets the maximum interested transactions per block for a segment, needs to be higher for that table. NOTE: each transaction entry takes 23 bytes.

Fraser McCallum
MVP Oracle Administration
www.brainbench.com

"Frank Bucher" <frankbucher_at_gmx.de> wrote in message news:f742146.0202210638.3a4d9d25_at_posting.google.com...
> Hi all,
> we're running 8.1.7.2 server on an AIX machine and have 8.1.5 clients
> on NT machines. The discussion that appears is, that if oracle does
> some kind of row locking. We have one table 'customer' (which is a
> parent table to several other ones). On this table a transaction holds
> a lock (row exclusive), because a client worked with a customer
> (insert/update). Could it be that oracle locks other customer as well
> for an insert or an update? It seems that other the clients couldn't
> work with other customers as well. Therefore it seems as if oracle
> tries to lock a whole block?! Does this behaviour depend on the kind
> of activity (insert or update)? How does an insert on a table affect
> locks at all, if it has no foreign keys?
>
> Any help appreciated.
>
> TIA
> Frank.
>
> PS: Could you please mail the answer as well to frankbucher_at_gmx.de
Received on Thu Feb 21 2002 - 12:04:41 CST