Lock Db Rows For Concurrent Access

Yii 1.1 General Discussion
1

I’m trying to manage concurrent DB access where the concurrent clients are essentially competing for a set of resources and I have to ensure that each client is assigned a unique resource.

The simplified version is this:

  • DB is MySql

  • Tables are using InnoDB

  • ‘resource’ table has resources, one per row, with state

  • ‘allocation’ table has details about a particular resource allocation as well as the allocated resource’s ID

  • ‘resource’ looks like this:

    ID STATE

    1 Available

    2 Available

    3 Available

  • ‘allocation’ table looks like this

    RESOURCE USER

  • Then, during a resource allocation transaction, something like this:

    $id = SELECT ID FROM resource WHERE STATE = ‘Available’;

    UPDATE resource SET STATE = ‘Allocated’ WHERE ID = $id;

    INSERT INTO resource VALUES($id, $username);

The issue comes where I get concurrent access. I assumed (erroneously, I suppose) that with InnoDB, using a transaction would lock at least the affected tables/rows/etc until I committed it. However, what’s happening is that more than one client is getting $id of the same resource and ending up with multiple allocations having the same resource ID.

Is there a way for me to do some sort of locking so that either the read/update is atomic or the secondary clients are locked from selecting until the primary client is able to read and update? The concurrency requirements aren’t huge, so stalling the secondary clients would be acceptable. Just assigning the same resource to two different clients absolutely must not happen.

Thanks!

2

Yes, you can lock tables, for writing and for reading.

Keep in mind that this is MySQL specific SQL code here:


LOCK TABLES table1 WRITE;

SELECT * FROM table1;

UNLOCK TABLES;

More info here: LOCK TABLES and UNLOCK TABLES Syntax