Answered by:

PHP & SQLSRV : New transaction is not allowed because there are other threads running in the session

Question

I'm working on a web application written with PHP and uses SQL Server 2008 (I'm
using SQLSRV driver ver.2.). In a part of this application, I have to use SQL Transactions. As Microsoft suggested, I did it exactly based on
Microsoft Tutorial. The main processes in my codes follow these steps:1- starting sql transaction2- send information to PHP files through jQuery and check the result sent by JSON3- rollback if the result was false and go to the next query if it was true.4- commit transactions if no error occurred and all results were ok.

Answers

The problem you have encountered is most common with ASP developers, because ASP completely blurs the distinction between client and server-side execution of code. For example, it is possible to put code in your ASP 'application' which responds to
the click of a button in a browser, as if it is an interactive event - it isn't, the button click happens half way around the world. To achieve the effect and manage the process ASP obfuscates the simplicity of HTML and Javascript.

I'm no fan of jQuery either, because it makes Javascript look like something else, and is quite difficult to read.

To solve your problem, let's start with what PHP (or ASP or any other HTTP processor) does - it generates a web page and sends it to a browser. It creates some text, that's all.

In your example, your Javascript function is a piece of text being created by PHP before it gets sent to the browser...

While your file is processed in PHP (remember PHP is just creating text to send to the browser), these PHP functions are executed. When the script ends, all resources are cleaned up by PHP, all SQL Server connections are closed:

The browser receives all of the text from your sample without the PHP code. You cannot use the outcome of a Javascript 'if' statement to alter the PHP flow of control - in PHP it's just a piece of text, and Javascript never sees that PHP block.
For example:

When the method 'apply()' is executed in the web browser, several requests are sent to PHP. Each one is executed, result sent back to the browser and closed. In summary, you are:

Sending a POST request to PHP page "./process/editDoc.php".

When PHP replies to 1., you send another POST request to "./process/removeRows.php".

When PHP replies to 2., you send multiple requests to insert each row, to
"./process/newRow.php"

The code might work, depending on what happens in each of the PHP scripts, but it is not efficient. Each HTTP request takes time, and each connects to SQL Server, runs queries, and disconnects. Furthermore, you cannot use a SQL transaction, as
you have discovered.

Solution:

Gather all your data using javascript, and send it all in one go, to a single php script.

All replies

There are limitations when you enable MARS. For each query without specified explicitly transaction, it will run in an implicitly transaction. In your scenario, a new transaction will start with $nextQuery if the first transaction with $firstQuery does not
arise errors. However, it is not allowed in one connection, to start a second transaction while the first transaction is still active. There are two ways to work around this issue, please pay attention to this blog elaborated on it:
MARS, Transactions and SQL Error 3997, 3988 or 3983.

Thank you Stephanie for your answer. I thought you meant the problem could be about MARS. So I removed it from my codes and the current edited pseudo code is what I have in my application.
Please pay attention that the problem I have is not for executing queries. I get the error when I want to start the transaction. By the way I think all queries in my codes run in just one transaction (As Robert mentioned too).
Another strange item is that I have this code in another part of my application. And any error don't occur when I run the Transaction.

Robert, thank you for your attention to my question.
I removed MARS from my code. But still I get the error.
$stmt1 and $stmt2 will update 2 tables. But I think regarding to what I mentioned in my pseudo code, I don't enter to any transaction process. I get the error while I'm checking if is there ability to create a new transaction or no.

Rob, I could not solve the problem.
I'm sure that the connection is ready to use, no transaction is running before calling new transaction; But still I get the error :(
The process does not enter to the IF block. It stops in the IF statement condition and shows what sqlsrv_errors() returns in the ELSE block.

I'm so occupied Rob. I'm working on this time consuming problem for about 2 weeks and it did not solve yet.

Please look at the code below. It's a combination of PHP and jQuery that I've used in my project.
When I press a button, it calls apply() javascript function ...

You cannot span multiple PHP requests to the server in a transaction. Even if you could it's bad practice.

Each post to the server is a separate php request, and each one does not know about the open transaction, or the open connection to SQL Server.

You could achieve what you want by writing your own mock transaction - write your results to temporary tables, or mark your rows as incomplete (using your own schema), then in *one* final server request, perform a routine that makes good all the temporary
changes, or removes them, etc.

The problem you have encountered is most common with ASP developers, because ASP completely blurs the distinction between client and server-side execution of code. For example, it is possible to put code in your ASP 'application' which responds to
the click of a button in a browser, as if it is an interactive event - it isn't, the button click happens half way around the world. To achieve the effect and manage the process ASP obfuscates the simplicity of HTML and Javascript.

I'm no fan of jQuery either, because it makes Javascript look like something else, and is quite difficult to read.

To solve your problem, let's start with what PHP (or ASP or any other HTTP processor) does - it generates a web page and sends it to a browser. It creates some text, that's all.

In your example, your Javascript function is a piece of text being created by PHP before it gets sent to the browser...

While your file is processed in PHP (remember PHP is just creating text to send to the browser), these PHP functions are executed. When the script ends, all resources are cleaned up by PHP, all SQL Server connections are closed:

The browser receives all of the text from your sample without the PHP code. You cannot use the outcome of a Javascript 'if' statement to alter the PHP flow of control - in PHP it's just a piece of text, and Javascript never sees that PHP block.
For example:

When the method 'apply()' is executed in the web browser, several requests are sent to PHP. Each one is executed, result sent back to the browser and closed. In summary, you are:

Sending a POST request to PHP page "./process/editDoc.php".

When PHP replies to 1., you send another POST request to "./process/removeRows.php".

When PHP replies to 2., you send multiple requests to insert each row, to
"./process/newRow.php"

The code might work, depending on what happens in each of the PHP scripts, but it is not efficient. Each HTTP request takes time, and each connects to SQL Server, runs queries, and disconnects. Furthermore, you cannot use a SQL transaction, as
you have discovered.

Solution:

Gather all your data using javascript, and send it all in one go, to a single php script.

Please take a look at this sample code, and run it for yourself and see the result.
It's a small sample for what you said in your last answer. But believe it or not, it's working well. It's a successful implementation of both server-side and client-side combination.