I want to know if concurrently UPDATE …. LIMIT N will not overlap with more than one client querying the db.
After UPDATE … LIMIT N the client will make a SELECT with some client_id assigned.
I don't want clients to overlap results, so every client will have different records each time the query the db with the SELECT after the update.
This depends on table engine?
The update locks the table/records? (I am sure this one depends on engine).
From my opinion UPDATE LIMIT should not overlap results, dbs have ACID property…I want to confirm this.
Best Answer
Yup. MyISAM will lock the table, InnoDB will lock rows
If you're looking for ACIDity, you'll want to be using InnoDB. How InnoDB handles locking is described in this doc page
The following user comments on the above document page illustrate a flaw in InnoDB performance on large tables:
and
Extra curricular reading: