Re: [PHP] mysql_query blocking
Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? Daniel I can understand you asking the question here, but I suspect that the answer may come better from a list who has people who are more expert in MySQL? Personally - with Firebird - this is not a problem that ever arises since 'locking' is not something one would normally do and has only appeared IN Firebird because people 'expect' that is how a database works. Even if using it would actually be wrong :) The problem is that the database is the problem rather than PHP, hence the questions about which type of tables you are using in MySQL. From my personal experience, it's the way that you are using the database that may be wrong if it relies on 'locking' to achieve something that is not properly managed IN the database. Any database can provide 'locking', if that is really required, by additional flagging in the database - if the database itself does not provide the function properly. Such as setting an 'edit' flag on a record showing who is changing it, which can then be used to advise that fact to other users. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk// Firebird - http://www.firebirdsql.org/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
On 6/27/09 7:09 PM, Daniel Kolbo kolb0...@umn.edu wrote: Tom Worster wrote: On 6/27/09 3:15 PM, Daniel Kolbo kolb0...@umn.edu wrote: When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? would it be possible to test for presence of the lock before issuing the query that would block? if so, you could check the lock periodically until your time limit is up. would achieve you you're looking for? it would still be possible that after checking but before issuing the query the table's could be locked - albeit a much smaller chance. that's true. what would be nice for this kind of problem would be a kind of async api, perhaps a bit like curl_multi but with a way to check the state of an outstanding query (queued, processing, etc). much like a js script uses ajax. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK Even the apache TimeOut directive does not seem to work in this situation. Any help would be appreciated. Thanks, dK -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Ashley Sheridan wrote: On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk I issue a 'lock tables tablename write' and do some work. This is to be expected. I want a way that to stop mysql_query() from just waiting for the mysql server. ` -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
On 6/27/09 3:15 PM, Daniel Kolbo kolb0...@umn.edu wrote: When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? would it be possible to test for presence of the lock before issuing the query that would block? if so, you could check the lock periodically until your time limit is up. would achieve you you're looking for? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Why issue/do a lock at all? Shouldn't need a large lock at read bastien On Saturday, June 27, 2009, Daniel Kolbo kolb0...@umn.edu wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk I issue a 'lock tables tablename write' and do some work. This is to be expected. I want a way that to stop mysql_query() from just waiting for the mysql server. ` -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
On Saturday, June 27, 2009, Bastien Koert phps...@gmail.com wrote: Why issue/do a lock at all? Shouldn't need a large lock at read bastien On Saturday, June 27, 2009, Daniel Kolbo kolb0...@umn.edu wrote Ashley Sheridan wrote: On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk I issue a 'lock tables tablename write' and do some work. This is to be expected. I want a way that to stop mysql_query() from just waiting for the mysql server. ` -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Bastien Cat, the other other white meat -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Bastien Sent from my iPod On Jun 27, 2009, at 4:13 PM, Daniel Kolbo kolb0...@umn.edu wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk I issue a 'lock tables tablename write' and do some work. This is to be expected. I want a way that to stop mysql_query() from just waiting for the mysql server. ` -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Further thought, do you have indexes on the table? Bastien -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Tom Worster wrote: On 6/27/09 3:15 PM, Daniel Kolbo kolb0...@umn.edu wrote: When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? would it be possible to test for presence of the lock before issuing the query that would block? if so, you could check the lock periodically until your time limit is up. would achieve you you're looking for? it would still be possible that after checking but before issuing the query the table's could be locked - albeit a much smaller chance. Thanks for the input, dK -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Bastien Koert wrote: Why issue/do a lock at all? Shouldn't need a large lock at read bastien On Saturday, June 27, 2009, Daniel Kolbo kolb0...@umn.edu wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk http://www.ashleysheridan.co.uk I issue a 'lock tables tablename write' and do some work. This is to be expected. I want a way that to stop mysql_query() from just waiting for the mysql server. ` -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php a different user (user who is updating) would be doing the lock. thanks, dK -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
Phpster wrote: Bastien Sent from my iPod On Jun 27, 2009, at 4:13 PM, Daniel Kolbo kolb0...@umn.edu wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk I issue a 'lock tables tablename write' and do some work. This is to be expected. I want a way that to stop mysql_query() from just waiting for the mysql server. ` -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Further thought, do you have indexes on the table? Bastien Yes. The question is not about MySQL efficiency - i'll leave that to the MySQL email group. This question is about putting in place PHP safe guards. Thanks, dK -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] mysql_query blocking
On Jun 27, 2009, at 7:17 PM, Daniel Kolbo kolb0...@umn.edu wrote: Phpster wrote: Bastien Sent from my iPod On Jun 27, 2009, at 4:13 PM, Daniel Kolbo kolb0...@umn.edu wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 16:03 -0400, Daniel Kolbo wrote: Ashley Sheridan wrote: On Sat, 2009-06-27 at 15:15 -0400, Daniel Kolbo wrote: Hello, When a MySQL table is locked a php call of mysql_query() that requires that table will hang as the request blocks at the MySQL server until the table is unlocked. Is there a way to stop a mysql_query from hanging (by setting a time limit)? The php.ini directive max_execution_time does not help b/c: Note: The set_time_limit() function and the configuration directive max_execution_time only affect the execution time of the script itself. Any time spent on activity that happens outside the execution of the script such as system calls using system(), stream operations, database queries, etc. is not included when determining the maximum time that the script has been running. The my.ini directive table_lock_wait_timeout does not work either b/c from MySQL: This variable currently is unused. (I am using mysql v5.1) I do not want to bluntly stop this from the apache level. How does one place a time limit on the execution of mysql_query ()? Thanks for your help, dK It depends on what type of table you have, MyISAM blocks at table level for instance, while InnoDB locks at row level. Thanks Ash www.ashleysheridan.co.uk I'm using InnoDB. But either way how do you place a time limit on mysql_query()? Thanks, dK I've not seen this done before, but it should only be row locking if you're using InnoDB. What queries are you running that are affecting a script that badly? Thanks Ash www.ashleysheridan.co.uk I issue a 'lock tables tablename write' and do some work. This is to be expected. I want a way that to stop mysql_query() from just waiting for the mysql server. ` -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php Further thought, do you have indexes on the table? Bastien Yes. The question is not about MySQL efficiency - i'll leave that to the MySQL email group. This question is about putting in place PHP safe guards. Thanks, dK So it's a concurrency question. There are a couple of schools of thought here: Flag the record on select before the update to 'lock' it to a specific user. Any other user would get a readonly version of the data ( perhaps with a notice indicating another user has the lock ) Another common option is to accept both changes into a queue and then deciding who's data shall be the final version. This would add a ton of complexity fir very little gain. IMHO the first option is better as you could build functionality to track the record request and notify the second user when that data has been committed and the 'lock' flag turned 'off'. Bastien -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
