Tweaking MySQL configuration to support hundreds of connections locking the same table

I have a resources table with 2000 rows. Engine is innodb. There is a 'free_at' field (indexed). On each request I need to lock the table, get a free resource (ordered by 'free_at' column), update that row to non free, and release the lock.

This is a basic pool implementation that I've been using and had worked fine with 100-200 connections and less than 1000 rows (resources in the pool).

Right now there are about 800 processes that are constantly requesting resources from the table (each every 10-15 seconds, so the average is up to 80\s).

My bottle neck is the lock wait time, which is ranging between 30 to 60 seconds (!) for each request. I'm sure there is some configuration I should change to make it lock and release faster.

I've tried changing the engine type to MEMORY but that didn't improve the lock wait time.

Should I look for another pool solution that is not MySQL based and can dispense resources by priority (which in my case is the 'free_at' field)?

EDIT:

for locking I use LOCK TABLES table_name WRITE

Then selecting SELECT * FROM table_name WHERE (free_at < NOW() OR free_at is null) ORDER BY free_at ASC

Updating the 'free_at' field UPDATE table_name SET free_at = NOW() + INTERVAL 5 MINUTE WHERE id= 1234

Finally unlocking UNLOCK TABLES

Table schema

`resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `free_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `free_at` (`free_at`),
) ENGINE=InnoDB

Solution

I managed to solve this issue by internally buffering resources. Instead of fetching 1 resource at a time, I'm fetching 5 of them, thus reducing the amount of calls and locks on that table by a factor of 5.