MYSQL - Lock empty rows

Question

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 = 'nousername' 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?

Answer 1

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)