[SLL] Need MySQL expert

[Justin Simpson]

Not that this helps you in any way, but this situation is a good example
of why postgresql is a better choice than mysql.  Multi Version
Concurrency Control.

from the postgres docs
http://www.postgresql.org/docs/8.1/interactive/mvcc.html

Unlike traditional database systems which use locks for concurrency
control, PostgreSQL maintains data consistency by using a multiversion
model (Multiversion Concurrency Control, MVCC). This means that while
querying a database each transaction sees a snapshot of data (a database
version) as it was some time ago, regardless of the current state of the
underlying data. This protects the transaction from viewing inconsistent
data that could be caused by (other) concurrent transaction updates on
the same data rows, providing transaction isolation for each database
session. 

The main advantage to using the MVCC model of concurrency control rather
than locking is that in MVCC locks acquired for querying (reading) data
do not conflict with locks acquired for writing data, and so reading
never blocks writing and writing never blocks reading. 

Table- and row-level locking facilities are also available in PostgreSQL
for applications that cannot adapt easily to MVCC behavior. However,
proper use of MVCC will generally provide better performance than
locks. 

Justin Simpson

On Thu, 2006-08-03 at 09:02 -0700, James Moore wrote:
> > Maybe the data has
> > to be held stable while it's being sent back to the client?
> 
> I'd assume that sending doesn't imply that everything has been completely
> read from the tables, so the sending connection still has a read lock on the
> table.
> 
> MySQL has a pretty straightforward one writer/many readers table-level
> locking system; all reads have to complete before a write can go ahead, and
> you can't read while someone's writing; many reads can happen simultaneously
> if there aren't any writes.
> 
> The implication is that long reads are potentially extremely expensive,
> since you get something like:
> 
> 1.  Start very long read (a select on a non-indexed field, for example).
> 2.  Do a tiny update - one column of one row
> 3.  All new readers are now blocked waiting for #2 to finish, and #2 is
> waiting for #1. 
> 
> Joins make this problem even worse, since you've got read locks on every
> table the join hits. 
>  
>  - James Moore
>