Re: [PHP-DB] Transaction over persistent connection problem

2005-04-27 Thread Manuel Lemos
Hello,
on 04/27/2005 09:58 AM Oskar said the following:
You see - tables are locked and the only way to fix it is restarting MySQL.
Could you tell me what am I doing wrong, please?
I suspect that your scripts may be exiting once in a while with 
unfinished transactions due to bugs or errors that make it end 
prematurely. With persistent connections you need to rollback unfinished 
transations by the end of the script.

The solution of Metabase (portable database abstraction package) is to 
implicitly register a shutdown function that clean any pending transactions:

http://www.phpclasses.org/metabase
--
Regards,
Manuel Lemos
PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/
PHP Reviews - Reviews of PHP books and other products
http://www.phpclasses.org/reviews/
Metastorage - Data object relational mapping layer generator
http://www.meta-language.net/metastorage.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] Transaction over persistent connection problem

2005-04-27 Thread Oskar
Ok. So the idea of splitting one transaction into two steps of a script is
wrong?

I wanted to use it in a script that is importing big amount of datas (about
200k rows).

First step:
mysql_pconnect()
start transaction
insert rows and test errors
no errors = commit, mysql_close()
some errors = echo(Some errors occured. Do you want to keep 
non-error
entries in database? Yes/No);
Second step:
mysql_pconnect()
Yes = commit
No  = rollback
mysql_close()



 -Original Message-
 From: Manuel Lemos [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, April 27, 2005 4:21 PM
 To: php-db@lists.php.net
 Subject: Re: [PHP-DB] Transaction over persistent connection problem


 The solution of Metabase (portable database abstraction package) is to
 implicitly register a shutdown function that clean any pending
 transactions:

 http://www.phpclasses.org/metabase


 --

 Regards,
 Manuel Lemos

 PHP Classes - Free ready to use OOP components written in PHP
 http://www.phpclasses.org/

 PHP Reviews - Reviews of PHP books and other products
 http://www.phpclasses.org/reviews/

 Metastorage - Data object relational mapping layer generator
 http://www.meta-language.net/metastorage.html

 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Transaction over persistent connection problem

2005-04-27 Thread Manuel Lemos
Hello,
on 04/27/2005 11:49 AM Oskar said the following:
Ok. So the idea of splitting one transaction into two steps of a script is
wrong?
Yes, it is not possible to achive that. What happens is that first 
access to script is handled by one Web server process or thread and you 
cannot assure that the second access will be handled by the same Web 
process or server to finish the same transaction that was started.

Furthermore, you should never leave a transaction open that you cannot 
guarantee that it will finished in a very short notice, otherwise it may 
block the access to the whole database forever.

--
Regards,
Manuel Lemos
PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/
PHP Reviews - Reviews of PHP books and other products
http://www.phpclasses.org/reviews/
Metastorage - Data object relational mapping layer generator
http://www.meta-language.net/metastorage.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


RE: [PHP-DB] Transaction over persistent connection problem

2005-04-27 Thread Oskar

Thanks for your help, Manuel. Your advice saved me hours of searching the
web.

Regards, OKi98

 -Original Message-
 From: Manuel Lemos [mailto:[EMAIL PROTECTED]
 Sent: Wednesday, April 27, 2005 7:20 PM
 To: php-db@lists.php.net
 Subject: Re: [PHP-DB] Transaction over persistent connection problem


 Yes, it is not possible to achive that. What happens is that first
 access to script is handled by one Web server process or thread and you
 cannot assure that the second access will be handled by the same Web
 process or server to finish the same transaction that was started.

 Furthermore, you should never leave a transaction open that you cannot
 guarantee that it will finished in a very short notice, otherwise it may
 block the access to the whole database forever.


 --

 Regards,
 Manuel Lemos

 PHP Classes - Free ready to use OOP components written in PHP
 http://www.phpclasses.org/

 PHP Reviews - Reviews of PHP books and other products
 http://www.phpclasses.org/reviews/

 Metastorage - Data object relational mapping layer generator
 http://www.meta-language.net/metastorage.html

 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php




-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php



Re: [PHP-DB] Transaction over persistent connection problem

2005-04-27 Thread Jochem Maas
Manuel Lemos wrote:
Hello,
on 04/27/2005 11:49 AM Oskar said the following:
Ok. So the idea of splitting one transaction into two steps of a 
script is
wrong?

Yes, it is not possible to achive that. What happens is that first 
access to script is handled by one Web server process or thread and you 
cannot assure that the second access will be handled by the same Web 
process or server to finish the same transaction that was started.
some thoughts:
if he ran the the page/script on a seperate webserver (configured to
run one process), especially for this one task - to which only one
person [c|w]ould connect - then it would be technically doable no?
Furthermore, you should never leave a transaction open that you cannot 
guarantee that it will finished in a very short notice, otherwise it may 
say database server is in the US, and connecting server (that makes use of 
transactions)
is in Europe... how are you going to guarantee that the connecting server will 
never
suffer network or power loss midway thru a transaction? (I'd put money on the
fact that the same thing has crossed the minds of more than one database engine
developer.)
block the access to the whole database forever.
thats a broad statement. a transaction doesn't have to block access per 
definition,
and its not unfeasable to suggest that some database engines may account for the
possibility for 'hung' transaction in some kind of garbage collection 
routine...?

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


Re: [PHP-DB] Transaction over persistent connection problem

2005-04-27 Thread Manuel Lemos
Hello,
on 04/27/2005 08:13 PM Jochem Maas said the following:
Ok. So the idea of splitting one transaction into two steps of a 
script is
wrong?

Yes, it is not possible to achive that. What happens is that first 
access to script is handled by one Web server process or thread and 
you cannot assure that the second access will be handled by the same 
Web process or server to finish the same transaction that was started.
some thoughts:
if he ran the the page/script on a seperate webserver (configured to
run one process), especially for this one task - to which only one
person [c|w]ould connect - then it would be technically doable no?
It would be the same problem because the other separate Web server 
script would start the transaction and then it would end.

The problem is not running many processes per script but rather that PHP 
scripts exit after they serve their response.

Furthermore, if the main Web server could run many scripts in parallel 
that would ask the separate server to start a transaction. Even if it 
was feasable to end the transaction in a second request, how would the 
separate know which transaction was started by which client?

The problem all boils down to the fact that HTTP is stateless.
Even if starting and ending transactions on different request was 
feasible, transactions should be managed by a different type of server, 
not really by common HTTP servers.


Furthermore, you should never leave a transaction open that you cannot 
guarantee that it will finished in a very short notice, otherwise it may 
say database server is in the US, and connecting server (that makes use 
of transactions)
is in Europe... how are you going to guarantee that the connecting 
server will never
suffer network or power loss midway thru a transaction? (I'd put money 
on the
fact that the same thing has crossed the minds of more than one database 
engine
developer.)
Disconnected or timed-out transactions should be aborted and implicit 
rollback happens. However that happens in an abnormal situations. 
Timeout and rollback only happens to assure that there are no 
outstanding transactions living for more than a short period (were 
talking about seconds here).

If you need to start a more complex operation that needs to wait for 
user response during an unknown period, you need to use other kind of 
non-blocking locking methods.


block the access to the whole database forever.
thats a broad statement. a transaction doesn't have to block access per 
definition,
It must be atomic, therefore no more than once concurrent access can 
change the same data.


and its not unfeasable to suggest that some database engines may account 
for the
possibility for 'hung' transaction in some kind of garbage collection 
routine...?
It is not really a garbage collection but rather aborting transactions 
(rollback) after a timeout or disconnection.

--
Regards,
Manuel Lemos
PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/
PHP Reviews - Reviews of PHP books and other products
http://www.phpclasses.org/reviews/
Metastorage - Data object relational mapping layer generator
http://www.meta-language.net/metastorage.html
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php