List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:May 20 2002 5:07am
Subject:Re: Concurrency control
View as plain text  
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
	dnelson@stripped
Thread
Concurrency controlKhaled Elmeleegy20 May
  • Re: Concurrency control龔惠樑(Borus.Kung)20 May
  • Re: Concurrency controlDan Nelson20 May