Dbink hangs; enq: DX – contention

When I was monitoring v$session view, I’ve noticed the following event “enq: DX – contention”.

I want to share the information, which I’ve collected about this problem and also provide you by my solution.

This problem happens, when a query includes the references for remote objects via database link as well as its local function, that doesn’t exist at remote site.

Look at the following query:

select *
from  employees@db_link
where local_package.local_function(var1)=1

In this example, data is queried from employees table via “db_link” database link and there is also used the function “local_function”. That is causing “enq: DX – contention” event.

This case can make blocking lock on remote database.

As many administrators are saying this is an Oracle bug.

My solution is very simple and might be very helpful for many administrators:

I recommend you to make the following changes into your application, with the help of developers.

Try to migrate all remote objects that are used in this SQL into local database , if it is time and resource consuming then try the inverse-> migrate Local functions that are used in this SQL from local database to remote database.

So my solutions for the example shown above would be the followings:

1. Migrate local_package.local_function to remote database

or

2.Migrate employees table to local database.

I prefer the first, if the local function do not use other local functions.

I hope, it was helpful for many DBAs.