1

I have a mysql lock question: If I query this sql: select * from user order by id asc limit 0,1000. Then anohther thread simutanousely delete the row between 0,1000 in the user table,if allowed?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Jack
  • 5,540
  • 13
  • 65
  • 113

1 Answers1

0

In the MySQL Documentation for InnoDB, it states InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default.

More directly, however is Internal Locking Methods, which says MySQL uses table-level locking for MyISAM, MEMORY, and MERGE tables, allowing only one session to update those tables at a time. Also, this:

MySQL grants table write locks as follows:
    1. If there are no locks on the table, put a write lock on it.
    2. Otherwise, put the lock request in the write lock queue.
MySQL grants table read locks as follows:
    1. If there are no write locks on the table, put a read lock on it.
    2. Otherwise, put the lock request in the read lock queue.

Okay, let's digest that: In InnoDB, each row has it's own lock, which means your query would loop through the table until it hit a row that has a lock. However, in MyISAM, there is only one lock for the entire table, which is set before the query is executed.

In other words, for InnoDB, if the DELETE operation removed the row before the SELECT operation read the row, then the row would not show up in the results. However, if the SELECT operation read the row first, then it would be returned in the result set, but any future SELECT operations would not show the row. If you want to intentionally lock the entire result set in InnoDB, look into SELECT ... FOR UPDATE.

In MyISAM, the table is locked by default, so it depends which query began execution first: if the DELETE operation started first, then the row would not be returned with the SELECT. But if the SELECT operation began execution first, then the row would indeed be returned.

There is more about interlaced here: http://dev.mysql.com/doc/refman/5.0/en/select.html

And also here: Any way to select without causing locking in MySQL?

Community
  • 1
  • 1
cegfault
  • 6,442
  • 3
  • 27
  • 49
  • Dear Cegfrault,thanks your kindly answer,but still have a question: in the InnoDb,if two thread(one write other is read) currently focus on one row,so who is first. in other words:if I query 0,1000 ordered rows,but currently one thread delete the 500th row and lat long time (maybe the delete last 10s),so then the read thread bypass the delete row continue read 501 t0 1000,or the read thread be blocked wating the delete or update success. – Jack Sep 04 '12 at 07:44
  • In InnoDB, if the write lock is set before the read lock then the select will wait for the update. If the read lock gets there first, then the select will be issued before the delete. The locks use mutexes, so it is impossible for the locks to be assigned at the same time. – cegfault Sep 04 '12 at 13:44
  • yes I see,but as you say when the read waits the update/delete(we presume only one row),but the update/delete need 10seconds to finish the one row or many rows operation,and just the read operation will read the row so if the read need wait 10 seconds i.e blocked 10 senconds,to wait the delete/update finished. thanks – Jack Sep 04 '12 at 14:39
  • 2
    If your database is taking 10 seconds to delete you are doing something terribly, terribly wrong. – cegfault Sep 05 '12 at 19:45