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.
精彩评论