5.1.24-rc and partitioning and add, drop partitions and select
Hi ALL, I have been using 5.1.24-rc and partitioning by range. I have noticed that while doing a set of SELECT statements on one of the partitions (e.g. p1) and at the same time doing an ALTER TABLE with drop or add a different partition (e.g. ALTER TABLE DROP or ADD PARTITION p10) all the select statements are placed on hold (Waiting for table). Would it be possible to know the reason for that ? My understanding was there there was a lock per partition, instead of lock per table ? Thanks in advance for your help, Mariella Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.1.24-rc and partitioning and add, drop partitions and select
One more question, plz (http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html) Table locks. The process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending INSERT and UPDATE operations are performed as soon as the partitioning operation has completed Is that true also for the InnoDB storage engine ? What does Reads from such tables are relatively unaffected; implies, please ? Thanks, Mariella --- Mariella Petrini [EMAIL PROTECTED] wrote: Hi ALL, I have been using 5.1.24-rc and partitioning by range. I have noticed that while doing a set of SELECT statements on one of the partitions (e.g. p1) and at the same time doing an ALTER TABLE with drop or add a different partition (e.g. ALTER TABLE DROP or ADD PARTITION p10) all the select statements are placed on hold (Waiting for table). Would it be possible to know the reason for that ? My understanding was there there was a lock per partition, instead of lock per table ? Thanks in advance for your help, Mariella Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 5.1.24-rc and partitioning and add, drop partitions and select
Hi, Alter table works on the whole table and it apply on whole table, irrespective of partition. It will block all other queries ( select, insert, update) until alter gets completed. On Mon, May 12, 2008 at 8:45 PM, Mariella Petrini [EMAIL PROTECTED] wrote: One more question, plz (http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html) Table locks. The process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending INSERT and UPDATE operations are performed as soon as the partitioning operation has completed Is that true also for the InnoDB storage engine ? What does Reads from such tables are relatively unaffected; implies, please ? Thanks, Mariella --- Mariella Petrini [EMAIL PROTECTED] wrote: Hi ALL, I have been using 5.1.24-rc and partitioning by range. I have noticed that while doing a set of SELECT statements on one of the partitions (e.g. p1) and at the same time doing an ALTER TABLE with drop or add a different partition (e.g. ALTER TABLE DROP or ADD PARTITION p10) all the select statements are placed on hold (Waiting for table). Would it be possible to know the reason for that ? My understanding was there there was a lock per partition, instead of lock per table ? Thanks in advance for your help, Mariella Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL Internals Mailing List For list archives: http://lists.mysql.com/internals To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]