开发者

MYSQL - Lock empty rows

How can i lock empty rows?

I want to lock an empty select for example:

Session 1

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

mysql> select * from users where username = 'nous开发者_如何学运维ername' for update;
Empty set (0.01 sec)

mysql>

Session 2

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

mysql> select * from users where username = 'nousername' for update;
Empty set (0.00 sec)

mysql>

I need Session 2 to wait until session 1 is completed before the result is returned.

How can i achieve that?

Thanks?


There you have three options, each having advantages and disadvantages, I prefer select for update or lock tables:

1. advisory lock get_lock() as mentioned by @Denis:

cons:

  • doesn't get cleared away with commit, rollback,

  • system wide names, so you should implement your own unique naming

pros:

  • doesn't clear existing transactions

  • doesn't block other queries

2. table lock lock tables

pros:

  • does get cleared away with commit, rollback,

  • per database locks

cons:

  • clears existing transactions

3. lock tables with pseudo select - Instead of having specific select, i use general select select max(users_id) from users for update

pros:

  • does get cleared away with commit, rollback,

  • per database locks

  • doesn't clear existing transactions

cons:

  • doesn't work if table is empty (you need to insert pseudo row, or use dedicated table for locks)


Use an advisory lock.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