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 > it.
Please see http://www.mysql.com/doc/L/o/Locking_methods.html and http://www.mysql.com/doc/L/o/Locking_Issues.html . 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. -- Dan Nelson [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
