Re: One table gets locked by itself
- Original Message - From: nixofortune nixofort...@gmail.com Few more things. You can't have a deadlock on Mylsam table. You can You *can* have deadlocks in MyISAM; the concept is not related to any specific engine - or even databases. What you can't have, is deadlock on a single table :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
Yes indeed, but I think we are talking about MySQL level deadlocks, that can happen only with row level locking and transactions. If the deadlock is generated at application level then you can have it on anything, also blackhole :-) Claudio 2012/5/9 Johan De Meersman vegiv...@tuxera.be - Original Message - From: nixofortune nixofort...@gmail.com Few more things. You can't have a deadlock on Mylsam table. You can You *can* have deadlocks in MyISAM; the concept is not related to any specific engine - or even databases. What you can't have, is deadlock on a single table :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Claudio
Re: One table gets locked by itself
- Original Message - From: Claudio Nanni claudio.na...@gmail.com Yes indeed, but I think we are talking about MySQL level deadlocks, that can happen only with row level locking and transactions. If the deadlock is generated at application level then you can have it on anything, also blackhole :-) Yup, but you don't know where the lock comes from, so that's possible. However, I just checked the documentation, and he *is* right after all: MySQL claims to be deadlock-free for table-locking engines, they simply lock all tables at once (I suppose that means any lock statement will invalidate the previous one) and always lock tables in the same order. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: One table gets locked by itself
On 5/9/2012 6:17 AM, Johan De Meersman wrote: - Original Message - From: Claudio Nanniclaudio.na...@gmail.com Yes indeed, but I think we are talking about MySQL level deadlocks, that can happen only with row level locking and transactions. If the deadlock is generated at application level then you can have it on anything, also blackhole :-) Yup, but you don't know where the lock comes from, so that's possible. However, I just checked the documentation, and he *is* right after all: MySQL claims to be deadlock-free for table-locking engines, they simply lock all tables at once (I suppose that means any lock statement will invalidate the previous one) and always lock tables in the same order. Actually, we do not lock all tables at the same time. Each table is locked as it is needed by the connection that needs it. As long as this is an implicit lock (created by the SQL command to be executed) and not a lock held by an explicit LOCK command then there is no way for any two connections to deadlock on a MyISAM (or another non-transactional table). It is only when a lock extends beyond the single-object scenario that makes a deadlock is possible. As each statement against a MyISAM table generally only needs to lock one object (the table) no two MySQL-based statements can deadlock. Even in those cases where one statement uses two objects (such as a INSERT...SELECT... or multi-table UPDATE command) the lock prioritization process for MyISAM prevents any two threads from cross-locking at the same time. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: One table gets locked by itself
Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: One table gets locked by itself
Also,... If it happens again, do SHOW FULL PROCESSLIST while it is happening. Usually the non-Locked entry is the villain. -Original Message- From: Darryle Steplight [mailto:dstepli...@gmail.com] Sent: Tuesday, May 08, 2012 7:32 AM To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: One table gets locked by itself
Not really. If its a deadlock , they all are victims. (E.g. mutex wait) Or a long running query (sending data) might be the guy. Claudio On May 8, 2012 7:31 PM, Rick James rja...@yahoo-inc.com wrote: Also,... If it happens again, do SHOW FULL PROCESSLIST while it is happening. Usually the non-Locked entry is the villain. -Original Message- From: Darryle Steplight [mailto:dstepli...@gmail.com] Sent: Tuesday, May 08, 2012 7:32 AM To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself In your my.conf or configuration file look for an attribute that says LOG_SLOW_QUERIES , that should point to the path of your slow query log. On Tue, May 8, 2012 at 10:19 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi Thanks, Where can i find query log for previous one,or i have to do some config in my.ini file, please let me know, Thanks Abhi -Original Message- From: Darryle [mailto:dstepli...@gmail.com] Sent: 08 May 2012 19:42 To: abhishek jain Cc: mysql@lists.mysql.com Subject: Re: One table gets locked by itself Chech your query log for queries hitting that tables. Myisam tables dont have row level locking. There is probably a slow query somewhere. Sent from my iPhone On May 8, 2012, at 10:04 AM, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi
Re: One table gets locked by itself
My plan B was basically what Rick and Claudio said. Check your my.conf file for this variable LONG_QUERY_TIME . That determines how long a query will run before it's considered slow. You may need to adjust that setting, but that will just get rid of the symptom and not the problem at hand. Finding the actual query is the first step. On Tue, May 8, 2012 at 1:42 PM, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi -- -- May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: One table gets locked by itself
Few more things. You can't have a deadlock on Mylsam table. You can check status of your tables in use with: SHOW OPEN TABLES WHERE IN_USE !=0 you might check mysqld error log ad well On 8 May 2012 18:42, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi
Re: One table gets locked by itself
Right, Technically not a deadlock, Practically yes if hundreds of threads are waiting on the same mutex, Like key cache one or query cache or any other mutex. Claudio On May 8, 2012 7:51 PM, nixofortune nixofort...@gmail.com wrote: Few more things. You can't have a deadlock on Mylsam table. You can check status of your tables in use with: SHOW OPEN TABLES WHERE IN_USE !=0 you might check mysqld error log ad well On 8 May 2012 18:42, nixofortune nixofort...@gmail.com wrote: You might run out of file desciptors. Check your open file limits, open table limits vars and corresponding syatus values On 8 May 2012 15:05, abhishek jain abhishek.netj...@gmail.com wrote: Hi I am facing a strange problem, from the last few days in one of my projects in production, i find that one of my table fails to retrieve or insert records, I think it gets locked somehow, certainly my code doesn't have code to do so explicitly. All / rest of tables are fine, only one table creates problem. All is well after i restart mysqld. Dont know what to check! Details are: Mysqld version: 5.0.x Linux - Centos 5 Table : MyISAM Please help me asap, Thanks, Abhi
