Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

This depends on table engine?

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:

Major gotcha: Rows are locked during updates using indexes. For example, UPDATE tab SET col1=3 WHERE col2=17; will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked).

and

To expand on the above comment, any operation that does a table scan for update/delete will lock all the rows in the table

Extra curricular reading:

TL;DR: InnoDB index lock is major architectural performance flaw, and that is why you hear that large tables are slower. There’s a big chance that there’re more scalable engines for on-disk writes out there, and all the large InnoDB write/insert benchmarks were severely hit by this.

share|improve this answer
    
Nice extra curricular article. +1 !!! –  RolandoMySQLDBA Oct 31 '11 at 16:45
    
Good one, very helpful! Does it also apply to SELECT ... FOR UPDATE queries? –  fedorqui Aug 15 '13 at 8:36

There is always the imminent threat of overlapping results. In this particular case, I can see rows even in InnoDB running headfirst into deadlock situations.

I answered three very tough questions addressing a similar issue.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I aggressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

In all three of these questions, a row lock involved a corresponding lock in the clustered index of the same table. Neighboring keys of locked rows were involved and thus contributed to the issues.

MORAL OF THE STORY : Deadlocks with InnoDB is still a possibility. Setting up a proper algorithm for individual row-level locks and individual updating the rows in question is a whole lot safer that bulk updating via multiple row-level locks anyday.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.