Does the server-database connection mimic the client-server stateless connection?

[Vartan]

I understand how the client-server connection works.
A user (web browser) sends an http request to a web server listening
on port 80. The web server forks a child. The child opens a connection
to the current user on a port other than 80 to allow the server to
keep listening to other users on port 80. The child process performs
the request, creates a response, sends it to the user and terminates
the connection. This is a stateless connection. To track the user, the
server creates a session and a sessionid. It includes the session id
(PHPSESSID) inside a cookie and sends it to the client.

My question is this. Does the same thing happen between a server and a
database? Namely, does the database (Sybase Anywhere) listening on
port 2638, fork a child where the child connects to the server child
on a different port, fetches the data, sends it to the server child
and terminates its connection? Does it use the same session id to
track a specific user and therefore perform rollbacks and commits?

Thanks
Vartan

[Mohammed Abouzour]

On Wed, Aug 18, 2010 at 12:11 PM, Vartan <vart...@gmail.com> wrote:

I understand how the client-server connection works.
A user (web browser) sends an http request to a web server listening
on port 80. The web server forks a child. The child opens a connection
to the current user on a port other than 80 to allow the server to
keep listening to other users on port 80. The child process performs
the request, creates a response, sends it to the user and terminates
the connection. This is a stateless connection. To track the user, the
server creates a session and a sessionid. It includes the session id
(PHPSESSID) inside a cookie and sends it to the client.

This is not entirely correct. The web server first accepts
the connection request on port 80. Accepting the connection effectively opens
a socket on a different local port number. Once this new socket is created, the
web server forks a child and hands that child the file descriptor of the socket. The child sends
the response to the original web request on that file descriptor.

Also, the above description of a web server is only specific to multi-process
web servers. Apache is one example. Not all web servers are multi-process. Other
web servers are multi-threaded. In a multi-threaded web server threads within the web
server process are used to process the web request and respond back to the web client.
IIS is one example of a multi-threaded web server.

My question is this. Does the same thing happen between a server and a
database? Namely, does the database (Sybase Anywhere) listening on
port 2638, fork a child where the child connects to the server child
on a different port, fetches the data, sends it to the server child
and terminates its connection? Does it use the same session id to
track a specific user and therefore perform rollbacks and commits?

SQL Anywhere is a multithreaded database server. No forking happens in handling
database client side requests. All threads live in one address space (the process)
and share/pass the request/data appropriately through mutual exclusion.
When a connection is first established to the database server at port 2638,
an authentication packet is first received from the client identifying the
userid and password of that client. This information is stored as part of the
connection information. After a connection is established, the client starts
sending requests to the server through that connection. A connection is only terminated
if a client makes a request to close its connection. This is different than web servers that
automatically close the connection once the response has been sent.

You might want to refer to this URL:
http://dcx.sybase.com/index.html#1200en/dbadmin/control-threading-cmdline-running.html
for a discussion on threading in SQL Anywhere.

I am not sure if that answers your question about SQL Anywhere or not, but
if not, please clarify what you mean by the term "server" as you are using
it in different places in your question without clarify exactly what you mean by it.

--
Mohammed

[Eric Farrar]

Hi Vartan,

Unlike the stateless connection that you are describing with Apache,
the connection to SQL Anywhere is a fully stateful connection.

- Eroc

On Aug 18, 1:57 pm, Mohammed Abouzour <abouz...@gmail.com> wrote:

> You might want to refer to this URL:http://dcx.sybase.com/index.html#1200en/dbadmin/control-threading-cmd...

[Vartan]

Thanks for both replies, they clarified some stuff in my head. By the
server in my question I meant a web server or in my case IIS. The
reason I brought up this question is the confusion I have over
sasql_connect() and sasql_pconnect(). From what I understand pconnect
doesn't give you any functionality that is not possible with connect.

I have the following setup:

Server 1:
------------
Windows Server 2003 SP 2
Microsoft IIS 6.0
PHP 5.3.2
php-5.3.2_sqlanywhere_nts.dll

phpinfo() returns:
------------------------
Server API - CGI/FastCGI
Thread Safety - disabled
sqlanywhere.auto_commit - Off

Server 2:
-------------
SQL Anywhere 11


The web app has to perform rollbacks to undo the last operation
(insert, delete and updates).
I can get it to work with pconnect but not connect.


