Interbase &amp; transactions

Interbase & transactions

Anon

hi,

i do not know why, but ibase_connect behaves in the same way as ibase_pconnect when using the same arguments... in php-manual you can read: "In case a second call is made to ibase_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned..."

but this is a quite BIG problem, as far i can understand it (and i tried it). imagine following: i've got many transactions in my php scripts (using ibase_trans etc) - but when more than one user access my web, they share the connection and their transaction can be seen (from database) as nested (and they are not, of course, because each of them access as a separate user to www) and it can result in inconsistent db or deadlocks...

only when i use different username and pswd, the connections are different - and it works well. but we are excepting many and many users (of course :-)) on our www and we can not prepare so many user accesses. (not talking about licence to interbase - i.e. sum of concurent users accessing db)...

any hint will be VERY appreciated.

thank you,

robert

[deleted]

It is slightly more complex than that.

It is not possible for two scripts to use the same connection at the same time.
So if you run 100 scripts at the same time, that all use the same username and password, you will get 100 connections, all with their own transaction scope.

And if the API programmers did their job properly, you will also find that ending your script will rollback your transaction even though the connection is not really closed.

Try it out and see.

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

well, i'm not sure that you are right. look at the source code (from ibase module):

sprintf(hashed_details, "ibase%s%s%s%s%s%s_%s", SAFE_STRING(ib_server), SAFE_STRING(ib_uname), SAFE_STRING(ib_passwd), SAFE_STRING(ib_charset), SAFE_STRING(ib_buffers), SAFE_STRING(ib_dialect), SAFE_STRING(ib_role));

and this hashed_details is used for deciding wether to use an existing connection or to create a new one... it seems to me that all of them must be the same (the 100 you talked about) - or am i wrong?

thank you,

robert

[deleted]

You're wrong. 🙂

If you try to open the same connection (same username/password) twice in the same script, then you will get the same conneciton identifier.

But if you start two scripts they wil always get seperate connections.

Don't look at sourcecode to see what it might do, build a script and see what it does.

Anon

Methinks Robert was assuming Apache/PHP was threaded, in which case the interface code to interbase COULD act like that if programmed (im)properly... :-)

If he's running under Windows I wonder if it becomes an issue?

But you're 101% correct, don't assume what it will do, write some code and test it.

I'd write a script that did a lot of sleeping so it could hold connections open while a copy of itself ran and see if it works right.

[deleted]

The point remains that you cannot run more than one query at a time on one connection, so every script must get it's own connection. If you are running many seperate threads then the database has no option but to create separate connections...

Anon

hi,

thank you for your interest.

i'd like to make clear some circumstances about this topic: first of all - we are running apache on linux (ibase 6.0).

do you want to say by that that the whole theory with persistent connections is just a bubble? i mean when i use a persistent connection, so that every script which runs uses in fact a separate connection?

the only problem i need to solve is to make sure that every user accessing my www has a new, its own connection... (because of transactions).

thank you,

robert

[deleted]

You're not the first person to worry about this, and it can be a serious problem.
But fortunately the database and PHP designers also saw this problem and they tackled it.

There are a few things you need to have
clear in your mind.

Each script that runs requires it's own database connection. Two scripts that run at the same time cannot use the same connection at the same time.

If you run 100 scripts at the same time, you will need 100 connections. Those connections may use the same credentials to login, but they are seperate connections.

If you try to open two connections with the same credentials inside one script, then PHP will simply return a pointer to the first connection without actually creating a new databse connection. (sad but true).
Pconnect simply doesn't close the connections after the script ends. So if you run two scripts after eachother, the second script will get to use the exact same connection that the first script was using.

This is where a transaction problem could arise, if it weren't for the fact that the people who designed PHP and the database API thought of this. At least for postgreSQL, you cannot open a transaction in one script, exit the script, open the same connection in the next script, and commit or rollback. The PHP/the API should prevent this by 'resetting' (or whatever) the connection when your script exits. ANy open transactions are rolled back. But this is what you should test with a real script.

MySQL has a small 'problem' with persistant connections, in that you can insert a record in one script, and retreive the mysql_insert_id() in another. Not a big deal, but still...

"the only problem i need to solve is to make sure that every user accessing my www has a new, its own connection... (because of transactions)."

This is true. Scipts can only re-use connections once they are free. Once a script decides to use a connection, that connection is reserved for that script untill the script exits.

One connection can be used by one script at a time.

And remember, you cannot tell PHP which database connection to use, you can only request 'a' connection. So you cannot open a persistant connection called 'X', open a transaction, close the script, start a new script, use the same 'X' connection (which should be open because of pconnect) and commit. PHP simply does not let you choose the exact same connection, only 'a' connection with the same credentials.

few... so much typing so early in the morning...

A forum, a FAQ, what else do you need?