Distributed Transaction Waiting for Lock err after upgrd to MDAC 2.7

[JamesCasey]

Any help would be greatly appreciated with the following situation:

I have a 3-tier app running on NT 4.0 sp6a with Oracle 8.1.7.1.5 client. Our Oracle database is 8.1.7.2.0 and is running on a Unix server. I am using Visual Basic 6.0 runtime modules sp5. We were using MDAC 2.5sp2 but upgraded to 2.7 (Refresh) at Microsoft's direction to solve a problem. After upgrading our development environment to 2.7, we immediately began to see the following error:

Error 2049 : Error occurred on line: 2807, ORA-02049: timeout: distributed transaction waiting for lock

The SQL statements that get the above error vary and are simple update statements.

In three years of working with this application, I have never seen the above error before. It now occurs intermittently. Our test environment is not under load, and in the instances where this problem has occurred, a locking conflict would seem to be impossible. I can force this error by locking a row on our database with a manually run query. If I then perform an operation with our application that attempts to update this row, I will get this error. Other than by the above very contrived means, I cannot replicate this error on demand.

The last time the error occurred, my DBA checked Oracle for any locks, alerts, or trace files. Nothing was found. I am now running a client side trace to see if I can see anything the next time the problem occurs.

Has anyone had a similar problem after upgrading to MDAC 2.7? For what it is worth, the original problem that we were trying to solve was an intermittent 'No transaction active' error. Microsoft says that people who have experienced the above problem have resolved it by upgrading to MDAC 2.7. Of course, if going to MDAC 2.7 is just going to cause another problem then...

Any thoughts?

 

[oracle101]

hi,
i also have an issue with 8.0.5 database after upgrading to mdac 2.7 in a 3 tier environment using IIS, VB, Oracle-DB.
Only experiencing the problem since upgrading to MDAC 2.7.1 but this is only re-producable under heavy load with 8.0.5.

have you managed to resolve this??
would really appreciate your feedback on this..

Thanks..

 

[JamesCasey]

Are you getting the 'distributed transaction waiting for lock error', the 'no transaction active error', or some other error? Also, what is the OS of you middle-tier?

We've been working with MS on the 'distributed transaction waiting for lock error.' Apparently, this error was fairly common for the version of the following files associated with MSDTC that were originally installed with NT Option Pack 4.0: MSDTC.dll, MSDTCPRX.dll, and MSDTCTM.dll. This problem was 'solved' by later service packs. However, I am unconvinced. My server was already at NT4.0 SP6a, so I should have had the correct version of the dlls. I am trying to confirm this point right now by looking at files in a backup of this server. (I was out of the office for a month, and a decision was made to rebuild the server in my absence. I have started picking up the pieces.) Once we can confirm which dlls were present on the server pre-rebuild, I will have a better idea if MS's suggestion is correct.

 

[oracle101]

2 machines in the environment both part of MS Cluster, & Oracle Fail safe.
Machine 1, NT Spk6a with Oracle 8.0.5
Machine 2, Nt spk61 with MDAC 2.7, MTS 2.0, 8.0.5 Client, IIS WebServer..
Using Oracle XA plug-in library.

Yes getting the exact same error ORA-02049: timeout: distributed transaction waiting for lock.
This looks to point towards a MTS issue but can not confirm this for 100%..

 

[JamesCasey]

After much pain, we found a way to make this problem go away. Per my 4/22 post, the problem went away in our development environment when we rebuilt the server. We thought that reapplying SP6a during the rebuild was what did the trick. So we went to production with the following process:
1) Install MDAC 2.7 sp1
2) Re-apply NT sp6a.

We went live and the moment a bit of load was on the server, we began to get the Distributed Transaction Timed Out waiting for lock error. In desperation, I did the following:

1) Uninstalled NT Option pack 4.0 completely.
2) Reinstalled NT Option pack 4.0.
3) Applied NT sp6a
4) Installed MDAC 2.7sp1
5) Applied NT sp6a
6) Reinstalled my application

We resumed processing, and all was well. You might want to try the above process to see if it works. However, we were only running MTS, not IIS, so the option pack 4.0 steps were simple for us. With IIS, it might not be so easy.

If you can, I would also suggest upgrading to Oracle client 8.1.7.1.5. Any Oracle client earlier than this one has numerous bugs related to Microsoft Transaction Server.

 

[JamesCasey]

One final point: The problem I was trying to solve originally was the "No Transaction Active" error that I was receiving. The cause of this error is quite simple (although Microsoft was never able to tell me this). In MTS, one can set the Transactions will timeout in xx seconds parameter at the computer level. The "No Transaction Active" error occurs when a transaction has timed out. What is going on here is that MTS kills the transaction after XX seconds, but the code is still running. At some point, the code tries to interact with the transaction again, but the transaction no longer exists. Hence, the error. The real problem is, of course, a performance issue, but the error is not very intuitive.

 

[DaveVBProgrammer]

Hi,
You mentioned that:

In MTS, one can set the Transactions will timeout in xx seconds parameter at the computer level.

Where abouts do you set this on MTS?

I currently have many cmd.commandtimeout statements in MTS objects with different values and I was wandering if I could somehow set a global setting. Can you please advise.

 

[JamesCasey]

Open the Management Console for MTS (mmc.exe) and expand the folder structure on the left-hand side until you see the My Computer Icon. Right-click on the My Computer Icon, choose Properties. Choose the Options Tab. You should see the Transaction Timeout parameter there. I believe that the default is 60 seconds. Hope this helps.

 

[DaveVBProgrammer]

You're right it is set at 60 seconds.

I have another question, does this override the setting in the MTS object or not?

There is a connection object in the sub of the MTS object that uses the default setting of 30 for the cn.commandtimeout property. I was going to make the modification by increasing this figure in code or completely creating a cmd object and setting the cmd.commandtimeout to a figure say 300 or 450. What are your thoughts about this, have you encountered this before and does this sounds a possible approach. This would then mean that wherever I have performance issues then I will need to increase this figure and that is why I'm asking whether the setting on MTS server overrides it or not.

Note this is only happening intermittently and under heavy load.

I would appreciate your feedback and any recommendation.

 

[JamesCasey]

I think we are talking about two different things here. The commandtimeout parameter you are talking about is, I believe, the ADO command timeout parameter. The parameter I am referring to is the MTS timeout parameter. The MTS parameter controls how long a transaction will be allowed to run before it is killed. A transaction can consist of many ADO commands, among other things. The ADO command parameter only controls how long that individual command will be allowed to run before it times out. So, when you talk about the two, it would seem to me that the shorter one is always going to override the longer one. For example, you may have an MTS transaction that is made up of 10 different ADO commands. If the MTS default is set to 60 seconds and the ADO command is set to 600 seconds, and each ADO command takes 30 seconds to process, MTS is going to kill the transaction just when the code is about to execute the third ADO command. Similary, if the command parameter is set to 10 seconds, ADO is going to kill the first command before it finishes and well before MTS would try to kill the entire transaction.

I wouldn't bother with setting individual command timeout parameters as a rule. I would let MTS manage the maximum duration of a given transaction via its parameter. One warning, though, transactions consume resources. If you set the MTS parameter to 300 seconds, that's a long time for those resources to be consumed. You are probably better off setting the parameter to 60 seconds or less and then tuning the transactions which are taking so long.