pconnect is as follows:
---------------------------------
$conn = sasql_pconnect("Uid=".$uid."; Pwd=".$pid."; ServerName=blah;
CommLinks=tcpip(host=blah;port=2638)");
sasql_query($conn, "begin transaction");
sasql_query($conn, "update query ...");
I then do a select and see the update
sasql_query($conn, "rollback transaction");
I then do a select and see the update undone

so far so good.


connect is a follows:
-----------------------------
$conn = sasql_connect("Uid=".$uid."; Pwd=".$pid."; ServerName=blah;
CommLinks=tcpip(host=blah;port=2638)");
sasql_query($conn, "begin transaction");
sasql_query($conn, "update query ...");
I then do a select and don't even see the update

Is it because of my setup?

Vartan

> > Mohammed- Hide quoted text -
>
> - Show quoted text -

[Mohammed Abouzour]

On Wed, Aug 18, 2010 at 5:20 PM, Vartan <vart...@gmail.com> wrote:

reason I brought up this question is the confusion I have over
sasql_connect() and sasql_pconnect(). From what I understand pconnect
doesn't give you any functionality that is not possible with connect.

In the case of IIS and FastCGI, there actually might be a performance advantage to using
pconnect(). The way IIS executes PHP scripts is by spawning a child process (through the
FastCGI IIS extension) and that child process would be the php-cgi.exe. In a regular CGI
environment, after php-cgi.exe has completed the request, php-cgi.exe would terminate.
With FastCGI, the child php-cgi.exe is actually kept around and is not terminated. As a result
if you use sasql_pconnect(), subsequent php executions will cause the connection to be reused
and that might gain you the benifit of not have to re-establish the connection as in the case
of sasql_connect(). You should be aware however, that with many php-cgi.exe child processes
hanging around, there will be at least one open connection per child php-cgi.exe process
hanging around. 

The web app has to perform rollbacks to undo the last operation
(insert, delete and updates).
I can get it to work with pconnect but not connect.

... 

Is it because of my setup?

 

I just tried a similar test and I am not reproducing the behaviour that you are describing.
What version of the SA PHP driver are you using? Can you post all the output of phpinfo()
for the sqlanywhere section only.

Also, I noticed you are using sasql_query( $conn, "Rollback" ) to rollback your transaction.
You should use sasql_rollback( $conn ) or sasql_commit( $conn ) as that result in better
performance in the client side libraries.

--
Mohammed

[Vartan]

Thanks agian. Here is my phpinfo output:

SQLAnywhere support enabled
---------------------------------------------
Allow Persistent Connections Yes
Persistent Connections 0/unlimited
Total Connections 0/unlimited
PHP SQLAnywhere driver version 2.0.9.0
SQLAnywhere client version 11.0.1.2044

Directive Local Value Master Value
----------------------------------------------------
sqlanywhere.allow_persistent On On
sqlanywhere.auto_commit Off Off
sqlanywhere.max_connections Unlimited Unlimited
sqlanywhere.max_persistent_connections Unlimited Unlimited
sqlanywhere.verbose_errors On On

Vartan


On Aug 18, 3:07 pm, Mohammed Abouzour <abouz...@gmail.com> wrote:

[Mohammed Abouzour]

On Wed, Aug 18, 2010 at 6:24 PM, Vartan <vart...@gmail.com> wrote:

SQLAnywhere support enabled
---------------------------------------------
Allow Persistent Connections  Yes
Persistent Connections  0/unlimited
Total Connections  0/unlimited
PHP SQLAnywhere driver version  2.0.9.0
SQLAnywhere client version  11.0.1.2044

I tried using version (2.0.9) but I still cannot reproduce your problem. Can you
trim down your repro to a small script and post it here. I have a feeling that your
connection object might be going out of scope and the transaction gets rolledback
and that's why you don't see your updates.

--
Mohammed

[Vartan]

Thanks Mohammed for all your help. I'll check my code to see if the
connection object gets out of scope, minimize the code and post it.

Vartan

On Aug 18, 3:37 pm, Mohammed Abouzour <abouz...@gmail.com> wrote:

[Vartan]

Hi Mohammed,
This test app will hopefully show the difference in behaviour I get
between sasql_connect() and sasql_pconnect().

Summary of what I get:
---------------------------------
When using sasql_connect():
If the update and select queries are done in one HTTP session (request-
response) then the select shows the correct updated result.
If the update is done in one HTTP session and the select in the next
HTTP session then the select does NOT show the updated result.

When using sasql_pconnect():
The select will show the correct updated result in both cases as
described above.


file test.php:
-----------------
<html>
<head>
<title>Test</title>
<script type="text/javascript">
function performAction(action) {
document.getElementById("action").value = action;
document.myform.submit();
}
</script>
</head>
<body>
<form name="myform" action="test.php" method="post">
<input type="button" value="select"
onclick="performAction('select');" />
<input type="button" value="update"
onclick="performAction('update');" />
<input type="button" value="rollback"
onclick="performAction('rollback');" />
<input type="button" value="commit"
onclick="performAction('commit');" />

<input type="hidden" id="action" name="myaction" value="" />
</form>
</body>
</html>

<?php
$conn = sasql_connect("Uid=blah; Pwd=blah; ServerName=blah;
CommLinks=tcpip(host=blah;port=blah)");
if($_POST) {
if($_POST['myaction'] == "rollback") {
sasql_rollback($conn);
// select($conn);
}
else if($_POST['myaction'] == "commit") {
sasql_commit($conn);
// select($conn);
}
else if($_POST['myaction'] == "select") {
select($conn);
}
else if($_POST['myaction'] == "update") {
sasql_query($conn, "update ....");
// select($conn);
}
}

function select($conn) {
$data = null;
$result = sasql_query($conn, "SELECT ....");
while ($obj = sasql_fetch_object($result))
$data = $obj->test;
echo($data);
sasql_free_result($result);
}
?>

The test app has these 4 buttons:
select, update, rollback and commit
auto_commit is set to off in php.ini

With the select() function commented out as shown in the code the
following tests are performed:
Test 1 with sasql_pconnect():
select
update
select - shows the updated result correctly

Test 2 with sasql_connect():
select
update
select - does NOT show the updated result ...

Now uncomment the select() function in 3 places and test again:
Test 1 with sasql_pconnect():
produces the correct updated result

Test 2 with sasql_connect():
select
update (which includes a select()) - shows the updated result
correctly.
select (i.e. a new HTTP session)- does NOT show the updated result ...


Vartan

[Mohammed Abouzour]

On Fri, Aug 20, 2010 at 11:42 AM, Vartan <vart...@gmail.com> wrote:

Summary of what I get:
---------------------------------
When using sasql_connect():
If the update and select queries are done in one HTTP session (request-
response) then the select shows the correct updated result.
If the update is done in one HTTP session and the select in the next
HTTP session then the select does NOT show the updated result.

I can see your problem. The issue is that each press on one of the
four buttons (select, update, rollback, commit) is actually a new request
to the server. When you use sasql_connect(), a new connection is establish
for that invocation of the php script. If you do an update action and do not call
sasql_commit() before the script terminates, then the transaction will be automatically
rolled back because the connection will be closed and the auto_commit option is
set to Off.

In the case of sasql_pconnect(), the connection is never closed, so between invocations
of the PHP script, the transaction is still active and that's why on the next select() you see the rows that
were updated. Although it might look that sasql_pconnect() is doing what you want, it's
actually the wrong way to write your application. You should not rely on sasql_pconnect()
keeping the transaction open between invocations to the PHP script. The purpose of sasql_pconnect()
is to improve the performance of the PHP script by caching the connection and not having to re-establishing
a connection every time the PHP script is invoked. Also, in other web server environments,
there is no guarantee that the same sub-process that was invoked to handle the first request
will be the same sub-process that will be handling the second request, in which case, even sasql_pconnect()
will not be working as you would like.

--
Mohammed

[Vartan]

I agree with you. So, what's the correct way to give the user the
ability to rollback say 5 minutes after the initial update using
sasql_connect function?

Thanks
Vartan



On Aug 20, 9:33 am, Mohammed Abouzour <abouz...@gmail.com> wrote:

[Eric Farrar]

Hello Vartan,

This is something that would typically be done at the application
level, and not at the database level. Trying to use database long-
running transactions in this way can have some unpleasant side effects
such as holding database locks that will cause others to block. As
well, you will only be able to "rollback" to the last thing you did.

There are a few different approaches to build an "undo" log in the
application level. Can you describe how the application will use this
feature?

Thanks,

- Eric

[Vartan]

The web app is used to display and manipulate (i.e. merge, split and
move) b-trees. I had been thinking about doing it in the application,
but I set it aside. Basically it would look at the log file and undo
the last step going back in history. I'm going to look into that
further more because as you said when I use isolation levels of other
than zero I get blocking and deadlocks if concurrent users need to
manipulate the same b-tree differently. I'll take any suggestions you
have.

Thanks for following my thread.
Vartan