After lost connection to Db, row lock still preset in data table

996039

Scenario:
I am using Odp 11.2 on 11g database and I have simple .Net application which using OracleTransaction doing a simple update on one row in data table in database. After statement ExecuteNonQuery with update command and before application succeed to commits transaction (Transaction.Commit()), application server lost connection to database and update fails as expected.

The problem:
In database in table in specific row which was simple application tried to update is till present lock. In this example lock was present for about 35 minutes. Also in v$transaction there was still present mentioned transaction despite application was already closed.

Is there some setting or job on database side that cleans lost transactions?
What is solution to avoid that kind of locks in scenario that application could lost connection before commits or rollback pending work.

Thenx.

Mark Williams-Oracle

Have you looked into the possibility of implmenting Dead Client Detection (DCD) on the server?

It may be beneficial in your case if this situation is reasonably common or expected as the database does not know that the client has "gone away".

996039

Yes we already tried with DCD, we set "SQLNET.EXPIRE_TIME=1", but in this case this is not helping. Maybe because the transaction lock on database is still present. If I check table v$session, in particular row there is still set TADDR value.
We can use profile idle_time, but this is not very good solution, because influence on all inactive sessions.

Does exist some parameter or database job that cleans lost transactions or TX locks?

Mark Williams-Oracle

Is there a firewall between the database server and the middle tier client?

I'm not an expert in firewalls, but I have seen reports in the past of firewalls interfering with DCD (or also being the need for it!).

Just to confirm, you did configure the DCD (sqlnet.expire_time) in the sqlnet.ora file on the server? Sometimes people configure it on the client by mistake.

I'm not aware of any job (unless a manual job or procedure has been created) that would clean things up as you mention (other than the normal PMON cleaning up failed user processes/threads).

I also agree that creating and assigning a profile with idle_time would be a bad idea as connections in the connection pool could/would appear idle but really they should not be closed as they are eligible to be given to a client request.

996039

Yes I configure "sqlnet.expire_time" on server side. I also tried if it is working on some isolation example and with network sniffing tool.
In general this is working good, but in this particular situation does not helping.