TransactionTimeout with Oracle 11g

Question

Using an Oracle 11g database. I have a service that is tagged with:

[ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall, TransactionTimeout="00:00:10")]

The method itself looks like this:

    [OperationBehavior(TransactionScopeRequired = true)]
    [TransactionFlow(TransactionFlowOption.NotAllowed)]
    public OrderMessage AddOrder(OrderMessage orderMessage)
    {
        ValidateMessage(orderMessage);
        return this.orderBusiness.Add(orderMessage);
    }

If I put a breakpoint in the orderBusiness.Add method and wait 10 seconds, the transaction times out. If I take out the breakpoint, but then lock a table involved in the transaction, it will wait much longer than 10 seconds, at least until the service times out at 60 seconds. Any ideas how to get a transaction to timeout at a specified interval if the table is locked?

Answer 1

You could use an insert with NOWAIT to immediately return control to you if it is locked and handle it in your code.

Answer 2

Here's what I had to do in this case. I found my transactiontimeout had no effect because I was waiting for a lock for my transaction. I didn't know this though because distributed_lock_timeout was set to the default 60 seconds in my DB and the service was timing out before that. Since I couldn't change the distributed_lock_timeout I increased the service timeout and then I started seeing:

InnerException: Oracle.DataAccess.Client.OracleException
Message="ORA-02049: timeout: distributed transaction waiting for lock"

So in summary, the transactiontimeout worked when I had a breakpoint because it cares about how long the transaction actually takes to execute. It had no effect when the table was locked because it hadn't started executing. I should use a try catch to make sure the resource is not locked before starting my transactional work.