none
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 transaction
    2- send information to PHP files through jQuery and check the result sent by JSON
    3- 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.

    // This is my pseudo code if (sqlsrv_begin_transaction( $sqlsrv->sqlsrvLink ) === true) { $firstQuery = sqlsrv_query($stmt1); if (!$firstQuery) { sqlsrv_rollback(); } else { $nextQuery = sqlsrv_query($stmt2); if (!$nextQuery) { sqlsrv_rollback(); } else { sqlsrv_commit(); } } }

    else

    {
    print_r(sqlsrv_errors()); // Here is where I get the error mentioned at the end of this post.
    }

    I included the my own sqlsrv class to the first part of index.php containing the methods below:

    function __construct($dbServerName,$dbUsername,$dbPassword,$dbName)
    {
        $connectionInfo = array("Database"=> $dbName, "CharacterSet" => "UTF-8");
        $this->sqlsrvLink = sqlsrv_connect($dbServerName, $connectionInfo);
        if ($this->sqlsrvLink === false) {
            $this->sqlsrvError = sqlsrv_errors();
        }
    }
    
    function __destruct()
    {
        sqlsrv_close($this->sqlsrvLink);
    }

    The problem I have is this error:

    [Microsoft][SQL Server Native Client 10.0][SQL Server] New transaction is not allowed because there are other threads running in the session

    What's the problem ? None of the previous posts in the forum regarding to this issue could help me :(





    Saturday, March 03, 2012 9:12 AM

Answers

  • Hi Mohammad

    Solution is at the bottom if you don't want to read my waffle!

    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:

    <?php
    	if (sqlsrv_begin_transaction( $sqlsrv->sqlsrvLink ) === true) 
    	{
    		sqlsrv_rollback( $sqlsrv->sqlsrvLink ); 
    		sqlsrv_commit( $sqlsrv->sqlsrvLink ); 
    		sqlsrv_rollback( $sqlsrv->sqlsrvLink ); 
    		sqlsrv_rollback( $sqlsrv->sqlsrvLink ); 
    	} 
    	else 
    	{
    		$errors = array();
    		$errors = sqlsrv_errors();
    		$errorMessage = '';
    		foreach($errors as $error) 
    		{
    			$errorMessage = $error['message'].'\n';
    		}
    		echo("alert('".$errorMessage."');");
    	}
    ?>

    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:

    if (err) 
    {
    	<?php sqlsrv_rollback( $sqlsrv->sqlsrvLink ); ?>
    	alert('Error');
    }

    Is the same as this:

    <?php 
    echo "if (err)\r\n"; 
    echo "{\r\n";
    sqlsrv_rollback( $sqlsrv->sqlsrvLink );
    echo "    alert('Error');\r\n";
    echo "}\r\n";
    ?>
    


    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:

    1. Sending a POST request to PHP page "./process/editDoc.php".
    2. When PHP replies to 1., you send another POST request to "./process/removeRows.php".
    3. 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.

    <script type="text/javascript">
    function apply()
    {
    	var myData = { fdate:'', des:'', doc_id:'', rows:[] };
    	
    	myData.fdate = $('#fdate').val();
    	myData.des = $('#des').val();
    	myData.doc_id = $('#doc_id').val();
    
    	var rows = ($('#myTable tr').length)-1;
    	// add new rows
    	var err = false;
    	for (i = 1 ; i <= rows ; i++) 
    	{
    		var account = $('#account_'+i).val();
    		var bed     = $('#bed_'+i).val();
    		var bes     = $('#bes_'+i).val();
    		var des     = $('#des_'+i).val();
    		bed     = bed.replace(/,/g,'');
    		bes     = bes.replace(/,/g,'');
    									
    		if (account == '' && (bed != '0' || bes != '0')) 
    		{
    			$('#account_' + i).css('background-color','gray');
    		}
    									
    		if (account != '') 
    			myData.rows.push( {	account:account, bed:bed, bes:bes, des:des, docId:myData.doc_id} );
    	}
    
    	$.post("./process/saveRecord.php", myData, function(data)
    			{
    				$("#msg").html();
    				if (data.result == 0) 
    				{
    					err = true;
    					break;
    				}
    			}, "json" );
    
    }
    </script>

    saveRecord.php might look like this:

    <?php 
    /* Some pseudo code is included. 
     * This is a basic example.
     *
     * PHP file to handle AJAX post request.
     * 
     * Look into using sqlsrv_prepare when saving multiple rows, 
     * instead of sqlsrv_query.
     * 
     * */
    
    if ( $_SERVER['REQUEST_METHOD'] != 'POST' )
    {
    	// Send back whatever HTTP error you want here.
    	header("{$_SERVER['SERVER_PROTOCOL']} 404 Not Found");
    	exit( "Request failed" );
    }
    
    $conn = sqlsrv_connect('MYSERVER', $options);
    
    // Call some pseudo function that sorts the post data out...
    $myData = ValidateMyPostData($_POST['fdate'], $POST['des'], $_POST['doc_id'], $_POST['rows']);
    
    if ( sqlsrv_begin_transaction($conn) )
    {
    	$stmt = sqlsrv_query($conn, 'Update tblDocuments... where doc_id=?', array($myData['doc_id'], $myData['others']) );
    	if ( $stmt )
    	{
    		$stmt = sqlsrv_query($conn, 'Delete previous rows where doc_id=?', array($myData['doc_id']));
    	}
    	
    	if ( $stmt )
    	{
    		foreach ( $myData['rows'] as $ct=>$row )
    		{
    			if ( !($stmt=sqlsrv_query($conn, 'Insert new row...' )) )
    				break;
    		}
    	}
    	
    	if ( $stmt )
    	{
    		sqlsrv_commit($conn);
    		return; // normal http 200 exit.  Echo whatever you want to send to the browser.
    	}
    	else 
    	{
    		// handle sqlsrv_errors() etc. before calling rollback.
    		sqlsrv_rollback($conn);
    	}
    	header("{$_SERVER['SERVER_PROTOCOL']} 404 Error");
    	echo "Save error";
    }
    ?>


    Rob

    Tuesday, March 13, 2012 1:42 PM

