Java EE (Java Enterprise Edition) General Discussion

What is your full database version per the link on --> and answer #5

5) Database Version and IDE Version
Ensure you provide your database version number e.g. 11.2.0.3 so that we know what features we can use when answering.
If you're not sure what it is you can do the following:
select * from v$version;
in an SQL*Plus session and paste the results.

Always post this information when posting a new question.

user5008104 wrote:
Hi, 
I have a session that performs an update that blocks a set of sessions that make inserts on the same table. The set of sessions with the insert statement have the event tx index contention, how is it possible?
Thanks for your help 

does table have Primary Key which contains a SEQUENCE? Does INSERT trigger exist on this table?

Hi,

the oracle version is "

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production"

The table has a lot of indexes (a total of 43)

The primary key IDX_ZEUPH2A0 has no sequence but there is an ID column in the others indexes using a sequence generated with a trigger during insert.

The typical cause of "enq TX: Index contention" is index block splits. If your transaction is updating columns which appear in indexes then it will be deleting and re-inserting index entries, and either action could require a leaf block split to take place.  Under certain circumstances the split can take a "long" time and other sessions that want to insert into the splitting leaf block will report this enqueue wait.

You might be able to identify which index(es) from the ASH data, or from v$segstat / v$segment_statistics.  However if you give a better description of what the updating process is doing, and what the other processes are doing, this might give us enough information to explain where the problem is probably appearing.  (e.g. is your update changing a flag from Y to N in a large number of rows in a single update).

Regards

Jonathan Lewis

I must continue my analysis to obtain what the update is doing.

But the index block split is the consequence of the update or the insert ?

See this somewhat older thread where Jonathan already gave an excellent analysis about various possible causes and strategies to avoid that problem:what causes enq: TX - index contention?

yes, I read a lot on the subject but here the origin of event "enq : TX - index contentions" seems to be the update carried out via the other session

Regards

If the blocker is the session doing the update then the update is the thing doing the block split and the inserting sessions are waiting for the update to finish the split. In theory the split should only take a very short time, so the waits for any single split should also be very short.

Regards

Jonathan Lewis

I do not understand in what situation, an update can cause a split of an index block, when the update leads to an increase in the size of the record and there is no more space in the block ?

can a fragmented table be the origin of this behavior ?

user5008104 wrote:
I do not understand in what situation, an update can cause a split of an index block,

Read my first response:

If your transaction is updating columns which appear in indexes then it will be deleting and re-inserting index entries,

An "update" to an index entry means the current entry has to be deleted from one leaf block and a new index entry created (usually) in another leaf block.

Regards

Jonathan Lewis

thanks,

how can i identify the index (or indexes) in question from the ASH data, or from v$segstat / v$segment_statistics.  i have to filter on statistic_name "enq:Tx index contention" ?

the problem here is that the insert is trying to access the same index block as the update, is that right ?

Regards

Sorry Jonathan,

you answered me in a previous response

Regards

Thanks all for your help

Regards

user5008104, being that your post showed one session being responsible for pretty much all the blocking you should be very interested in what SQL statement the session was running, how many rows it updates, and how long the transaction runs for.  I am trying to remember if an index rebuild results in this wait or if it results in a different one, but hopefully no one was rebuilding indexes during production hours.

- -

HTH -- Mark D Powell --