CODEWITHSUNDEEP
mysqlinnodb
Eugene
Eugene

Reputation: 2974

MySQL: How to achieve row-level transaction locking instead of table locking

Here's the use case:

I have a table with a bunch of unique codes which are either available or not available. As part of a transaction, I want to select a code that is available from the table, then later update that row later in the transaction. Since this can happen concurrently for a lot of sessions at the same time, I want to ideally select a random record and use row-level locking on the table, so that other transactions aren't blocked by the query which is selecting a row from the table.

I am using InnoDB for the storage engine, and my query looks something like this:

select * from tbl_codes where available = 1 order by rand() limit 1 for update

However, rather than locking just one row from the table, it ends up locking the whole table. Can anyone give me some pointers on how to make it so that this query doesn't lock the whole table but just the row?

Update

Addendum: I was able to achieve row-level locking by specifying an explicit key in my select rather than doing the rand(). When my queries look like this:

Query 1:

   select * from tbl_codes where available = 1 and id=5 limit 1 for update

Query 2:

   select * from tbl_codes where available = 1 and id=10 limit 1 for update

However, that doesn't really help solve the problem.

Addendum 2: Final Solution I went with

Given that rand() has some issues in MySQL, the strategy I chose is:

  1. I select 50 code id's where available = 1, then I shuffle the array in the application layer to add a level of randomness to the order.

    select id from tbl_codes where available = 1 limit 50

  2. I start popping codes from my shuffled array in a loop until I am able to select one with a lock

    select * from tbl_codes where available = 1 and id = :id

Upvotes: 3

Views: 5378

Answers (2)

jeremycole
jeremycole

Reputation: 2761

It may be useful to look at how this query is actually executed by MySQL:

select * from tbl_codes where available = 1 order by rand() limit 1 for update

This will read and sort all rows that match the WHERE condition, generate a random number using rand() into a virtual column for each row, sort all rows (in a temporary table) based on that virtual column, and then return rows to the client from the sorted set until the LIMIT is reached (in this case just one). The FOR UPDATE affects locking done by the entire statement while it is executing, and as such the clause is applied as rows are read within InnoDB, not as they are returned to the client.

Putting aside the obvious performance implications of the above (it's terrible), you're never going to get reasonable locking behavior from it.

Short answer:

  1. Select the row you want, using RAND() or any other strategy you like, in order to find the PRIMARY KEY value of that row. E.g.: SELECT id FROM tbl_codes WHERE available = 1 ORDER BY rand() LIMIT 1
  2. Lock the row you want using its PRIMARY KEY only. E.g.: SELECT * FROM tbl_codes WHERE id = N

Hopefully that helps.

Upvotes: 3

gd1
gd1

Reputation: 11403

Even if not exactly mapping to your question, the problem is somewhat discussed here: http://akinas.com/pages/en/blog/mysql_random_row/

The problem with this method is that it is very slow. The reason for it being so slow is that MySQL creates a temporary table with all the result rows and assigns each one of them a random sorting index. The results are then sorted and returned.

The article does not deal with locks. However, maybe MySQL locks all the rows having available = 1 and does not release them until the end of the transaction!

That article proposes some solution, none of them seems to be good for you, except this one which is, unfortunately, very hacky and I didn't probe its correctness.

SELECT * FROM table WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM table ) ORDER BY id LIMIT 1;

This is the best I can do for you since I don't command MySQL internals. Moreover, the article is pretty old.

Upvotes: 1

Related Questions