Re: Locking Problem

Good point, Mike, and one that is often overlooked in high-speed, high-density OLTP.

Shad, your PCTFREE=10 could well protect you from this problem though, as any free space available in the block can be used dynamically for extra transaction slots.

If there are deadlocks, they should be resolved very rapidly by automatic deadlock detection, so you will find that hard to see.

However if a long-running transaction takes the single ITL slot in a block, and there is no free space, any other transaction that wants to update the block will have to queue until the first transaction completes, and it does this in the normal way (i.e. just as if the ITL was a locked row in the table) with a lock of type TX visible in v$lock. This will be a request for a share lock (i.e. request = 4).

So your quick test is:

    select * from v$lock
    where type = 'TX'
    and request = 4
;

Look at the value for CTIME, which is the time for which the lock has been held. SID is the SID waiting. If you then note ID1 and ID2, you can then look for further TX locks with matching values for ID1 and ID2 but LMODE=6 - this is the holding lock for which the other is waiting.

If this is the problem, you can

    ALTER TABLE XXX initrans 2; -- or more

All new blocks will have more transaction slots reserved, and if you old data is subject to deletes and reducing updates so that space become free, then I believe existing blocks may also acquire enough space to reserve more transaction slots.

--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Shad wrote in message <7c00v4$4tn_at_romeo.logica.co.uk>...
>Mike,
>
>Thanks for this. It certainly looks possible - the table that I think is
>the problem is set with INITRANS 1 and PCTFREE 10 so there's not much extra
>space in the block to play with.
>
>My question now is, is there any way that I can quickly check that this is
>the situation that's happening? Is there a dba view which I can use to
>convince myself and the client's DBA staff that this is the problem?
>
>Presumably, the only way to remedy the situation is going to be to
re-create
>the table and then get the data back from a temporary table or an export -
>all fairly timeconsuming and not really something I want to do unless I
know
>I'll get some improvements.
>
>Thanks
>
>Mike Burden wrote in message <36DD07B0.67055814_at_capgemini.co.uk>...
>>Setting a correct value for initrans is important.
>>
>>When initrans space is not available it is the only time Oracle locks at
>block
>>level as oppose to row level. This can cause transactions which use no
keys
>in
>>common with another transaction to lock out each other.
>>
>>The default value for initrans is one so only enough space if reserved for
>a
>>single user. More space will be used but only if it is available. If it's
>not
>>you will lock the block. This can lead to deadlocks.
>>
>
>
>
Received on Tue Mar 09 1999 - 15:22:18 CST