Strange SQL2005 problem. "SqlConnection does not support parallel transactions"

Question

Hi.

I have a problem that seems like its a result of a deadlock-situation. Whe are now searching for the root of the problem but meantime we wanted to restart the server and get the customer going.

And now everytime we start the program it just says "SqlConnection does not support parallel transactions". We have not changed anything in the program, its compiled and on the customers server, but after the "possible deadlock"-situation it want go online again.

We have 7 clients (computers) running the program, each client is talking to a webservice on a local server, and the webservice is talking to the sql-server (same machine as webserver).

We have restarted both the sql-server and the iis-server, but not rebooted the server because of other important services running on the server so its the last thing we do. We can se no locks or anything in the management tab.

So my question is, why does the "SqlConnection does not support parallel transactions" error comming from one time to another without changing anything in the program and it still lives between sql-restart.

It seems like it happens at the first db-request the program does when it start.

If you need more information just ask. Im puzzled...

More information: I dont think I have "long" running transactions. The scenario is often that I have a dataset with 20-100 rows (ContractRows) in that Ill do a .Update on the tableAdapter. I also loop throug those 20-100 rows and for some of them Ill create ad-hook-sql-querys (for example if a rented product is marked as returned I create a sql-query to mark the product as returned in the database)

So I do this very simplified:

Create objTransactionObject
Create objtableadapter (objTransactionObject)
for each row in contractDS.contractrows
  if row.isreturned then
    strSQL &= "update product set instock=1 where prodid=" & row.productid & vbcrlf
 End if
next
objtableadapter.update(contractDS)
objData.ExecuteQuery(strSQL, objTransactionObject)    
if succsesfull 
  objtransactionobject.commit
else
  objtransactionobject.rollback
end if
objTran.Dispose()

And then Im doing commit or rollback depending on if It went well or not.

Edit: None of the answers have solved the problem, but I'll thank you for the good trouble shooting pointers.

The "SqlConnection does not support parallel transactions" dissapeared suddenly and now the sql-server just "goes down" 4-5 times a day, I guess its a deadlock that does that but I have not the right knowledge to find out and are short on sql-experts who can monitor this for me at the moment. I just restart the sql-server and everything works again. 1 of 10 times I also have to restart the computer. Its really bugging me (and my customers of course).

Anyone knowing a person with good knowledge in analyzing troubles with deadlocks or other sql problems in sweden (or everywhere in the world,english speaking) are free to contact me. I know this is'nt a contact site but I take my chanse to ask the question because I have run out of options, I have spent 3 days and nights optimizing the clients to be sure we close connections and dont do too much stupid things there. Without luck.

Answer 1

Yours doesn't appear to be an unusual problem. Google found a lot of hits when I pasted your error string into the query box.

Reading past answers, it sounds like it has something to do with interleaving transactions improperly or isolation level.

How long are connections held open? Do you have long-running transactions?

Answer 2

Do you have implicit transactions turned on somewhere, so that there are some transactions where you wouldn't have expected them? Have you opened Activity Monitor to see if there are any unexpected transactions?

Answer 3

Have you tried doing a backup of your transaction log? That might clear it out as well if I remember a previous, similar experience correctly.

Answer 4

It seems to be that you are sharing connections and creating new transactions on the same open connection (this is the parallel part of the exception you are seeing).

Your example seems to support this as you have no mention of how you acquire the connection in it.

You should do a review of your code and make sure that you are only opening a connection and then disposing of it when you are done (and by all means, use the using statement to make sure that you close the connection), as it seems like you are leaving one open somewhere.