Partitions and locking

[Benoit St-Jean]

I've checked the documentation and couldn't find anything related to locks in a partitioned table.

I'm investigating the possibility of using a partitioned table (with InnoDB) to act as a message queue for an in-house middleware. Instead of having a table for every topix/queue, a partitioned table would definitely simplify maintenance and design a lot.

I'm not set on the number of partitions (might be split by topic or by a bogus hashed number) and approximately 50 concurrent users will each insert 15 records at a time into that table while approximately 20 worker-threads will pop those messages (SELECT FOR UPDATE and then DELETE) every 5-10 seconds and put them in their appropriate tables after some data massaging.

I was wondering if this SELECT/DELETE/SELECT_FOR_UPDATE mix would be better handled by a partitioned table (as opposed to a "normal" table) or if any of you can see big trouble on the horizon!

Thanks