CODEWITHSUNDEEP
mysql
Jesvin Jose
Jesvin Jose

Reputation: 23098

select ...for update causes locks

When users are signing up to our service, I want to allocate one solr core (the kind of resource) out of many. One core exclusively for one user (for example!). And I want to use select ...for update to ensure that concurrent signups see (and use) different rows.

Stuff works for the first signup transaction; we get the row we are allowed to use. But for the second (onward), Lock wait timeout exceeded happens; when I wanted the next row.

Why is MySQL giving throwing this error?

From terminal 1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from SolrCoresPreallocated order by id limit 1 for update;
+----+-------------+-----+-----+
| id | used_status | sid | cid |
+----+-------------+-----+-----+
|  1 |           0 |   0 | 400 |
+----+-------------+-----+-----+
1 row in set (0.00 sec)

In terminal 2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from SolrCoresPreallocated order by id limit 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  rollback;
Query OK, 0 rows affected (0.00 sec)

I actually wanted to see:

mysql> select * from SolrCoresPreallocated order by id limit 1 for update;
+----+-------------+-----+-----+
| id | used_status | sid | cid |
+----+-------------+-----+-----+
|  2 |           0 |   0 | 401 |
+----+-------------+-----+-----+
1 row in set (0.00 sec)

My data is:

mysql> select * from SolrCoresPreallocated;
+----+-------------+-----+-----+
| id | used_status | sid | cid |
+----+-------------+-----+-----+
|  1 |           0 |   0 | 400 |
|  2 |           0 |   0 | 401 |
|  3 |           0 |   0 | 402 |
|  4 |           0 |   0 | 403 |
|  5 |           0 |   0 | 404 |
|  6 |           0 |   0 | 405 |
+----+-------------+-----+-----+
6 rows in set (0.00 sec)

I described my problem in: Different transactions must guarantedly select different items; avoid contentions

Upvotes: 0

Views: 176

Answers (1)

O. Jones
O. Jones

Reputation: 108839

This query actually always requests and locks the same row, no matter which MySQL connection runs it.

select * from SolrCoresPreallocated order by id limit 1 for update;

Locked rows aren't somehow magically excluded from future queries. They're just locked. That's the problem you're seeing; all your operations are locking the same row.

If you could do something like this (caution: pseudocode; caution: I don't understand your schema) it would most likely work much better

 begin;

 update SolrCoresPreallocated
    set allocated = (this customer id)
  where allocated = 0 
  limit 1;

(check result; if no rows were modified you ran out of allocated=0 rows.)

select * from SolrCoresPreallocated where allocated = (this customer id);

commit;

Upvotes: 1

Related Questions