Ang Chin Han <[EMAIL PROTECTED]> writes: > Heck, even using myisam, mysql's count(*)'s still accurate, since all INSERTs, > etc are autocommitted.
That's sort of true, but not the whole story. Even autocommitted transactions can be pending for a significant amount of time. The reason it's accurate is because with mysql isam tables all updates take a table level lock. So there's never a chance to select the count while an uncommitted transaction is pending, even if the update takes a long time. This is simple and efficient when you have low levels of concurrency. But when you have 4+ processors or transactions involving lots of disk i/o it kills scalability. I'm curious how it's implemented with innodb tables. Do they still take a table-level lock when committing to update the counters? What happens to transactions that have already started, do they see the new value? Actually it occurs to me that that might be ok for read-committed. Is there ever a situation where a count(*) needs to represent an old snapshot in read-committed? It has to for long-running selects, but if the count(*) itself is always fast that need should never arise, just shared-lock and read the value and unlock. In order words, imagine if you had every transaction keep a net delta of rows for every table and at commit time locked the entire table and updated the count. The lock would be a point of contention but it would be very fast since it would only have to update an integer with a precalculated adjustment. In read-committed mode that would always be a valid value. (The transaction would have to apply its own deltas I guess.) -- greg ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
