I’m having an issue where simultaneous access to the SQL Server from different threads within my DA server are causing problems. Specifically the error I’m getting is this:
“Cannot create new connection because in manual or distributed transaction mode”
This is happening sporadically when I have two different threads within my server attempting to access the database at the same time (not the same tables though).
One thread is adding approximately 900 rows to a TDAMemDataTable and then calling ApplyUpdates. It appears that, if another thread attempts to insert a row into another table whilst this ApplyUpdates is being performed, I get the above error.
Can anyone offer any advice on how I can solve this or work around it? The server will be constantly servicing requests from many clients and obviously has to be able to marshall all the data access correctly without producing errors.
Firstly I’m not sure why I’m getting this error when two processes are trying to access different tables within the database but I accept that it could be to do with the way SQL Server handles transactions rather than anything to do with DA.
If it’s a limitation of the way the database engine works, is there some way I can make the DA server more “tolerant”, perhaps waiting until the transaction has been completed and then retrying or something?
If I use a critical section around all calls to ApplyUpdates to ensure that two of these calls can’t be made simultaneously, it solves the problem. The downside is that the second one will block until the current one has finished. In the case where I’m adding a fair number of rows, such as the 900 in this case, this causes the second ApplyUpdates call to wait for a second or two. If this is in response to a client-side function call to the server, this also blocks the client for a second or two which is hardly ideal.
Is this the only way around this problem? Am I not allowed to have two calls to ApplyUpdates in different threads at the same time?
With the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), when you try to run multiple commands in the same session within the scope of a transaction, you may receive the following error message:
Cannot create new connection because in manual or distributed transaction mode.
You can also try to use another provider like SQLNCLI*.1
You can have two calls to ApplyUpdates in different threads at the same time
I’m actually already using the 10.1 native client (SQL 2008 R2). I had seen that MS article but didn’t think it was relevant as it was talking specifically about the OLE DB Provider for SQL Server.
I’ve not tried disabling pooling but wouldn’t this harm performance?
The two errors appear to be the opposite of each other. Without MARS enabled, it seems that DA is trying to create a new connection to the server for the second update and the SQL server is rejecting it for some reason, whilst when I enable MARS, it’s instead complaining about a new transaction not being permitted within the “session” (SQL server session?) which suggests it’s re-using the same connection.
I’m at a loss here. I could try to replicate it with a test project running against the Northwind database or something.
Ok, attached is a small test project I just created.
It uses an SQL Server database called ROTest - the included ROTest.sql script will create this.
The database contains two simple tables, each containing an Id field (IDENTITY) and a string Value field.
The server is connecting to the database via ADO using the SQLNCLI10.1 driver.
All logic is server-side, the client simply triggers this via a couple of service functions.
The first button on the client instructs the server to create a thread which inserts 2000 new rows into the first table in the database. The ApplyUpdates call in this thread takes a few seconds to complete.
The second button on the client instructs the server to create a single new row in the second table - this functionality is in the main server thread.
Now, if I run the server and client I can press either button independently and it works as expected. Although the first button returns to the client immediately (i.e. doesn’t block), the thread is spawned and the records added although it takes a few seconds.
If I try it again but this time click the first button then a second or so later click the second button, i.e. I click the second button whilst the thread spawned by the first is still processing the ApplyUpdates call, then I get this error:
“Cannot create new connection because in manual or distributed transaction mode”
What I then tried was enabling MARS on the database connection by adding “MARS Connection=True;” to the connection string. When I do this, things get even stranger.
When overlapping the two calls, as described above, now and then it will work and the second button manages to add a row to the second table even though the thread from the first button is still processing its ApplyUpdates call.
Most of the time though I get various weird errors. Most often this is “Can’t refresh delta, record isn’t found” but I’ve also had errors about “object in a zombie state”.
Can you try this there and see how you get on with it? Many thanks.
Ah ok, so the LDA isn’t thread safe but the RDA is?
At present I’m using a critical section to ensure that no two threads using the LDA attempt to perform an ApplyUpdates at the same time and this appears to work ok. When you say I could use separated threads, do you mean use a separate LDA in each of my threads? Would that work?
Alternatively, I presume I could use an RDA component on the server instead, even though it’s effectively talking to “itself”, but I presume that would add unnecessary overhead?
When you say I could use separated threads, do you mean
use a separate LDA in each of my threads? Would that work?
Yes, you can use a separate LDA in each of your threads
Alternatively, I presume I could use an RDA component on the server instead, even
though it’s effectively talking to “itself”, but I presume that would add unnecessary overhead?
LDA works directly, and using RDA you add unnecessary overhead.
