In the last episode (May 19), Khaled Elmeleegy said:
> How does MySQL handle concurrency control between two or more concurrent
> quires (not transactions), on their website they say they use
> multi-versioned concurrency control, but i guess this between transactions,
> not queries.
> Consider the case we have two queries
> Q1: select * from table1 EXCEPT select * from table1 where id>50;
> Q2:update table1 set id = id+1;
> Concurrent execution of these two queries could result in incorrect results,
> if no appropriate concurrency control is used, specifically if the update
> query is executed between the first half of Q1 and the second half.
> there are many other examples to this problem, so i wonder how MySQL handles
Please see http://www.mysql.com/doc/L/o/Locking_methods.html and
Mysql will always return the correct value for your query. How it
accomplishes that depends on the table type. For MyISAM tables, the
entire table is read-locked during queries and read+write locked during
updates. Writers wait for readers to finish before locking the table,
then readers queue up until the writer finishes. For BDB tables, only
affected pages are locked. For InnoDB, records are write-locked and
multiple versions of records are kept to keep queries from blocking
each other unnecessarily.