Re: [PHP-DB] Sybase Peristent Connections Gotchas
On transactions, no this is my biggest concern. Say a php script performs a begin tran then aborts early due to an error (user error for instance), but the script error handling fails to rollback the transaction before it exits. Just 1 poorly coded script. Now the begin tran is still open, the next page served by that apache process inherits an open transaction. Even if it is coded and perform perfectly with its own begin / commit trans (remember you can nest trans), the transaction is ultimately never commited; but there is almost no indication to the script. This keeps going and going making this uncomitted trans bigger and bigger, locking more and more records. Finally one of 2 things will happen. 1. The locks get so big that users may get blocked or the max locks exceed the sybase config. 2. The apache process may actually exit if its defined for a max usage or is trimmed by the web server due to max running daemons. In case 1, application support would probably restart the web server to release the locks. In case 2 apache itself does it. Either way, the transaction and all the subsequent nested ones will be rolled back, as if they never occurred. It could be a big database headache. The best way I can forsee to prevent this is to always perform a rollback tran after a call to sybase_pconnect to make sure there isn't an unclosed trans. As for the other points you make, I was expecting to perform a sybase_pconnect each execution anyway so that is automatic. I'm not sure how I can reset the handle in Sybase? If its possible I'd like to know how. The more I think about this, it seems that this whole thing is more work than its worth. But if someone sees some effective ways to handle some of these issues I'd like to hear them. Brian Jeff Moss wrote: The biggest problem I've had with persistent connections is the problems that arise when the connection goes down. You have to monitor the connection status anyways (and reconnect on a failure), so it was usually easier to just connect every time. I don't know if this is specific to sybase. You also avoid headache dealing with multiple connections per process. Over a local ethernet this was usually such a short delay that it didn't matter. Typically I don't care much for speed, you avoid a lot of headache avoiding the persistent connections, but the tradeoff is speed of course. It seems to make a lot more sense to me to just reset the handle to drop all temp tables and that. As for the transactions, I think as long as you do the transaction all at once there would be no problem right? If it was a problem in the middle of a socket write, chances are the socket closed also, right? -Jeff Brian Foddy wrote: I've been using PHP4/5 and Sybase for several years, using standard sybase_connect. Today I tried playing around with pconnect to get aquainted. I expected one simple condition of a use database from one web page affecting another, and easilly handled that with a connection wrapper that re-uses the proper database with each reconnection. A couple other more troublesome issues also quickly came up. 1. Any #temp database tables are not destroyed between calls. I can probably work around this with some minor coding changes to manually drop temp tables. 2. Any call to environmental set commands like set isolation remain in effect after the web page is complete. Again with some work I could probably recode some pages to not change these values, or reset them when complete. 3. The potentially most bothersome would be a page failing to call commit tran/rollback tran, especially during some error condition. Its easy to think any uncommited tran is rolled back when the page exists. But persistent connections won't do this. This could be a disaster by leaving open a transaction causing all subsequent calls to never be commited... I might be able to create some wrapper that always resets the transaction state before starting?? I'm curious how others have attempted to solve these problems, and others I haven't thought of yet. Clearly many can be avoided by having clean code, but just 1 exception... Brian -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Sybase Peristent Connections Gotchas
Hi Brian Why don't you just avoid using sybace_pconnect() with transactions? I use ODBTP to connect to SQL Server and Sybase databases, and this is not an issue. The reason is because ODBTP involves the use of a mediating service that pools all connections, and automatically rollbacks transactions upon client disconnection. The odbtp extension does not support persistent connections, since they are pooled by the ODBTP server. And, if an odbtp script aborts, the odbtp client connection will be terminated, which will cause the odbtp server to rollback the transaction. However, the underlyning database connection will still be pooled by the server for use by another client. Persistent connections may not be the best way to implement database connection reusage. Not only are they subject to undesirable open transactions, they will never timeout, and a single web client can cause a database connection to remain open for each web server child process. Connection pooling may be a better alternative, however, it requires a middle-man server to manage the pool. Unfortuantely, ODBTP requires the server to be placed on a Win32 host that can access the database with its local ODBC facilities. For some developers, this is not always feasible. If it's feasible for you, then your problem will be solved by using the odbtp extension. Otherwise, you can't use persistent connections. -- bob On Wed, 15 Sep 2004, Brian Foddy wrote: On transactions, no this is my biggest concern. Say a php script performs a begin tran then aborts early due to an error (user error for instance), but the script error handling fails to rollback the transaction before it exits. Just 1 poorly coded script. Now the begin tran is still open, the next page served by that apache process inherits an open transaction. Even if it is coded and perform perfectly with its own begin / commit trans (remember you can nest trans), the transaction is ultimately never commited; but there is almost no indication to the script. This keeps going and going making this uncomitted trans bigger and bigger, locking more and more records. Finally one of 2 things will happen. 1. The locks get so big that users may get blocked or the max locks exceed the sybase config. 2. The apache process may actually exit if its defined for a max usage or is trimmed by the web server due to max running daemons. In case 1, application support would probably restart the web server to release the locks. In case 2 apache itself does it. Either way, the transaction and all the subsequent nested ones will be rolled back, as if they never occurred. It could be a big database headache. The best way I can forsee to prevent this is to always perform a rollback tran after a call to sybase_pconnect to make sure there isn't an unclosed trans. As for the other points you make, I was expecting to perform a sybase_pconnect each execution anyway so that is automatic. I'm not sure how I can reset the handle in Sybase? If its possible I'd like to know how. The more I think about this, it seems that this whole thing is more work than its worth. But if someone sees some effective ways to handle some of these issues I'd like to hear them. Brian Jeff Moss wrote: The biggest problem I've had with persistent connections is the problems that arise when the connection goes down. You have to monitor the connection status anyways (and reconnect on a failure), so it was usually easier to just connect every time. I don't know if this is specific to sybase. You also avoid headache dealing with multiple connections per process. Over a local ethernet this was usually such a short delay that it didn't matter. Typically I don't care much for speed, you avoid a lot of headache avoiding the persistent connections, but the tradeoff is speed of course. It seems to make a lot more sense to me to just reset the handle to drop all temp tables and that. As for the transactions, I think as long as you do the transaction all at once there would be no problem right? If it was a problem in the middle of a socket write, chances are the socket closed also, right? -Jeff Brian Foddy wrote: I've been using PHP4/5 and Sybase for several years, using standard sybase_connect. Today I tried playing around with pconnect to get aquainted. I expected one simple condition of a use database from one web page affecting another, and easilly handled that with a connection wrapper that re-uses the proper database with each reconnection. A couple other more troublesome issues also quickly came up. 1. Any #temp database tables are not destroyed between calls. I can probably work around this with some minor coding changes to manually drop temp tables. 2. Any call to environmental set commands like set isolation remain in effect after the web page is complete. Again with some work I could probably recode some
Re: [PHP-DB] Sybase Peristent Connections Gotchas
Bob, I agree there are inherit limitations and better ways to pool db connections. Any decent middleware package would probably handle these problems very easily. I just thought that some people may have found simplier ways to realize the benefits of pconnect and reduce some of the side effects. It may be in my situation, it just isn't worth the troubles. I just thought I'd ask... Thanks, Brian Robert Twitty wrote: Hi Brian Why don't you just avoid using sybace_pconnect() with transactions? I use ODBTP to connect to SQL Server and Sybase databases, and this is not an issue. The reason is because ODBTP involves the use of a mediating service that pools all connections, and automatically rollbacks transactions upon client disconnection. The odbtp extension does not support persistent connections, since they are pooled by the ODBTP server. And, if an odbtp script aborts, the odbtp client connection will be terminated, which will cause the odbtp server to rollback the transaction. However, the underlyning database connection will still be pooled by the server for use by another client. Persistent connections may not be the best way to implement database connection reusage. Not only are they subject to undesirable open transactions, they will never timeout, and a single web client can cause a database connection to remain open for each web server child process. Connection pooling may be a better alternative, however, it requires a middle-man server to manage the pool. Unfortuantely, ODBTP requires the server to be placed on a Win32 host that can access the database with its local ODBC facilities. For some developers, this is not always feasible. If it's feasible for you, then your problem will be solved by using the odbtp extension. Otherwise, you can't use persistent connections. -- bob On Wed, 15 Sep 2004, Brian Foddy wrote: On transactions, no this is my biggest concern. Say a php script performs a begin tran then aborts early due to an error (user error for instance), but the script error handling fails to rollback the transaction before it exits. Just 1 poorly coded script. Now the begin tran is still open, the next page served by that apache process inherits an open transaction. Even if it is coded and perform perfectly with its own begin / commit trans (remember you can nest trans), the transaction is ultimately never commited; but there is almost no indication to the script. This keeps going and going making this uncomitted trans bigger and bigger, locking more and more records. Finally one of 2 things will happen. 1. The locks get so big that users may get blocked or the max locks exceed the sybase config. 2. The apache process may actually exit if its defined for a max usage or is trimmed by the web server due to max running daemons. In case 1, application support would probably restart the web server to release the locks. In case 2 apache itself does it. Either way, the transaction and all the subsequent nested ones will be rolled back, as if they never occurred. It could be a big database headache. The best way I can forsee to prevent this is to always perform a rollback tran after a call to sybase_pconnect to make sure there isn't an unclosed trans. As for the other points you make, I was expecting to perform a sybase_pconnect each execution anyway so that is automatic. I'm not sure how I can reset the handle in Sybase? If its possible I'd like to know how. The more I think about this, it seems that this whole thing is more work than its worth. But if someone sees some effective ways to handle some of these issues I'd like to hear them. Brian Jeff Moss wrote: The biggest problem I've had with persistent connections is the problems that arise when the connection goes down. You have to monitor the connection status anyways (and reconnect on a failure), so it was usually easier to just connect every time. I don't know if this is specific to sybase. You also avoid headache dealing with multiple connections per process. Over a local ethernet this was usually such a short delay that it didn't matter. Typically I don't care much for speed, you avoid a lot of headache avoiding the persistent connections, but the tradeoff is speed of course. It seems to make a lot more sense to me to just reset the handle to drop all temp tables and that. As for the transactions, I think as long as you do the transaction all at once there would be no problem right? If it was a problem in the middle of a socket write, chances are the socket closed also, right? -Jeff Brian Foddy wrote: I've been using PHP4/5 and Sybase for several years, using standard sybase_connect. Today I tried playing around with pconnect to get aquainted. I expected one simple condition of a use database from one web page affecting another, and easilly handled that with a connection wrapper that re-uses the proper database with each reconnection. A couple other more troublesome issues also quickly came up. 1. Any #temp
[PHP-DB] Sybase Peristent Connections Gotchas
I've been using PHP4/5 and Sybase for several years, using standard sybase_connect. Today I tried playing around with pconnect to get aquainted. I expected one simple condition of a use database from one web page affecting another, and easilly handled that with a connection wrapper that re-uses the proper database with each reconnection. A couple other more troublesome issues also quickly came up. 1. Any #temp database tables are not destroyed between calls. I can probably work around this with some minor coding changes to manually drop temp tables. 2. Any call to environmental set commands like set isolation remain in effect after the web page is complete. Again with some work I could probably recode some pages to not change these values, or reset them when complete. 3. The potentially most bothersome would be a page failing to call commit tran/rollback tran, especially during some error condition. Its easy to think any uncommited tran is rolled back when the page exists. But persistent connections won't do this. This could be a disaster by leaving open a transaction causing all subsequent calls to never be commited... I might be able to create some wrapper that always resets the transaction state before starting?? I'm curious how others have attempted to solve these problems, and others I haven't thought of yet. Clearly many can be avoided by having clean code, but just 1 exception... Brian -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Sybase Peristent Connections Gotchas
The biggest problem I've had with persistent connections is the problems that arise when the connection goes down. You have to monitor the connection status anyways (and reconnect on a failure), so it was usually easier to just connect every time. I don't know if this is specific to sybase. You also avoid headache dealing with multiple connections per process. Over a local ethernet this was usually such a short delay that it didn't matter. Typically I don't care much for speed, you avoid a lot of headache avoiding the persistent connections, but the tradeoff is speed of course. It seems to make a lot more sense to me to just reset the handle to drop all temp tables and that. As for the transactions, I think as long as you do the transaction all at once there would be no problem right? If it was a problem in the middle of a socket write, chances are the socket closed also, right? -Jeff Brian Foddy wrote: I've been using PHP4/5 and Sybase for several years, using standard sybase_connect. Today I tried playing around with pconnect to get aquainted. I expected one simple condition of a use database from one web page affecting another, and easilly handled that with a connection wrapper that re-uses the proper database with each reconnection. A couple other more troublesome issues also quickly came up. 1. Any #temp database tables are not destroyed between calls. I can probably work around this with some minor coding changes to manually drop temp tables. 2. Any call to environmental set commands like set isolation remain in effect after the web page is complete. Again with some work I could probably recode some pages to not change these values, or reset them when complete. 3. The potentially most bothersome would be a page failing to call commit tran/rollback tran, especially during some error condition. Its easy to think any uncommited tran is rolled back when the page exists. But persistent connections won't do this. This could be a disaster by leaving open a transaction causing all subsequent calls to never be commited... I might be able to create some wrapper that always resets the transaction state before starting?? I'm curious how others have attempted to solve these problems, and others I haven't thought of yet. Clearly many can be avoided by having clean code, but just 1 exception... Brian -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