All replies

  • Hi mbsmt,

    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.


    Stephanie Lv

    TechNet Community Support

    Monday, March 05, 2012 11:16 AM
  • This is not an issue with the PHP driver...

    What Stephanie says is true, but according to your code, all the statements are running inside the same transaction.

    The way your pseudo code flows also shows no need for MARS to be active.

    Please could you post some more pseudo code for what happens in $stmt1 and $stmt2 ? 


    Rob

    Tuesday, March 06, 2012 4:03 PM
  • 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.

    I'm waiting for your valuable ideas.
    Thank you very much.



    Saturday, March 10, 2012 6:31 AM
  • 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.

    Help me using your experiences please.
    Thank you very much.

    Saturday, March 10, 2012 6:37 AM
  • Mohammad

    Please see my answer to your follow-up question, which I am assuming is about the same subject:
    http://social.msdn.microsoft.com/Forums/en/sqldriverforphp/thread/ee247845-27b9-4f77-b35c-c5854e187c7a


    Rob

    Saturday, March 10, 2012 7:54 PM
  • 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 ...

    function apply(){ <?php if (sqlsrv_begin_transaction( $sqlsrv->sqlsrvLink ) === true) { ?> fdate = $('#fdate').val(); des = $('#des').val(); doc_id = $('#doc_id').val(); $.post("./process/editDoc.php",{ fdate:fdate, des:des, doc_id:doc_id },function(data){ $("#msg").html(); if (data.result == 1) { // Remove all previous rows $.post("./process/removeRows.php",{ docId:doc_id },function(data){ if (data.result == 1) { var rows = ($('#myTable tr').length)-1; // Add new rows doc_id = $('#doc_id').val(); var err = false; for (i = 1 ; i <= rows ; i++) { account = $('#account_'+i).val(); bed = $('#bed_'+i).val(); bes = $('#bes_'+i).val(); des = $('#des_'+i).val(); bed = bed.replace(/,/g,''); bes = bes.replace(/,/g,''); if (account == '' && (bed != '0' || bes != '0')) { $('#account_' + i).css('background-color','gray'); } if (account != '') { $.post("./process/newRow.php",{ account:account, bed:bed, bes:bes, des:des, docId:doc_id },function(data){ $("#msg").html(); if (data.result == 0) { err = true; break; } }, "json" ); } } if (err) { <?php sqlsrv_rollback( $sqlsrv->sqlsrvLink ); ?> alert('Error'); } else { <?php sqlsrv_commit( $sqlsrv->sqlsrvLink ); ?> window.location = 'index.php?subject=document&section=editDoc&status=done'; } } else { <?php sqlsrv_rollback( $sqlsrv->sqlsrvLink ); ?> $('#loading').html(''); $("#msg").html('<img src="./public/images/reject.png" style="position:relative; top:4px"> <span style="color:red"> '+data.msg+'</span>'); } }, "json" ); } else { <?php sqlsrv_rollback( $sqlsrv->sqlsrvLink ); ?> $('#loading').html(''); $("#msg").html('<img src="./public/images/reject.png" style="position:relative; top:4px"> <span style="color:red"> '+data.msg+'</span>'); } }, "json" ); <?php } else { $errors = array(); $errors = sqlsrv_errors(); $errorMessage = ''; foreach($errors as $error) { $errorMessage = $error['message'].'\n'; }
    echo("alert('".$errorMessage."');");
    }
    ?>
    }


    Sunday, March 11, 2012 6:25 AM
  • Hi Mohammad

    Nice bit of code in theory, but unfortunately it won't work.

    Have you tried it without the transaction in place?

    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.


    Rob

    Monday, March 12, 2012 2:06 AM
  • Thank you Rob.
    The most important thing that I was seeking for, is what you said about my code accuracy.
    I will try what you said, and I hope your suggestion can help me to solve this issue.

    Wish all the best to you.

    Monday, March 12, 2012 5:59 AM
  • Rob, I got the base of your strategy. But, would you give me a sample code to help me to get your idea completely ?

    I have some records in database in two table (tblDocuments and tblDocsRows), and I want to do these steps on them:

    1. Update one row in tblDocuments (it has doc_id as its PK)
    2. Delete previous rows in tblDocsRows that have same doc_id as their FK.
    3. Insert new rows to tblDocsRows with same doc_id

    I need to see where I have to put my PHP and jQuery codes exactly. This combination could be the secret point in my question.

    Tuesday, March 13, 2012 6:27 AM
  • Hi Mohammad

    Solution is at the bottom if you don't want to read my waffle!

    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:

    <?php
    	if (sqlsrv_begin_transaction( $sqlsrv->sqlsrvLink ) === true) 
    	{
    		sqlsrv_rollback( $sqlsrv->sqlsrvLink ); 
    		sqlsrv_commit( $sqlsrv->sqlsrvLink ); 
    		sqlsrv_rollback( $sqlsrv->sqlsrvLink ); 
    		sqlsrv_rollback( $sqlsrv->sqlsrvLink ); 
    	} 
    	else 
    	{
    		$errors = array();
    		$errors = sqlsrv_errors();
    		$errorMessage = '';
    		foreach($errors as $error) 
    		{
    			$errorMessage = $error['message'].'\n';
    		}
    		echo("alert('".$errorMessage."');");
    	}
    ?>

    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:

    if (err) 
    {
    	<?php sqlsrv_rollback( $sqlsrv->sqlsrvLink ); ?>
    	alert('Error');
    }

    Is the same as this:

    <?php 
    echo "if (err)\r\n"; 
    echo "{\r\n";
    sqlsrv_rollback( $sqlsrv->sqlsrvLink );
    echo "    alert('Error');\r\n";
    echo "}\r\n";
    ?>
    


    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:

    1. Sending a POST request to PHP page "./process/editDoc.php".
    2. When PHP replies to 1., you send another POST request to "./process/removeRows.php".
    3. 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.

    <script type="text/javascript">
    function apply()
    {
    	var myData = { fdate:'', des:'', doc_id:'', rows:[] };
    	
    	myData.fdate = $('#fdate').val();
    	myData.des = $('#des').val();
    	myData.doc_id = $('#doc_id').val();
    
    	var rows = ($('#myTable tr').length)-1;
    	// add new rows
    	var err = false;
    	for (i = 1 ; i <= rows ; i++) 
    	{
    		var account = $('#account_'+i).val();
    		var bed     = $('#bed_'+i).val();
    		var bes     = $('#bes_'+i).val();
    		var des     = $('#des_'+i).val();
    		bed     = bed.replace(/,/g,'');
    		bes     = bes.replace(/,/g,'');
    									
    		if (account == '' && (bed != '0' || bes != '0')) 
    		{
    			$('#account_' + i).css('background-color','gray');
    		}
    									
    		if (account != '') 
    			myData.rows.push( {	account:account, bed:bed, bes:bes, des:des, docId:myData.doc_id} );
    	}
    
    	$.post("./process/saveRecord.php", myData, function(data)
    			{
    				$("#msg").html();
    				if (data.result == 0) 
    				{
    					err = true;
    					break;
    				}
    			}, "json" );
    
    }
    </script>

    saveRecord.php might look like this:

    <?php 
    /* Some pseudo code is included. 
     * This is a basic example.
     *
     * PHP file to handle AJAX post request.
     * 
     * Look into using sqlsrv_prepare when saving multiple rows, 
     * instead of sqlsrv_query.
     * 
     * */
    
    if ( $_SERVER['REQUEST_METHOD'] != 'POST' )
    {
    	// Send back whatever HTTP error you want here.
    	header("{$_SERVER['SERVER_PROTOCOL']} 404 Not Found");
    	exit( "Request failed" );
    }
    
    $conn = sqlsrv_connect('MYSERVER', $options);
    
    // Call some pseudo function that sorts the post data out...
    $myData = ValidateMyPostData($_POST['fdate'], $POST['des'], $_POST['doc_id'], $_POST['rows']);
    
    if ( sqlsrv_begin_transaction($conn) )
    {
    	$stmt = sqlsrv_query($conn, 'Update tblDocuments... where doc_id=?', array($myData['doc_id'], $myData['others']) );
    	if ( $stmt )
    	{
    		$stmt = sqlsrv_query($conn, 'Delete previous rows where doc_id=?', array($myData['doc_id']));
    	}
    	
    	if ( $stmt )
    	{
    		foreach ( $myData['rows'] as $ct=>$row )
    		{
    			if ( !($stmt=sqlsrv_query($conn, 'Insert new row...' )) )
    				break;
    		}
    	}
    	
    	if ( $stmt )
    	{
    		sqlsrv_commit($conn);
    		return; // normal http 200 exit.  Echo whatever you want to send to the browser.
    	}
    	else 
    	{
    		// handle sqlsrv_errors() etc. before calling rollback.
    		sqlsrv_rollback($conn);
    	}
    	header("{$_SERVER['SERVER_PROTOCOL']} 404 Error");
    	echo "Save error";
    }
    ?>


    Rob

    Tuesday, March 13, 2012 1:42 PM
  • Thank you Rob, for your valuable answer, and for your patient on helping me for this problem.
    Wednesday, March 14, 2012 7:25 AM
  • Hi Rob.
    Again this is me ...

    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.

    <?php
    if (1 < 2) {
    	$test = 1;
    ?>
    <script type="text/javascript">
    var test = '<?php echo($test) ?>';
    if (test == '1') {
    	<?php
    	$test = 2;
    	?>
    	alert('Successful; Test is <?php echo($test) ?>');
    } else {
    	alert('Failed; Test is <?php echo($test) ?>');
    }
    </script>
    <?php
    } else {
    	echo('Process stopped!');
    }
    ?>
    

    The interesting thing that I've occurred, is that what I had used in my application before as I said in previous posts, is working without any problem in one part of my application.

    Tell me your idea about this sample code.

    Saturday, March 24, 2012 5:51 PM
  • Hi again Mohammad

    What you've got is some hard-coded javascript, this is what your browser sees:

    <script type="text/javascript">
    var test = '1';
    if (test == '1') {
    	alert('Successful; Test is 2');
    } else {
    	alert('Failed; Test is 1');
    }
    </script>
    
    The PHP code is not run in your Javascript, it *creates* the Javascript that gets sent to the browser.  You need to separate what is done by PHP - it creates some text and sends it to a browser...


    Rob

    Monday, March 26, 2012 9:08 AM
  • Once again, you are right Rob. I got your mean ...

    Thank you.

    Monday, March 26, 2012 10:01 AM